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 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