Is there a way to count the number of "Reopened" tickets in a JIRA project?

[Ron Yun]

We have a lot of resolved issues fail QA testing and the status changed to "Reopened". I would like to know how to count the number of Reopened tickets.

[Ron Yun · Accepted solution]

Does anyone have any statistics showing average defect fix failure rates?

What I mean by "defect fix failure" is a JIRA issue that is resolved by software develops and subsequently fails QA testing (JIRA status is changed to "Resolved" by development and then changed to "Reopened" by QA)

I'm wondering what the industry average is, or what other people are experiencing? What is a good defect fix failure rate (2%, 5%, 10%, 20%, etc.)?

[Radu Dumitriu]

What, are you trying to revenge on developers ? Mine is below 2% mark. A large number of defects fix failure rate means that:

1) You do not have unit-tests OR

2) You do not have integration tests OR

3) You do not use continuous integration (see bamboo) OR

4) Your test coverage is below 30 % (see clover) OR

5) Your work env is so stressful (managers using get things done philosophy) that speed is more important than quality OR .....

-- insert lots of reasons like the above here ---

[Rebeca Luna]

Hi Ron, 

I'm currently looking for this solution in Jira Cloud as well.

Please let me know if you find a way.

Thanks in advance!

Rgs,

Rebeca Luna

[Dane Bigham]

Rebeca - did you ever solve this for Jira Cloud?  I am also seeking a way to count haw many times a ticket is reopened.

Thx!

-Dane Bigham

[Rebeca Luna]

Hi Dane,

I created a new field called "Reopened" and setup it as auto increment (using post function) in the reopen part of the workflow. So everytime the task is reopened, the field increment in one.

[Jayanth Tumati]

Hi Rebeca

Can you detail which post function you have used to increment the value ?

[Rebeca Luna]

Hi Jayanth,

I used this one: Increase value of field (JMWE add-on) - The value of the field will be increased by 1 unit.

Hope this helps!

Rgs,

[Jayanth Tumati]

thanks for reply, I am looking at the transition (from Resolved to Re-opened) to add this post function. Is this where it needs going in? or at the custom field level?

[Rebeca Luna]

It's exactly in the transition.

[giulia.perelli · Accepted solution]

JIRA CLOUD - no plugin

Has anyone tried to use a JIRA automation rule ?

I defined a numeric custom field 'Numero Ricicli' with ID =10119; default value is zero.

I'm testing the attached rule:

• WHEN check if the 'status' field changes value for transition issue or edit issue
• IF the issue type is Bug
• AND the Status equals 'Reopened Defect' value 
• THEN: I choose the Edit Issue Action to increment the numeric custom value of 1 (so, if current value is 0, it should be set to 1).
  My numeric custom field is not displayed in the drop down list, so JIRA forces me to wirte a JSON code on the 'Additional Fields' box.

I use the attribute 'field', because 'update' is for multiple values field.
Documentation on Advanced field editing (JSON) https://confluence.atlassian.com/jirasoftwarecloud/advanced-field-editing-json-993927646.html

Both math operators gives this error during rule execution.
I tried:

