Community Tip - Stay updated on what is happening on the PTC Community by subscribing to PTC Community Announcements. X
DOES ANYONE KNOWS QUERY TO CHECK LAST LOGIN OF A USER(INACTIVE).
You can check security audit reporting. if you are recording user login events. Think this is enabled OOTB.
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?
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";
...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:
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.
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]
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):
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.
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.
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?
Query Builder report which correctly filters for last login posted last week - posted again here. Note that this has 90 days as a criteria.
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.
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