How to pull the overall number of logins this month for JIRA? (PostgreSQL)

zapzap47

How to pull the overall number of logins this month for JIRA? (PostgreSQL)

 

I need this for auditing and cant seem to get a number I will eventually need to do this for Confluence and Bitbucket, any help would be appreciated.

Aditya Verma

Hi @zapzap47 ,

Welcome to the community!

Below is an Oracle DB query for Jira. Please edit it for Postgres if required. This will return you all users with their last login details.

SELECT d.directory_name, u.id, u.directory_id, u.user_name, u.active, u.created_date, u.updated_date,
u.first_name, u.last_name, u.display_name, u.email_address,
TO_DATE('19700101','yyyymmdd') + ((a.attribute_value/1000)/24/60/60) as last_login_date
FROM cwd_user u
LEFT 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
LEFT JOIN (
SELECT *
FROM cwd_user_attributes
WHERE attribute_name = 'login.lastLoginMillis'
) a ON a.user_id = u.ID
LEFT JOIN cwd_directory d ON u.directory_id = d.ID
order by last_login_date desc;

Also,please note that last login date is not always correct if the user checks "remember me" per https://jira.atlassian.com/browse/JRASERVER-60508

Thanks,

Aditya