Sql query to get the list of Jira and Jira Service Desk Active users last login details

Surender_7y7n9v

Hi All,

Is there any Sql query to get the list of Jira Software as well as Jira Service Desk Active users last login details.

I have found the below article as part of my search. but this article is NOT giving the last login of the "Jira and Jira Service Desk Active users last login'

- https://confluence.atlassian.com/jirakb/how-to-get-a-list-of-active-users-counting-towards-the-jira-application-license-278695452.html\\ 
- https://confluence.atlassian.com/jirakb/retrieve-last-login-dates-for-users-from-the-database-363364638.html

I tried to make a query by using these two articles but that didn't worked for me.

Please advise.

Thanks,
Surender

Andy Heinzer

I think you can combine these two queries, but there are some considerations when doing so.   Each of these articles is seeking something slightly different so the SQL select statements are specifically geared for the results in question.  That said, I think I have found a way for you to see the last login time of specific users in a licensing role.  For Service Desk Agents:

SELECT d.directory_name AS "Directory", u.user_name AS "Username",
    to_timestamp(CAST(attribute_value AS BIGINT)/1000) AS "Last Login", lrg.license_role_name
FROM cwd_user u
JOIN (
    SELECT DISTINCT child_name
    FROM cwd_membership cm
    JOIN licenserolesgroup gp ON cm.parent_name = gp.GROUP_ID
    ) AS cm ON cm.child_name = u.user_name
JOIN (
    SELECT * 
    FROM cwd_user_attributes ca
    WHERE attribute_name = 'login.lastLoginMillis'
    ) AS a ON a.user_id = u.id
JOIN cwd_directory d ON u.directory_id = d.id
JOIN cwd_membership m
         ON u.id = m.child_id
            AND u.directory_id = m.directory_id
       JOIN licenserolesgroup lrg
         ON Lower(m.parent_name) = Lower(lrg.group_id)
WHERE  d.active = '1'
       AND u.active = '1'
    AND license_role_name = 'jira-servicedesk'
ORDER BY "Last Login" DESC;

 

And for Jira Software users:

SELECT d.directory_name AS "Directory", u.user_name AS "Username",
    to_timestamp(CAST(attribute_value AS BIGINT)/1000) AS "Last Login", lrg.license_role_name
FROM cwd_user u
JOIN (
    SELECT DISTINCT child_name
    FROM cwd_membership cm
    JOIN licenserolesgroup gp ON cm.parent_name = gp.GROUP_ID
    ) AS cm ON cm.child_name = u.user_name
JOIN (
    SELECT * 
    FROM cwd_user_attributes ca
    WHERE attribute_name = 'login.lastLoginMillis'
    ) AS a ON a.user_id = u.id
JOIN cwd_directory d ON u.directory_id = d.id
JOIN cwd_membership m
         ON u.id = m.child_id
            AND u.directory_id = m.directory_id
       JOIN licenserolesgroup lrg
         ON Lower(m.parent_name) = Lower(lrg.group_id)
WHERE  d.active = '1'
       AND u.active = '1'
    AND license_role_name = 'jira-software'
ORDER BY "Last Login" DESC;

Neither of these queries should return inactive users