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


DROP TABLE #results



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)


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


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



DROP TABLE #databases


SELECT * FROM #results

DROP TABLE #results

Link to comment
Share on other sites


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

  • Create New...