Skip to main content
P
ptc-49973211-Visitor
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

    D
    DarrenStorey12-Amethyst
    12-Amethyst
    July 10, 2014

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

    B
    BenPerry15-Moonstone
    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;
    P
    ptc-49973211-VisitorAuthor
    1-Visitor
    July 10, 2014

    Can we find out only for one specific user?

    B
    BenPerry15-Moonstone
    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.

    M
    22-Sapphire I
    MikeLockwood22-Sapphire I
    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]

    B
    BenPerry15-Moonstone
    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.

    M
    msyed1-Visitor
    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

    Terms & ConditionsCookie settingsAccessibility statement