How to find jira active users count/ list

[Natalie Salman]

Only users that logged in the last month

[Fabio Racobaldo _Catworkx_ · Accepted solution]

Hi @Natalie Salman ,

you can retrieve this info by performing an sql query :

SELECT DISTINCT u.lower_user_name
FROM cwd_user u
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)
JOIN cwd_directory d ON m.directory_id = d.id
JOIN cwd_user_attributes cua ON cua.ID=u.ID
WHERE d.active = '1' AND u.active = '1'
AND license_role_name = 'jira-software'
AND cua.attribute_name = 'login.lastLoginMillis'
AND cua.attribute_value>1546642800000;

 In order to retrieve millis value associated to start date you can use this easy online converter https://codechi.com/dev-tools/date-to-millisecond-calculators/

Hope this helps,

Fabio

[Natalie Salman]

@Fabio Racobaldo _Catworkx_  thanks!

[Fabio Racobaldo _Catworkx_]

Please mark my answer as accepted to close this thread!

[Sreekanth]

@Fabio Racobaldo _Catworkx_  Hi we have users going out of our org and we need to make them inactive,

Can you please help me out with the sql db query.

Thanks In advance.