SQL Active Users Last Login

Question

I am working on to provide some metrics, hoping someone has a SQL query for Confluence (JIRA too if possible) that provides active users and the last login date

I see these articles:

https://confluence.atlassian.com/confkb/how-to-identify-inactive-users-in-confluence-214335880.html

https://community.atlassian.com/t5/Confluence-questions/SQL-Query-to-show-all-users-with-Use-Confluence-permission/qaq-p/332404

- unfortunately its not showing active and last login

Confluence Server 7.4.0

JIRA Server 8.12.2

Khonski · Answer

Does anyone have MySQL query for JIRA?

Mike_pfalli · Answer

Thanks, looks like this should work:

SELECT cu.user_name,
cd.directory_name,
li.SUCCESSDATE, cu.active
FROM logininfo li
JOIN user_mapping um ON um.user_key = li.USERNAME
JOIN cwd_user cu ON um.username = cu.user_name
JOIN cwd_directory cd ON cu.directory_id = cd.id
WHERE li.SUCCESSDATE < (getdate() - 30) and cu.active= 'T'
ORDER BY li.SUCCESSDATE;

Nic Brough -Adaptavist- · Answer

The first article actually works for Jira as well - the cwd_ tables are almost identical for both Jira and Confluence.

If you are not getting data from the columns in the cwd_ tables because they are empty, then you're using a user directory system that does not record it in the application, and you'll need to query that instead.