Finding Issues Owned by Inactive Users with JQL without buying additional stuff

[Angie Goodman]

How can I search for issues assigned to inactive users in JIRA cloud?  We have to make people inactive when they leave (because we get charged by the user).   I found this link: https://scriptrunner.adaptavist.com/latest/jira/jql-functions.html#_user_functions in a response to a similar question, and it seemed to say that you can just put assignee in inactiveUsers() in your JQuery, but when I do that I get a message Unable to find JQL function 'inactiveUsers()'.

[Eero Sario · Accepted solution]

For Jira Cloud, use 

assignee not in membersOf("jira-software-users") and statusCategory != Done

[Angie Goodman]

I like the statusCategory != Done.  Initially, I had all categories listed.  Thanks!

[knedlik]

uu, nice thanks! :)

[Sergei Gridnevskii]

It works only if you delete users from Jira when they retire. It does not work if they were disabled.

[Anne Saunders]

@Sergei Gridnevskii that JQL still should work if you remove them from the group, though. Heck, I may move our deactivated users into their OWN group...

[Blaise Rey-Mermet]

This JQL using membersOf() also works for me on Data Center.  Thanks for that. 

[Marc Luijting · Accepted solution]

How about this one?

assignee not in membersOf("access-jira") 

It shows all issues that have been assigned in the past to users that are now no longer active. You could narrow down your search by also filtering only on issues that are still open.

[Anuradha Das]

It complains group not found.

[Ignacio Pulgar · Accepted solution]

The JQL function inactiveUsers() is provided by ScriptRunner plugin, and it won't work unless your JIRA instance has that additional addon, which seems not to be the case.

There's no easy native alternative.

A workaround could be adding inactive users to a group (ie: inactive-users) for flagging those users as inactive.

Once all inactive users were in that group, you would be able to get all issues assigned to an inactive user by executing this JQL query:

Assignee in membersOf ("inactive-users")

[Angie Goodman]

This would only work if the person was active.  They are still members of groups, so I tried to search by "Assignee in membersOf ("HCP") and assignee = m..." And all of the "M" names that are active came up but not hers.

I think we would just have to re-activate her briefly to do the search, then de-activate her immediately.  It just seems counterintuitive that we can't search for things assigned to inactive persons.

[Ignacio Pulgar]

Mmm... Found this:

https://confluence.atlassian.com/jirakb/search-for-inactive-users-346947771.html

The workaround then would be querying against the database.

The KB article above contains the SQL sentence for getting the list of inactive users, so now you just need to build a longer SQL sentence using the one in the KB as a subquery of a bigger query.

[Angie Goodman]

I don't think we have db access since we are using the Cloud version.

[Ignacio Pulgar]

Right, querying the DB is not possible on Cloud.

I'd suggest opening a ticket with Atlassian Support so that they perform the query for you:

https://support.atlassian.com/contact/#/

Alternatively, I think you may develop a solution which queried one of the JIRA REST API's:

https://docs.atlassian.com/software/jira/docs/api/REST/1000.1568.0/

[Ignacio Pulgar]

I've just tested this on Cloud:

So, you may:

1. save a filter like: assignee in membersOf("inactive-users")
2. add a Pie Chart gadget to a dashboard based on the assignee field, set to use the filter created in the previous step.

The pie chart will show the list of assignees in the group inactive-users, (checked it works too) and their names will contain a link to view all issues assigned to that inactive assignee.

Of course, this approach requires some manual work for adding inactive users to the inactive-users group, but that's the easiest workaround I can think of.

You may easily find all inactive users by appropriately modifying the following link:

https://<YOURS>.atlassian.net/admin/users?activeFilter=inactive

You may want to establish the practice of including users into that group at the time of user deactivation, so that you don't have to look for inactive users afterwards.

Hope it helps.

[Angie Goodman]

I tested and this is possible IF we assign users to inactive-users group when they leave.  I was surprised it still worked when the user had all access revoked.  Thanks!

[Chris Bell]

Use this JQL statement to display all issues assigned to inactive users.

assignee in (inactiveUsers())

If you wish to reduce the results to display issues which are incomplete, include the statusCatgory as suggested by @Eero Sario :

assignee in (inactiveUsers()) AND statusCategory != Done

[Nigel Lee]

listing using inactiveUsers() also includes unassigned tickets,  so you'd need to filter these results out with  "AND assignee != unassigned"

[Girish Patel]

Thank You!

[Jamie Schork]

