How to Clear JIRA Internal Directory?

[Tony Montana]

Hello

After projects migrate JIRA created useless users from first instance to JIRA Internal Directory. How to clear her now?

[Matti K · Accepted solution]

Hi @Tony Montana

I know this is an old question, but here goes...

If the users you wish to remove are tied to actions in Jira (comments, ticket create/edit, attachments, watches, status changes, workflows, ... etc) then there is no way to do it without breaking you instance (mentioned in the linked posts). You have a 3 options:

1. Reassign all actions to a new/different user and then delete the unwanted user
2. Remove all actions performed by the unwanted users (you mentioned that you don't want to do this, so not really an option)
3. Disable the unwanted users and deal with the fact that you've got some legacy crap left over in your system.

Option 3 is the best option. But...

If you go with option 1, then you'll need to look in the app_user table for the user_key of each of the unwanted users. You'll then need to map that key to a new user. This could be a generic account that you create to attribute the legacy actions to or a 1:1 map of new system users that correspond to the legacy users from your migration. Since you're asking for a query, it could look something like this:

Get bad user keys

select user_key
from app_user 
where lower_user_name in('badUser1','badUser2','badUser3',...)

Get Good User keys

select user_key
from app_user 
where lower_user_name in('goodUser1','goodUser2','goodUser3',...)

Assuming that badUser1 is replaced by goodUser1...

• update the jiraissue table (replacing reporter and assignee fields

update jiraissue
SET reporter = 'goodUser1'
WHERE reporter = 'badUser1';

update jiraissue
SET assignee = 'goodUser1'
WHERE aassignee = 'badUser1';

• update the jiraaction table (this is where all attachments/comments/ticket actions are stored)

update jiraaction
SET author = 'goodUser1'
WHERE author = 'badUser1'

If you also want to preserve meta-actions like watches, vote, work logs etc, you'll need to do the same kind of thing in the following tables:

• userassociation
• worklog
• changegroup

This list is by no means exhaustive, so you'll want to look at the schema to see what other tables need to be touched: https://developer.atlassian.com/server/jira/platform/database-schema/ 

Hint: look for tables with "author" columns.

As you can see, your ask is not trivial and isn't something accomplished by a single query. Jira is designed to track all things related to each issue, so changing bits/pieces of issue-related info is cumbersome and not recommended.

[Tarun Sapra]

Hello @Tony Montana 

Your requirement has been previously discussed on the portal, please go through the following links

https://community.atlassian.com/t5/Jira-questions/Bulk-delete-users/qaq-p/360626

https://confluence.atlassian.com/cloudkb/perform-a-mass-delete-of-users-691012067.html

https://community.atlassian.com/t5/Jira-questions/Bulk-delete-users/qaq-p/724342

[Tony Montana]

Its no duplicate. We need query for db to remove user, but! without remove comments and issues