Jump to content

User Login History?


Megan LaFrance

Recommended Posts

  • IPRO Employee

Hi Megan,

 

There are not any logs on disk or a report in the UI that will show this information. A SQL query can be used referencing the ActivityTracking tables to obtain this information. The below query is a simple example of what one would be. The query can be run on each Eclipse Database to return the following information: The User that logged in/out, if it was a login/logout and Date/Time the event occurred.

 

SELECT U.UserName, USAT.ActivityName, USA.EventDateTime

FROM ActivityTracking.F_UserSessionActivity USA

INNER JOIN ActivityTracking.DIM_UserSessionActivityType USAT ON USA.UserSessionActivityTypeKey = USAT.UserSessionActivityTypeKey

INNER JOIN ActivityTracking.DIM_User U ON USA.UserKey = U.UserKey

 

Thank you,

 

Michael Delgado

Technical Support Engineer

 

Link to comment
Share on other sites

  • IPRO Employee

Additionally, if you wish to report against all Eclipse cases at once, you can run the following query against your ADD database.

 

SELECT DatabaseName INTO #databases FROM Enterprise.CaseProductEnvironment WHERE ProductID = 3

 

IF OBJECT_ID('tempdb..#results') IS NOT NULL

BEGIN

DROP TABLE #results

END

 

CREATE TABLE #results (

[userName] NVARCHAR(256)

,[FirstName] NVARCHAR(80)

,[LastName] NVARCHAR(80)

,[Login DateTime] DATETIME

,[Case] NVARCHAR(80)

 

)

WHILE EXISTS(SELECT TOP 1 DatabaseName FROM #databases)

BEGIN

DECLARE @DBName NVARCHAR(MAX) = (SELECT TOP 1 DatabaseName from #databases)

DECLARE @sql NVARCHAR(MAX) = '

INSERT INTO #results

SELECT du.UserName

, du.FirstName

, du.LastName

, fusa.EventDateTime

, (SELECT TOP 1 Name

FROM Enterprise.CaseProductEnvironment

WHERE ProductID = 3

AND DatabaseName = ''' + @DBName + ''')

FROM ' + QUOTENAME(@DBName) + '.ActivityTracking.F_UserSessionActivity fusa

INNER JOIN ' + QUOTENAME(@DBName) + '.ActivityTracking.DIM_User du ON fusa.userkey = du.userkey

WHERE UserSessionActivityTypeKey = 1'

 

EXEC( @sql )

 

DELETE FROM #databases WHERE DatabaseName = @DBName

END

 

DROP TABLE #databases

 

SELECT * FROM #results

DROP TABLE #results

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...