User access with Atlassian tools

User access times with Atlassian tools You’d be mistaken if you had a few searches on the internet and found the following page suggesting that you could just query the application cwd_user table except when using Crowd when you should query against the crowd cwd_user_attribute table. Unfortunately that might have been true back with Jira 4.3, although in the last few years mixing the Atlassian tools (with Crowd as SSO provider) this is no longer the case and I’ve found the following MySQL query works well to find out login times for each application

Crowd logged in users

This came in useful for me today to understand users that could be disabled from a crowd instance: SELECT cwd_user.user_name AS Username, cwd_user.email_address AS "Email Address", from_unixtime(cwd_user_attribute.attribute_value/1000) AS "Last Logged In" FROM cwd_user LEFT OUTER JOIN cwd_user_attribute ON cwd_user_attribute.user_id = AND cwd_user_attribute.attribute_name = 'lastAuthenticated' WHERE active = 'T' ORDER BY 3,2,1 ;

SQL queries with Jira

To save having to understand how the underlying db schema works: SQL Queries for JIRA