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

Angie Goodman December 8, 2017

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()'.

9 answers

3 accepted

15 votes
Answer accepted
Eero Sario April 4, 2019

For Jira Cloud, use 

assignee not in membersOf("jira-software-users") and statusCategory != Done
Angie Goodman April 10, 2019

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

Like # people like this
knedlik May 31, 2022

uu, nice thanks! :)

Sergei Gridnevskii
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 9, 2023

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

Like # people like this
Anne Saunders
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 22, 2023

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

Like Ignacio Pulgar likes this
Blaise Rey-Mermet November 13, 2023

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

3 votes
Answer accepted
Marc Luijting February 16, 2018

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 May 6, 2023

It complains group not found.

0 votes
Answer accepted
Ignacio Pulgar
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 8, 2017

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 December 8, 2017

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 8, 2017

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 December 8, 2017

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

Ignacio Pulgar
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 8, 2017

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 9, 2017

I've just tested this on Cloud:

inactive.jpg

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 April 10, 2019

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!

Like # people like this
31 votes
Chris Bell June 18, 2019

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 January 8, 2020

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

Like # people like this
Girish Patel January 9, 2020

Thank You!

Like Jennifer Forrest likes this
Jamie Schork May 12, 2021

Massively helpful on Server.  Thanks!

Like Jennifer Forrest likes this
Yulia Ploshevsky January 12, 2022

Thank you!

Like Jennifer Forrest likes this
Dustin.DeBres August 23, 2022

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.

Like Jennifer Forrest likes this
Howell_ Adrianna March 9, 2023

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

Like Jennifer Forrest likes this
Jennifer Forrest August 28, 2023

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

GeeLong October 4, 2023

Great! This works. Got what I need. 

14 votes
Gautham Hari December 9, 2020
  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 February 1, 2022

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

Stephan Georgiev-Ashkenazy January 5, 2023

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.

3 votes
Abraham Castillo February 16, 2023

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 April 24, 2023

Thanks, this one worked for me. @Abraham Castillo  

Sowjanya Mupparaju September 14, 2023

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

Odette Vuletich October 19, 2023

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

1 vote
Sarah Stemann June 6, 2018

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

0 votes
Paul Grant March 31, 2023

assignee id still works ok - thanks 

Archit Bohra April 24, 2023

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 24, 2023

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

0 votes
Ganga Bopaiah January 23, 2023

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

Stephan Georgiev-Ashkenazy January 24, 2023

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 January 25, 2023

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.

Suggest an answer

Log in or Sign up to answer