Bulk User Deactivation in Jira

Hi,

 

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.

 

Thanks,

Ashley

2 answers

Hi Ashley,

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.

Regards,
Rodrigo Rosa

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

Hi Rodrigo, Any updates Thanks, Ashley

Hi Rodrigo,

For the first query I am getting below error.

ORA-00902: invalid datatype
00902. 00000 - "invalid datatype"
*Cause:
*Action:
Error at Line: 3 Column: 44

Hi Rodrigo, 

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?

Thank you, 

Mihaela 

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");



Thanks,

Rod

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,307 views 14 20
Join discussion

Atlassian User Groups

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!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot