DB Query to find number of time an issue got reopened

Erwin Manuel

Hi,

We would like to create a query wherein I can compute how many times an issue has been reopened. e.g. result:

Issue | Reopened count

abc-1 | 3

abc-2 | 2

abc-3 | 1

This should be available in DB as Jira have history but I could not find which table to look.

I have tried looking in table OS_HISTORYSTEP using:

select top 1000 * from OS_HISTORYSTEP h
left join jiraissue i on i.WORKFLOW_ID = h.ENTRY_ID

But, it doesn't seem to contain the records of the transition from Resolved to Reopened. Can someone point me to the right direction? Thanks in advance!

Nic Brough -Adaptavist-

The OS_ tables are workflow historym and you can extract it from there.

The Jira "what has changed" tables are a bit more nice to work with though (I think it's because you can see a simple relationship between the tables and what a user sees on the issue "history" tab)

Look for ChangeItem and ChangeGroup.

The group is a header for the change, it holds the issue, the person who made the change and the date/time. So a "select * from changegroup where issue = jiraissue.id" will give you a list of all the groups of changes.

Once you've got a group, you can use "select * from changeitem where group = changegroup.id" to get a list of all the fields that were changed during that change. You'll be able to see "status" in there, with it's old and new values.

(Note - I'm not a DBA, I only know the basics of SQL, so I'll leave the best way to build your query up to you based on the relationships I've just scribbled)