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- · Accepted solution]

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)

[Erwin Manuel]

Thanks Nic! this is exactly what we need

[Ramiro Pointis]

Hi @Erwin how you managed to make the query? Could you please share it? :)

[Erwin Manuel]

Hi Ramiro,

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.

[Ramiro Pointis]

Thanks Erwin, I'll give it a try :D