nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Accepted answers

Sergey_k6s8t8

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

All comments

Sergey_k6s8t8

Hey Enkhtaivan,

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

Sergey

Sergey_k6s8t8

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

Dave_cpia7h

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

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_k6s8t8

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

Cheers,

Sergey

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

jira-service-desk