We need the ability to find all issues that have been resolved multiple times.


We used to have a re-work status in our workflows but this has been removed to facilitate better Scrum and Kanban functionality.

We thought we could get the same date using JQL but have run into a problem.

One of the reports that prod management would like is to see issues that have been in a resolved state multiple times.

I have a filter that will give them all issues in Re-work:

project=<project_name> and status in (New,Analysis,Design,Ready,In Progress) and status changed from (resolved,Closed,verified,accepted)

NOTE: our workflow has the following statuses: New, Analysis, Design,Ready, In Progress, Resolved, accepted, verified, and Closed.

This filter handles the Report of issues that are currently being re-worked i.e. have been in Resolved or farther in the workflow then pulled back for evaluation and re-work.

How do I generate a report that will look at how many times an issue has been in the resolved state.

Example: I have an engineer that is constantly under-performing and his work needs to be re-done. I want a report that lists Issues that are being re-opened multiple times.

Haven't been able to find any way to do this.



1 answer

It's easy to do with SQL for JIRA because it gives you access to the issue change history.

As the SQL query language is pretty flexible there are lot of ways to get the same data, one way might be by using two nested SQL queries:

A) The query below shows the times that the issues of a project have been in a particular state.

select i.key as "Issue", c.field as "Status", count(*) as "Count"
from issues i inner join issuechanges c on c.issueid=i.id 
where i.JQL='project=&lt;project key&gt;' 
group by i.key, c.field

B) now you might want to put the focus on some Status only:

select Issue, sum(Count) as "Total"
from (&lt;embed the SQL query above&gt;) 
where Status in ('resolved','Closed','verified','accepted')

C) So the final query would be:

select Issue, sum(Count) as "Total" 
	select i.key as "Issue", c.field as "Status", count(*) as "Count"
	from issues i inner join issuechanges c on c.issueid=i.id 
	where i.JQL='project=&lt;project key&gt;' 
	group by i.key, c.field
where Status in ('resolved','Closed','verified','accepted')


You might also want to use SQL for JIRA Reports & Gadgets extension to show the results on a JIRA Dashboard or the SQL for JIRA Custom Fields extension to show the times that an issue has been in a particular Status by showing the first query results (A) on a custom field table for each issue (and changing the embedded JQL accordingly, of course).



Suggest an answer

Log in or Sign up to answer
Community showcase
Published Nov 29, 2018 in Jira

How to set up an incident workflow from the VP of Engineering at Sentry

Hey Atlassian community, I help lead engineering at Sentry, an open-source error-tracking and monitoring tool that integrates with Jira. We started using Jira Software Cloud internally last year, a...

1,087 views 0 8
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you