Inactive user clean-up for Jira, Confluence, and Bitbucket
Learn how to maintain control of your user directory by ensuring the directory reflects active users. You'll find user login analysis scripts for Jira, Confluence, and Bitbucket.
Whenever a workaround involves running Structured Query Language (SQL) scripts directly on your database, we strongly recommend you test in a non-production environment first, and take a backup of your production data before making any modifications via SQL scripts on your production database.
Solutions
The following scripts will produce a list of users ordered by their last login in Jira, Confluence, and Bitbucket. First, export to a CSV (comma-separated value file), then filter based on the newest → oldest. These example statements are specific for Microsoft SQL Server. You'll find more examples on knowledge base pages linked in the resources section below.
Jira - SQL Server
1SELECT d.directory_name AS "Directory",2 u.user_name AS "Username",3 DATEADD(second, cast(attribute_value as bigint)/1000,{d '1970-01-01'}) AS "Last Login"4FROM dbo.cwd_user u5JOIN (6 SELECT DISTINCT child_name7 FROM dbo.cwd_membership m8 JOIN dbo.licenserolesgroup gp ON m.parent_name = gp.GROUP_ID9 ) AS m ON m.child_name = u.user_name10JOIN (11 SELECT *12 FROM dbo.cwd_user_attributes ca13 WHERE attribute_name = 'login.lastLoginMillis'14 ) AS a ON a.user_id = u.ID15JOIN dbo.cwd_directory d ON u.directory_id = d.ID16ORDER BY "Last Login" DESC;Example of Jira SQL for users who have not logged in for 90 days
1SELECT d.directory_name AS "Directory", 2 u.user_name AS "Username", 3 to_timestamp(CAST(ca.attribute_value AS BIGINT)/1000) AS "Last Login"4FROM cwd_user u 5JOIN cwd_directory d ON u.directory_id = d.id 6LEFT JOIN cwd_user_attributes ca 7 ON u.id = ca.user_id AND ca.attribute_name = 'login.lastLoginMillis' 8WHERE u.active = 1 AND d.active = 1 AND u.lower_user_name 9IN ( SELECT DISTINCT lower_child_name 10 FROM cwd_membership m JOIN licenserolesgroup gp 11 ON m.lower_parent_name = lower(gp.GROUP_ID)) AND 12 (u.id IN ( SELECT ca.user_id 13 FROM cwd_user_attributes ca 14 WHERE attribute_name = 'login.lastLoginMillis' AND 15 to_timestamp(CAST(ca.attribute_value as bigint)/1000) <= current_date - 90) OR 16 u.id NOT IN ( SELECT ca.user_id FROM cwd_user_attributes ca 17 WHERE attribute_name = 'login.lastLoginMillis') ) 18ORDER BY "Last Login" DESC;Confluence - SQL Server
1SELECT cu.user_name,2 cd.directory_name,3 li.SUCCESSDATE4FROM logininfo li5JOIN user_mapping um ON um.user_key = li.USERNAME6JOIN cwd_user cu ON um.username = cu.user_name7JOIN cwd_directory cd ON cu.directory_id = cd.id8WHERE li.SUCCESSDATE < (getdate() - 180)9ORDER BY li.SUCCESSDATE;Bitbucket - SQL Server
1SELECT a.user_name, 2 dateadd(s, convert(bigint, b.attribute_value) / 1000, 3 convert(datetime, '1-1-1970 00:00:00'))4FROM cwd_user a, cwd_user_attribute b5WHERE a.id = b.user_id and b.attribute_name = 'lastAuthenticationTimestamp'Marketplace Apps
App | Vendor | Details |
|---|---|---|
resolution | Automatically deactivate multiple users Automatically remove users from groups Filter users on last activity date and deactivate manually Receive reports when users are deactivated | |
TechTime Initiative Group Limited | Automatic, regular, and efficient user management solution for Confluence. | |
Adaptavist | Ability to use script console to automatically deactivate inactive users when they have not logged in after a set amount of time. |
Resources
Was this content helpful?
Connect, share, or get additional help
Atlassian Community