Notifications
Clear all
Topic starter
We are searching for a way to determine the last date/time that all users accessed GE. Does anyone have any suggestions?
Posted : January 11, 2017 5:07 am
I'm not sure in CPS, but in CEHR there is a last_login_date in USRINFO. Hope this helps.
Posted : January 11, 2017 5:25 am
SELECT LAST_LOGIN_DATE FROM USR WHERE LOGINNAME = '$$$$$$$$$'
Replace $ with the login name of the user
Posted : January 11, 2017 5:52 am
What about a way to just look for users who haven't accessed the system in a specified number of days, such as the last 180 days? We are currently on CPS 12.0.1 sp 12
Posted : January 11, 2017 6:40 am
select * from USR where LAST_LOGIN_DATE<SYSDATE-180
Posted : January 11, 2017 7:09 am
I just tried the
select * from USR where LAST_LOGIN_DATE<SYSDATE-180
and received this error.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'SYSDATE'.
I looked and didn't see a 'SYSDATE' but there is a 'SYSDATETIME' and a 'SYSDATETIMEOFFSET'
Linda
Posted : January 11, 2017 11:22 am
OOPS my mistake... The command I gave you was for Oracle, and I THINK that CPS is SQL Server. The correct command for SQL server would be:
select * from USR where LAST_LOGIN_DATE<GETDATE()-180
This should show you all users with no login in 180 days, including deactivated ones.
Check the data dictionary -- there should be a usr.status field or something similar. If I've guessed the name correctly, this will give you active users with no recent login:
select * from USR where LAST_LOGIN_DATE<GETDATE()-180 and STATUS='A'
Maybe a CPS user can chime in...
Posted : January 11, 2017 1:19 pm
CPS is MS SQL, that is correct. Also you may want to add another condition to the query:
select * from USR where LAST_LOGIN_DATE<GETDATE()-180 and STATUS='A'
The status='a' means only show unused accounts that are marked as active in the system. Otherwise the results include disabled accounts.
Posted : January 12, 2017 2:04 am
If you have Active Directory Authentication, you can always use powershell to query AD.
get-aduser - searchbase "ou=user folder,dc=domain,dc=local" -filter {lastlogondate -gt "1/1/2017"} -Properties lastlogondate | select Name,LastLogonDate
You can add another pipe to it and export to csv
| export-csv user-report.csv
Posted : January 12, 2017 2:29 am
Thanks so much! The
select * from USR where LAST_LOGIN_DATE<GETDATE()-180 and STATUS='A'
worked like a charm!
Linda
Posted : January 12, 2017 10:12 am
