How to find out the number of reopen actions on closed issues

[Enkhtaivan Ganbat]

Hello Atlassian support,

How can I find out the number of reopen actions on closed issues? Same issue can be closed and reopened more than once, so what I need is to find out that reopen action number on all issues and then filter it by Q1, Q2, Q3, Q4 and by year.

Thanks in advance.

[Sergey · Accepted solution]

Hey Enkhtaivan,

You would need to run a SQL query to obtain the information you're after.

JIRA issue history is stored in changeitem table, but you will need to join other tables in SQL fields like Issue KEY are derived and it might not be as straight forward as it may sound.

Try the following query

{code}
SELECT
CONCAT(p.pkey,'-',ji.issuenum),ci.id,cg.created,ci.field,ci.oldstring,ci.newstring
FROM
jiraissue ji join project p on ji.project=p.id join changegroup cg on cg.issueid=ji.id join changeitem ci on ci.groupid=cg.id

WHERE ci.oldstring = "CLOSED";
{code}

Hope this helps. Let us know how you go.
Cheers,

Sergey

[Sergey]

p.s. additionally - if the above query doesn't return any rows - try removing the WHERE statement just to check what exactly the "OLD" status string is and then adjust the query accordingly

Cheers,

Sergey

[Enkhtaivan Ganbat]

Hello Sergey, 

Your query worked!, it didn't return anything as you predicted, but I adjusted the "where" part a little as you suggested after looking at the result of select * from changeitem; Here is the adjusted version of your query:

{code}

select concat(p.pkey,'-',ji.issuenum) as issueid, ji.created, ci.oldstring, ci.newstring, ji.SUMMARY, ji.DESCRIPTION, ji.REPORTER, ji.ASSIGNEE
from jiraissue
ji join project p on ji.project=p.id
join changegroup cg on cg.issueid=ji.id
join changeitem ci on ci.groupid=cg.id
where ci.FIELD="status" and ci.NEWSTRING="Reopened" and ji.created <= "2018-03-31" and ji.created>="2018-01-01"
order by ji.issuenum desc;

{code}

Thanks a lot!

[Sergey]

Great! Happy to hear the query worked for you and  thanks for sharing your experience with the community! :)

Cheers,

Sergey

[Dave]

Hi Enkhtaivan,

You can run a JQL using the status changed from function and then using the during to set your dates, example below using statuses Closed and Open (you can use any status you have in mind)

project in (exampleproject) and status changed from "Closed" to "Open" during (endOfMonth(-4), endOfMonth(-1))

This will at least give you how many issues was re-opened during the timeframe you specified, in the example it's a 3 month period.

Please see our page - advanced searching function reference for more functions and how to use them.

Hope this helps,

Cheers,

Dave