Bulk User Deactivation in Jira

[ashleyg]

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

[Rodrigo Dominguez]

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

[CST JIRA Confluence Admin]

'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

[Rodrigo Rosa]

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

[ashleyg]

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

[ashleyg]

Hi Rodrigo, Any updates Thanks, Ashley

[DOTS Team]

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

[Mihaela Gosman]

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 

[Scott DeLeeuw]

Is there a user cache upon deactivation?  When I make that change in the DB it's not reflected in the GUI.  But if I make the change in the GUI it's reflecting there and in the DB right away.

[Scott DeLeeuw]

If you are using Oracle and getting invalid datatype, you might need this query instead.

SELECT u.user_name as "Username",
u.display_name as "Full Name",
TO_DATE('19700101','yyyymmdd') + ((a.Attribute_Value/1000)/24/60/60) 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_DATE('19700101','yyyymmdd') + ((a.Attribute_Value/1000)/24/60/60) <= current_date - 365 OR a.attribute_value IS NULL
ORDER BY a.attribute_value;

[MikeA]

I figured out how to refresh the user cache - I was also having the issue of setting the user to 'inactive' using the sql script with the user showing as 'active' in the GUI. 

You can refresh the user cache by restarting the JIRA service or by doing the following:

1. Created a new Internal user directory with LDAP auth. I left it at the last rank and didn't actually use it for anything.
2. Disabled and deleted that user directory