Is there a way to bulk deactivate users in JIRA may be using database query or something.
I have around 1000-1500 users which need to be deactivated in JIRA and just deactivating them one by one would really be a highly cumbersome task.
Please suggest me something if possible.
Do you have a list of users you need to delete or you need to retrieve them first? If you need to retrieve them, I believe the query below may help you and will return the list of users that either not logged into JIRA at all, or have not logged over a year.
SELECT u.user_name as "Username",
u.display_name as "Full Name",
to_timestamp(CAST(a.attribute_value as bigint)/1000) as "Last Login"
FROM cwd_user u
LEFT JOIN cwd_user_attributes a ON u.id = a.user_id AND attribute_name = 'login.lastLoginMillis'
WHERE to_timestamp(CAST(a.attribute_value as bigint)/1000) <= current_date - 365 OR a.attribute_value IS NULL
ORDER BY a.attribute_value;
And this is the update to deactivate the users returned by the query above:
update cwd_user u
set active = 0
WHERE u.id in (SELECT u2.id
FROM cwd_user u2
LEFT JOIN cwd_user_attributes a ON u2.id = a.user_id AND attribute_name = 'login.lastLoginMillis'
WHERE to_timestamp(CAST(a.attribute_value as bigint)/1000) <= current_date-365 OR a.attribute_value IS NULL)
Please note, we always recommend you to take a backup before running an update query because it will change values directly in the Database, so please, before running the query, take a new backup.
Hi Rodrigo, Thanks for the quick response! I already have the list of those users but they are say 1000-1500 in number. So is their a way that comma separated values of their username can be placed in update query to deactivate them directly through database. Thanks, Ashley
We need to disable multiple users in Jira that haven't logged in in the past 3 months. I've created a Jira test intance with a dummy database that it's a replica of the real database.
I've ran on my test database the first query to extract the users that haven't logged in the past 90 days using current_date - 90
The output of the query contains 625 users.
Then I ran the seconf query to dezactivate the inactive users:
625 rows were updated and all the users have now active set to to 0. I've restarted the jira service but unfortunately the users are not disabled in Jira user interface.
Can you please let me know if I missed something?
How to deactivate users in bulk --
First we do a lil' poking around to learn total number of INTERNAL and LDAP users on the JIRA instance.
# TOTAL LDAP USER COUNT
SELECT count(*) from cwd_user where directory_id = 3;
# LDAP ACTIVE USERS
SELECT count(*) from cwd_user where directory_id = 3 AND active = 1;
# LDAP INACTIVE USERS
SELECT count(*) from cwd_user where directory_id = 3 AND active = 0;
# INTERNAL ACTIVE USER COUNT
SELECT count(*) from cwd_user where directory_id = 1 AND active = 1;
# INTERNAL INACTIVE USER COUNT
SELECT count(*) from cwd_user where directory_id = 1 AND active = 0;
Now deactivate all accounts, INTERNAL and LDAP...
# DEACTIVATE ALL INTERNAL ACCOUNTS
UPDATE cwd_user SET active = 0 WHERE directory_id = 1;
# DEACTIVATE ALL LDAP ACCOUNTS
UPDATE cwd_user SET active = 0 WHERE directory_id = 3;
Now only activate the specific accounts needed...
# ACTIVATE SPECIFIC JIRA-ADMIN LDAP ACCOUNTS AND INTERNAL ACCOUNTS
Here we use "jiraadmin" as the internal JIRA Admin account and enable LDAP JIRA Admin accounts for Joe Lopez and Jay Smith. These are the accounts used by systems administrator, etc..
UPDATE cwd_user SET active = 1 WHERE user_name IN ("jiraadmin", "jlopez", "jsmith");
Connect with like-minded Atlassian users at free events near you!Find a group
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no AUG chapters near you at the moment.Start an AUG
We're bringing product updates and pro tips on teamwork to ten cities around the world.Save your spot