cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Did you get an answer that solved your problem? Please mark it as an Accepted Solution so others with the same problem can find the answer easily. X

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

ptc-4997321
1-Newbie

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

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

15 REPLIES 15

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

BenPerry
13-Aquamarine
(To:ptc-4997321)

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;

Can we find out only for one specific user?

BenPerry
13-Aquamarine
(To:ptc-4997321)

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.

Guys,

I have found out a better small query:

select * from AUDITRECORD where IDB5 = 29620;

here IDB5 = IDA2A2 of user which can be find out from below query:

select * from wtuser where name = "USERNMAE";

BenPerry
13-Aquamarine
(To:ptc-4997321)

...which of course can easily be turned into

select * from auditrecord where idb5 in (select ida2a2 from wtuser where name like '%ben.perry%')

so that only 1 query needs to be executed instead of 2.

But the problem with these 2 methods are that:

  • It includes all EVENTLABELs (Login, Logout, Context Logon, others?)
  • It includes all times (21,396 rows for my username), not just the most recent

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]

BenPerry
13-Aquamarine
(To:MikeLockwood)

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.

LoriSood
22-Sapphire II
(To:MikeLockwood)

Here is a very primitive Query Builder report that will spit out the last log in date of a specified user. Perhaps you could build on this.

BenPerry
13-Aquamarine
(To:LoriSood)

Lori,

That one didn't work for my 9.1 system. But I have this one attached that does work for 9.1. Although it returns all users who've logged in since the Parameter: yyyy-mm-dd.

But I have a question. I would like to add User also. But there is only 1 type of join available. And using it causes 0 results to be returned. What I wish to get in the end is User.Attributes. The reason is because I am connected to corporate LDAP (Active Directory). I have the manager attribute mapped. I would like visibility of the manager in this report, and probably some other useful things that can be gotten from the WTUSER table.

Thoughts?

Untitled1.png

Untitled2.png

Untitled3.png

MikeLockwood
22-Sapphire I
(To:BenPerry)

Query Builder report which correctly filters for last login posted last week - posted again here. Note that this has 90 days as a criteria.

BenPerry
13-Aquamarine
(To:MikeLockwood)

Mike,

Unfortunately this does not solve my particular problem. I cannot import this QML into my 9.1 system. Also, looking at the QML in Notepad++, it doesn't appear that I can get any information from the WTUSER table with this QML file - just the AUDITRECORD table.

Untitled.png

MikeLockwood
22-Sapphire I
(To:BenPerry)

hmmmm..

1. It's lousy that you can't import 10.x to 9.x. I don't have a 9.x system around to be able to play w/it or re-create. Maybe there is some small edit that can be done to the xml file - others may know. Only other approach would be to screen capture the report and re-create (not so bad to do this).

2. Yes, it has to be against the AUDITRECORD table. The WTUSER table does not store any info about the users accessing the system. AUDITRECORD only stores it if the configAudit.xml file is configured TRUE for the LOGIN event. Not many choices on this one.

Re: DOES ANYONE KNOWS QUERY TO generate inactive user list for a particular product?

 

Should I give the criteria with disabled=1??

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

Top Tags