Identify users who have been created for x days and never logged into Jira
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
There are times when the Jira administration would like to identify users that never logged to the application since the account had been created for a certain period for administrative purposes.
Solution
In order to see the users who never logged into Jira, run the following query:
Other databases may slightly differ the syntax. You can modify the '30 Day' to 'X Day' to cater for the days since the user had been created to suit your user's auditing requirements.
1
2
3
4
5
select user_name,created_date from cwd_user where created_date <=(CURRENT_DATE-INTERVAL '30 Day') and user_name not in
(SELECT cwd_user.user_name
FROM cwd_user, cwd_user_attributes
WHERE cwd_user_attributes.user_id = cwd_user.id
AND cwd_user_attributes.attribute_name = 'lastAuthenticated');ℹ️ This query was tested on Jira 8.16.0 and PostgreSQL database. You may need to update the syntax as per the database requirement.
Was this helpful?