Skip to main content
1-Visitor
July 10, 2014
Question

DOES ANYONE KNOWS QUERY TO CHECK LAST LOGIN OF A USER(INACTIVE).

  • July 10, 2014
  • 4 replies
  • 8643 views

DOES ANYONE KNOWS QUERY TO CHECK LAST LOGIN OF A USER(INACTIVE).

4 replies

12-Amethyst
July 10, 2014

You can check security audit reporting. if you are recording user login events. Think this is enabled OOTB.

15-Moonstone
July 10, 2014

Through Windchill user interface? Or by querying DB directly. I use this to query the DB directly. I have modified it a little from the example given by PTC in a CS document.

SELECT

a.eventtime

, a.username

, substr(a.username, instr(a.username,'(',1,1)+1, instr(a.username,':',1,1)-instr(a.username,'(',1,1)-1) username

, a.USERORGNAME

--,a.ida2a2

FROM

auditrecord a,

(SELECT max(ida2a2)as max_ida2a2, username FROM auditrecord

WHERE eventlabel='Login' GROUP BY username) b

WHERE

a.username=b.username

AND a.ida2a2=b.max_ida2a2

and a.eventtime > add_months(sysdate,-3)

ORDER BY

eventtime desc;

1-Visitor
July 10, 2014

Can we find out only for one specific user?

15-Moonstone
July 10, 2014

If using the SQL method, then add this to the WHERE clause:

and a.username like '%<username>%'

For example:

and a.username like '%ben.perry%'

Or of course you can just look through the original output list of all users for the user. Might be difficult for many users though. So adding that WHERE clause would be useful.

22-Sapphire I
July 10, 2014

Finding the last time each user logged in is such a common need there definitely should be an OTB method provided by PTC. It's there but doesn't filter for latest.

  • Security Audit Reporting (via configAudit.xml) can be used but has the downsides of a) it doesn't filter for latest - you have to find some tool to do that b) the records grow rapidly without limit and have to have purge occasionally so the data is either gone or not readily accessible if saved to .zip
  • The Security Audit Reporting records for login are in fact stored in the auditrecord table as noted above - accessing the info from the Security Audit Reporting or directly to the database via SQL returns the same info, but access via SQL allows the use of the "MAX" function to get the latest.
  • In general, it's best to avoid accessing the database directly unless really necessary, so a query builder report against the same table that improves on Security Audit Reporting is a more elegant solution. Report template for exactly this is attached. We use it all the time. The SQL in that report is similar to what is posted above. Note the MAX function. It accepts an input of how many days to go back (90 used here). Returning 90 days' data is dependent on how often the Security Audit data is purged and whether the Login event is recorded

SELECT

A0.userName C1C0,

TO_CHAR(MAX(A0.createStampA2),

'dd mm yyyy hh24:mi:ss') C1C1,

MAX(A0.createStampA2) TS_C1C1

FROM AuditRecord A0

WHERE (((A0.eventLabel = ?) AND (A0.createStampA2 >= (SYSDATE)-(?))))

GROUP BY A0.userName

ORDER BY TS_C1C1 DESC

[Login, 90]

15-Moonstone
July 10, 2014

Yes...

a.eventtime > add_months(sysdate,-3)

is approximately equal to

A0.createStampA2 >= (SYSDATE)-(90)

And I didn't like getting the whole "username" from AUDITRECORD, which in fact seems to be the <fullname>(<username>:<organization>). So my 2nd SELECT statement with the substr extracted just the username from that column. Of course it could be done many different ways. For example, join AUDITRECORD and WTUSER to specifically pick out the username and not all 3.

Curiously I have found that BOTH SQLs will return duplicate results if one of the following changes during the course of the 90 days (or whatever is input):

  • Full Name
  • Username
  • Organization

I had a couple of users who's fullname changed from first.last to First Last and they are listed in the query results as both 12-JUN-2014 and TODAY. It was June 12 that they last accessed with their fullname set to first.last.

1-Visitor
April 25, 2016

Hey Guys...

Use the below query..

select * from AUDITRECORD where username like '%userid%' order by CREATESTAMPA2 desc;

Where userid in the above query is the user id of the user whom u want to search for.

Thanks,

Syed Mujahid Basha