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!
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)
You can use something similar to
select pkey 'Issue ID' ,computeReopen.[Count Reopened] from jiraissue ji inner join ( select cg.issueid ,COUNT(ci.ID) as 'Count Reopened' from changeitem ci left join changegroup cg on cg.ID = ci.groupid where ci.FIELD = 'status' and cast(ci.NEWSTRING as varchar(1000))like 'Reopened' group by cg.issueid ) computeReopen on ji.ID = computeReopen.issueid where ji.PROJECT = (select ID from project where pkey = 'AAA') order by pkey
Hope this helps.
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!
Unfortunately there are no AUG chapters near you at the moment.Start an AUG
We're bringing product updates and pro tips on teamwork to ten cities around the world.Save your spot