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?

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