How to find any JIRA data associated with deleted users

Question

We are using project configurator to export JIRA projects from one system and import into another. We discovered that the source system is missing a tremendous amount of users associated with data, like issues, filters, etc. The projects won't move over unless we add the missing user. We then disable them. They were apparently programmatically deleted during a mass change in the system shifting to a new SAML method.

How can we find all the deleted users associated with project data, so we can not have to discover these project by project.

Thanks,
Jay

Max Evens · Answer

Great answer!

bijay.rungta · Answer

If you can find any past JIRA Ticket where the deleted user has participated as in the user was reporter or assignee or had added a comment to the ticket then there is a way to get the User Id.

Use the JIRA REST API to get the details of the Task.

Just open the below Url (Replace base Url and JIRA Id) in a browser where you are logged into JIRA.

https://<baseUrl>/rest/api/latest/issue/JIRA-XYZ

It will have the details of the assignee, reporter, all comments and the details of their authors along with their accountId

Once you have the accountId, you can find issues where they had participated and also see their profile page.

Reference: https://developer.atlassian.com/server/jira/platform/rest-apis/

davy · Answer

Hi Jay,

Since the username of a user is also stored on the table jiraissue, it is possible to retrieve the usernames of the deleted users that still have some association to the project.

This sql query will give you the usernames of the deleted users that have issue assigned

select distinct(ji.assignee), au.user_key, au.lower_user_name, p.pkey, p.pname from jiraissue ji left join app_user au on ji.assignee = au.user_key left join project p on p.id = ji.project where au.lower_user_name not in (select lower_user_name from cwd_user)

This sql query will give you the usernames of the deleted users that are mentioned as reporters

select distinct(ji.reporter), au.user_key, au.lower_user_name, p.pkey, p.pname from jiraissue ji left join app_user au on ji.reporter = au.user_key left join project p on p.id = ji.project where au.lower_user_name not in (select lower_user_name from cwd_user)

Be aware that depending on how many issues you have on your instance, these queries can be fairly heavy

Best Regards,