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

This widget could not be displayed.

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 

This widget could not be displayed.

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

'to_timestamp' is not a recognized built-in function name.

 Getting the error as above, in our Jira database based on MSSQL

 

Maybe need to replace with any other parameters ?

Any thoughts, please ?

 

Thanks much in advance

Suggest an answer

Log in or Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted Wednesday in New to Jira

Are you planning to trial, or are currently trialling Jira Software? - We want to talk to you!

Hello! I'm Rayen, a product manager at Atlassian. My team and I are working hard to improve the trial experience for Jira Software Cloud. We are interested in   talking to 20 people planning t...

292 views 5 0
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