Search for issues where LDAP based users from been deleted

[Brent Webster]

I've found/read numerous "answers" where LDAP users have been deleted by a corporate LDAP admin because they have left the company, been fired, contract was up, etc. The Jira Admin had no time to perform a bulk change.

Some answers alluded to creating a local jira user so a bulk change could be done. Great but what happens if you don't know what ldap users have been deleted so I want to perform a search. Well, if you use the gui to edit the issue, the assignee|reporter fields show up as 'unassigned'. If you display the issue, assignee|reporter fields show the 'userid' and not the full name of the user.

So what JQL syntax can I use to find issues that are assigned or reported by users that have been deleted in LDAP?

[Tiago Comasseto · Accepted solution]

Hi Brent,

I'm not sure if it's possible to be done through JQL, but you extract this information directly from your database using this query:

select assignee from jiraissue where assignee not in (select user_name from cwd_user);

I hope this has helped.

Cheers

[Brent Webster]

Thanks for the quick response. I'm a little rusty with mysql but I ran the query with the addition of "select assignee,pkey" and got my list of effected users and their associated issues.

I'll leave the question open in hopes that I can get a JQL query.

[Renjith Pillai]

I guess, JQL is doubtful, you need to mostly go via the database way.