BO list of users last logon date

[Former Member]

Hei, I am trying to get a list of users last logon (or user that have not log on for last x months). I see other posts mentioning Audit universe, but this one is not in our system. I have tried Query builder with the following: SELECT SI_ID, SI_USERID, SI_NAME, SI_LASTLOGONTIME FROM CI_SYSTEMOBJECTS WHERE SI_KIND = 'Connection' AND SI_FAILOVER_AVAILABLE_UNTIL = NULL AND SI_AUTHEN_METHOD != 'server-token' ORDER BY SI_NAME However, it gives me only the users that are currently logged on. Is there a table with the last logon date?  Or how can I get this list? Thanks, Reynir

[Former Member · Accepted solution]

Hello Reynir,

Please try using below two queries:

Users who have not logged in for the past 6 months:

SELECT TOP 5000 SI_NAME, SI_LASTLOGONTIME FROM CI_SYSTEMOBJECTS

WHERE SI_NAME NOT IN ('ADMINISTRATOR','GUEST') AND SI_KIND='USER'

AND SI_LASTLOGONTIME < '2014.06.25' ORDER BY SI_NAME

Users who never logged in to system:

SELECT TOP 5000 SI_NAME, SI_LASTLOGONTIME FROM CI_SYSTEMOBJECTS

WHERE SI_NAME NOT IN ('ADMINISTRATOR','GUEST') AND SI_KIND='USER'

AND SI_LASTLOGONTIME IS NULL ORDER BY SI_NAME

I hope it helps!

- Mahesh

[CdnConnection]

Reynir,

     The best way I achieve is TWO parts:

1. Run custom query against CMS that provides detail list of the user. [ SELECT  SI_ID, SI_NAME, SI_USERFULLNAME, SI_DESCRIPTION, SI_LASTLOGONTIME, SI_CREATION_TIME from CI_APPOBJECTS, CI_SYSTEMOBJECTS, CI_INFOOBJECTS where (SI_KIND = 'User') ]  save this output to Excel.
2. Create a WebI report against the Audit database

Finally take the Excel out from CMS and cross report it with the Audit database.  This will provide list of the users in CMS that have also have never logged in the system and also when they logged in last.

Regards,

Ajay