Retrieve last login dates for users for a given group from the database

[Luc O.]

Hello,

I'm working on adding some auditing to our JIRA instance and I was using the following as a starting point (https://confluence.atlassian.com/jirakb/retrieve-last-login-dates-for-users-from-the-database-363364638.html\).

I would like to push this one step further and filter based on the groups they are in. I believe I need to add the CWD_GROUP and CWD_MEMBERSHIIP table but I'm not 100% sure.  Has anyone done this before?  Any help me with the SQL for Oracle would be fantastic.

Thanks for your help and time!

[Luc O.]

Here is what I have...  Can someone confirm if this seems to be OK?

SELECT u.user_name,
       TO_DATE('19700101','yyyymmdd') + ((attribute_value/1000)/24/60/60) as last_login_date,
       g.group_name,
       d.directory_name
FROM cwd_user u
JOIN (
    SELECT DISTINCT child_name
    FROM cwd_membership m
    JOIN licenserolesgroup gp ON m.parent_name = gp.GROUP_ID
    ) m ON m.child_name = u.user_name
JOIN (
    SELECT *
    FROM cwd_user_attributes ca
    WHERE attribute_name = 'login.lastLoginMillis'
    ) a ON a.user_id = u.ID
JOIN (
    SELECT group_name,
           parent_id,
           child_id
      FROM cwd_group g JOIN cwd_membership m ON g.id = m.parent_id
    ) g ON g.child_id = u.id
JOIN cwd_directory d ON u.directory_id = d.ID
order by last_login_date desc, user_name