Massively helpful on Server.  Thanks!

[Yulia Ploshevsky]

Thank you!

[Dustin.DeBres]

OMG Thank you for this! I'm cleaning up the "Customers" who were added as "Service Desk Agents" Now I'm cleaning up the tickets with your filter.

[Howell, Adrianna]

The other "accepted" solutions did not work for me, but this one did.  Thank you Chris!

[Jennifer Forrest]

@Chris Bell THANK YOU SO MUCH!!!!!!

[GeeLong]

Great! This works. Got what I need. 

[Sascha Stegmüller]

Works for me!

[Gautham Hari]

1. First, run the following query in Jira Cloud: 
   assignee in inactiveUsers() ORDER BY assignee ASC 
2. Then move the cursor over the Assignee name in one of the results and click on the 'View profile' button in the pop-up window.
3. Then copy the ID of the user from the URL which will be in the format https://company.atlassian.net/jira/people/ID.
4. Finally, paste the ID and run the following query:
   assignee in (ID) OR reporter in (ID) OR creator in (ID) OR watcher in (ID) ORDER BY updated DESC
5. After step 2, you could also click on the 'View all' link which is located next to the 'Worked On' section of the user profile page. Then click on the Jira link at the bottom of the page 'Search for more in Confluence or Jira' which generates the query shown in step 4.

[Carolina SILVA]

hello @Gautham Hari  for the assignee in inactiveUsers() ORDER BY assignee ASC  command, it's what I looked for 

[Stephan Georgiev-Ashkenazy]

Hello @Gautham Hari ,

This query works just fine: assignee in inactiveUsers() ORDER BY assignee ASC

But the point here is how to filter only the tickets for one specific deactivated user. Let's say that the count of them is 500 and I would like to find only the ticket created by one specific person, in order to assign them to another colleague.

I tried different options but looks like this function doesn't accept any arguments. If you have to share any additional details, I will be grateful.

I personally succeeded to narrow the search results by:

1. Putting the membersOf("the team") - this reduced the all found results only to this specific team.

2. The adding inactiveUsers() function with the AND operator helped to reduce the final result.

3. And of course, adding some additional criteria like filtering by status or creation date range can make the final result even better and smaller.

[Abraham Castillo]

I found this works but only if you know the email the deactivated user(s) used to have:

assignee = "name.lastname@company.com"

[Archit Bohra]

Thanks, this one worked for me. @Abraham Castillo  

[Sowjanya Mupparaju]

Thank you so much this worked for me! @Abraham Castillo 

[Odette Vuletich]

@Abraham Castillo this here is the BEST solutuion - thank you!

[Orcun Akdemir]

For me I used another workaround because 

Assignee in membersOf ("inactive-users")

didn't work for me.

I opened one of the dashboard's we use and select quick filters: (you can get user ID from somewhere else also)
I selected the inactivated user from the quick filters. Then on the browser address bar, I find the Assignee ID something like this:
/jira/software/c/projects/ABC/boards/9999?assignee=abcdefg12344556(id)
then copied that ID and use it in JQL string

 assignee in (abcdefg12344556) AND project = ABC

 another way of doing it I guess. 

[Ganga Bopaiah]

use: reporter in (inactiveUsers()) AND reporter = "username" ORDER BY created DESC to get issues created by a particular inactive user.

[Stephan Georgiev-Ashkenazy]

Thank you @Ganga Bopaiah .

The point with the reporter = "username" is not relevant here because the username doesn't exist and it causes to system error.

[Ganga Bopaiah]

No, in place of username add the text that an inactive user is displayed with. For example: in our Jira when a user leaves, the user details like first name, last name, even username is not searchable, but the user id, like their employee number which still shows on the ticket that was assigned to the user (now inactive), can be used in the query.

Even better, save "reporter in (inactiveUsers())" in a filter, use the filter in a dashboard gadget like a pie chart that's divided on the reporter. Click on the reporter you are looking for; it opens in a JQL filter and you will have the right filter query.

[Sarah Stemann]

I used a filter like  "assignee not in ([list of active users])" and it worked without any changes to user group assignments

[Paul Grant]

assignee id still works ok - thanks 

[Archit Bohra]

assignee id didn't work for me. Maybe id tables were changed later. I see some change in the backend storing user profile pics (that's where I got the id from.. html source)

[Anne Saunders]

@Archit Bohra See the top accepted answer here - I've been assigning terminated users to an "Inactive Users" group with no product access, and it works like a charm.