Welcome to the NEW CHUG— your ultimate destination for cutting-edge insights and discussions at the intersection of healthcare and technology.

Our platform is dedicated to exploring the dynamic landscape of healthcare technology, empowering professionals, enthusiasts, and innovators alike with the knowledge and resources to navigate the rapidly evolving field.

From telemedicine revolutionizing patient care to the transformative potential of artificial intelligence in diagnostics, we delve into a myriad of topics shaping the future of healthcare delivery. Whether you’re a seasoned healthcare IT professional, an aspiring entrepreneur in the digital health space, or simply curious about the latest innovations, our curated content, expert interviews, and thought-provoking articles provide a comprehensive exploration of healthcare technology’s most pressing issues.

Join us as we navigate the forefront of innovation, driving forward the transformation of healthcare for a healthier, more connected world.

Have questions or suggestions? Please use the contact button below.

Notifications

Clear all

How to determine users last login date/time

MEL, SQL, Oracle

Last Post by Anonymous 1 second ago

10 Posts

6 Users

0 Reactions

184 Views

RSS

mjordan

(@mjordan)

Posts: 14

Eminent Member

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

StephenAdamsMD

(@stephenadamsmd)

Posts: 23

Eminent Member

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

rfisher

(@rfisher)

Posts: 126

Estimable Member

SELECT LAST_LOGIN_DATE FROM USR WHERE LOGINNAME = '$$$$$$$$$'

Replace $ with the login name of the user

Posted : January 11, 2017 5:52 am

lkeith

(@lkeith)

Posts: 457

Honorable Member

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

StephenAdamsMD

(@stephenadamsmd)

Posts: 23

Eminent Member

select * from USR where LAST_LOGIN_DATE<SYSDATE-180

Posted : January 11, 2017 7:09 am

lkeith

(@lkeith)

Posts: 457

Honorable Member

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

StephenAdamsMD

(@stephenadamsmd)

Posts: 23

Eminent Member

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

SLHV

(@slhv)

Posts: 478

Honorable Member

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

chews on straws

(@jwollard)

Posts: 46

Trusted Member

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

lkeith

(@lkeith)

Posts: 457

Honorable Member

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

Share:

24 Forums
9,940 Topics
39.9 K Posts
6 Online
6,470 Members

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed

Powered by wpForo Powered by wpForo version 2.4.0