the sum operator {{#=}}{{issue.customfield_10119}}+1{{/}}
(https://support.atlassian.com/jira-software-cloud/docs/smart-values-math-expressions/)

and then 

the increment operator {{#increment}}{{issue.customfield_10119.value}}{{/}}(https://support.atlassian.com/jira-software-cloud/docs/use-smart-values-to-insert-numerical-values/)

Can anyone help me to solve the "Error while parsing additional fields. Not valid JSON. "?

Thanks a lot!!!
Giulia

[giulia.perelli]

[SOLVED WITH NATIVE FEATURE]

Hi community!

When my custom field became available in the drop-down list "Choose field to set..." in the "Edit Issue" action, I set the unit increment for the selected custom field and I left empty the "Additional fields" box.

I suggest to add an action to log the previous value; it's really helpfull in troubleshooting.

This automation rule works and the Customer appreciates a lot!

I love the Automation Rules for JIRA: no more human error on update the data and no more effort on repetitive tasks!
Jira Administrator can easly find  when the rule was triggered and what was the result. The audit log is a powerful tool to monitor any changes in the rule configuration and to troubleshoot any incongruent data by the Audit Log.

Hope it helps!

Bye,
Giulia

Note:

the trigger has changed from "Field value changed" to "Issue Transitioned" because there are more than one transition incoming in the Reopened status. I switch to "Issue Transitioned" for other reasons, nothing to do with the logic of reopening an issue.

[Ionascu Irina]

Thanks Giulia, I just tested it and it works! 

[giulia.perelli]

Hi Irina,
I'm happy to share some knowledge on JIRA automation rules!
Vote my post, if you like it, thanks.

Giulia 

[Yuriy Chepelyuk]

Hi,

You can use andvanced search and JIRA Query Language (JQL)

'status was reopened'

P.S. works for Jira v5.0 for sure

[Richard Meadows]

This doesn't account for the number of times one issue WAS reopened only that it had been reopened. Its better than nothing, but I've known times where an issue has been reopened several times.

[Suketa Sharma]

This worked for me:

reporter = currentUser() AND status was Reopened order by created DESC

[Rebeca Luna]

I need to know how many times reopened as well.

[Winfred]

I use an SQL query for this:

SELECT
        DATE_FORMAT(max(cg.created),"%d-%m") reopened
        , CONCAT("QC-", issuenum) issue
        , it.pname "type"
        , SUBSTRING(summary,1,40) summary
        , IFNULL(assignee,"") assignee
        , ist.pname "current status"
        , IFNULL(DATE_FORMAT(resolutiondate,"%d-%m"),"") "resolved"
        , DATE_FORMAT(max(updated),"%d-%m") "updated"
      , IF(count(issuenum)>1,concat(count(issuenum),"x"),"") "count"
FROM changeitem ci
JOIN changegroup cg ON ci.groupid = cg.ID
JOIN jiraissue i ON i.id = cg.issueid
JOIN issuetype it ON i.issuetype = it.ID
JOIN issuestatus ist ON i.issuestatus = ist.ID
WHERE NEWSTRING = "Reopened"
GROUP BY issuenum
ORDER BY max(cg.created) DESC
;

 which results in something like:

+----------+--------+-----------+------------------------------------------+----------+----------------+----------+---------+-------+
| reopened | issue  | type      | summary                                  | assignee | current status | resolved | updated | count |
+----------+--------+-----------+------------------------------------------+----------+----------------+----------+---------+-------+
| 22-05    | QC-23  | Bug       | Lorem ipsum dolor sit amet               | peter    | Reopened       |          | 22-05   | 2x    |
| 22-05    | QC-89  | Bug       | Fusce luctus faucibus est, at consect    | amy      | Reopened       |          | 22-05   | 2x    |
| 22-05    | QC-134 | Bug       | Lorem ipsum dolor sit amet               | amy      | Reopened       |          | 22-05   |       |
| 22-05    | QC-132 | Bug       | Fusce luctus faucibus est, at consect    | amy      | Reopened       |          | 22-05   |       |
| 22-05    | QC-69  | Bug       | Sed dapibus in eros et pellentesque      | peter    | Reopened       |          | 22-05   | 2x    |
| 22-05    | QC-91  | Bug       | Lorem ipsum dolor sit amet               | amy      | Reopened       |          | 22-05   |       |
| 22-05    | QC-104 | Bug       | Fusce luctus faucibus est, at consect    | peter    | Closed         | 22-05    | 22-05   |       |
+----------+--------+-----------+------------------------------------------+----------+----------------+----------+---------+-------+

[Loan Trần]

I don't know where input your SQL query?

Can you use andvanced search of JIRA Query Language (JQL) to show the reopened 1, 2 or 3 times bugs?

[Ron Yun]

Sorry, I wasn't clear in my question.

What I meant was, I need a count of all issues which have been Reopened at some point in their life (not just those currently in Reopened status). Issues may currently be Fixed, Closed, etc., but were Reopened at some point.

Thanks.

[Jobin Kuruvilla [Adaptavist]]

Combine the above with WAS.

[Herb Chan]

The above would only work depending on the version of JIRA. I think the WAS JQL feature was introduced in 4.3. Prior to that, you couldn't do an historical JQL search.

[Ionascu Irina]

Hi, 

I am using Jira Server and I also managed to solve this by using an automation rule in Jira. I created a number type custom field and then I created the automation rule which is checking all the tickets that went from "Resolved", "Closed" status to "Reopen" and increments that custom field. 

Hope this helps! 

Thanks, 

Irina 

[Bharat Kini]

Hi @Ron Yun

I use JIRA cloud. In my projects, we use statuses like "In progress" if the Dev's are working on the code and "Being tested" when the QA's are testing the ticket.

So to track how many tickets have been reopened I used the below JQL.

Here is it-> status changed TO "In Progress" FROM "Being Tested"

This query shows the tickets which have had their status moved from "Being Tested" to "In Progress" hence have been reopened in my projects.

[Emre Toptancı _OBSS_]

Hello All,

This is an old question but I see that it has been seeing a lot of action lately.

For anyone here who are interested in a ready-to-go solution, our team at OBSS created Time in Status app for this exact need. It is available for Jira Cloud, Server and Data Center.

Using Time in Status you can also see the number of times each status or transition was used (very useful for inspecting things like reopen counts).

You can report how much time each issue spent on each status, each assignee or even each user group. (useful for identifying bottlenecks in your process). You can consolidate multiple statuses into a report column (useful for calculating metrics like lead/cycle times). You can also list the status entry/exit dates.

The app calculates its reports using already existing Jira issue histories so when you install the app, you don't need to add anything to your issue workflows and you can get reports on your past issues as well.

Using Time in Status by OBSS, you can produce reports based on the default 24/7 calendar or define your own business calendars with custom workdays and working hours. 

All data can be exported as XLS, XLSX, CSV and can be accessed via REST API.

https://marketplace.atlassian.com/1211756

Emre Toptancı

[Ionascu Irina]

Hello, is there any way to measure this without the JMWE add on? I assume it would be possible with JMCF? Both of this add ons are coming with Groovy scripting, right? I was thinking that I can use the JMCF with a Groovy script.

Thanks!!

[David Fischer]

What are you trying to do exactly?

[Ionascu Irina]

I would like to measure somehow the exact times a issue was reopened. I saw above that one solution would be to use JMWE add on, but we do not have that one. So I was thinking using JMCF and create a custom field with a Groovy script to measure it. But I am not 100% sure this would work. Also, is there any other option to do this besides JMWE and maybe JMCF add ons? 

[Maria Kazakou {Appfire}]

Hi,

If you are using Jira Cloud, you can create a new custom field and configure the Calculated Fields post-function by JSU Automation Suite for Jira Workflows. In the reopen transition of the workflow you can set up the post-function to increment the value of your custom field by 1. So every time the task is reopened, the field will increment in one.

I hope this helps! Should you need further information or support, feel free to reach out to the Beecom Products team on our Service Desk.

Best,

Maria

[Ionascu Irina]

Hi Maria, I am sorry, I forgot to mention that I am using Jira Server, is this solution available for Jira Server also? Thanks! 

[Maria Kazakou {Appfire}]

Hi @Ionascu Irina ,

Unfortunately, the Calculated Field post-function is currently not available on Server or Data Center. 

Best, Maria

[David Fischer]

Hi @Ionascu Irina 

you can indeed achieve that with JMCF's Transition Count field type. No need for any Groovy script.

David

[Ionascu Irina]

Hi @David , thanks for the reply! Is this possible with Scriptrunner add on also? I saw that it also allows you to create custom fields and it has more functionalities also. 

[David Fischer]

Hi @Ionascu Irina , yes, most likely, but you'll have to write your own script using the Jira APIs. 

[Dzung Nguyen Trung]

Thank you Yuriy Chepelyuk,

Your anwsers is so helpful to me.

I have to count number of reopened defects for measurement objective and your query help me save a lot effort for it.

[Ehab Al-Qabbani]

where is that query please?

is this: ?

project = MYPRJ and status in ("Reopened" )

[Praveen N]

Where is the query ..??

[Rebeca Luna]

Where is the query?

[Ron Yun]

That does it!

Thanks much!

[MatthewC]

Just those in the Re-open state right now or those issues which have been re-opened at some point in it's life? WHat about multiple re-opens on one issue? Also, what version of Jira?

[Radu Dumitriu]

You mean using a filter like this ?

project = MYPRJ and status in ("Reopened" )

Page will show:

Displaying issues 1 to 3 of 3 matching issues

bla bla issues here ?