DB Query to find number of time an issue got reopened


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!

2 answers

1 accepted

Accepted Answer
0 votes

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)

Thanks Nic! this is exactly what we need

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

Hi Ramiro,

You can use something similar to

	pkey 'Issue ID'
	,computeReopen.[Count Reopened]
from jiraissue ji
inner join (
		,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.

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

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Sep 25, 2018 in Jira

Atlassian Research Workshop opportunity on Sep. 28th in Austin, TX

We're looking for participants for a workshop at Atlassian! We need Jira admins who have interesting custom workflows, issue views, or boards. Think you have a story to sha...

464 views 7 5
Join discussion

Atlassian User Groups

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!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you