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

[ptc-4997321]

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

[DarrenStorey]

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

[BenPerry]

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;

[ptc-4997321]

Can we find out only for one specific user?

[BenPerry]

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.

[ptc-4997321]

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]

...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

[MikeLockwood]

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]

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]

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]

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?

[MikeLockwood]

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]

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.

[MikeLockwood]

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.

[GS_8484098]

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

Should I give the criteria with disabled=1??

[msyed]

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