DB Query to find number of time an issue got reopened

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!

2 answers

1 accepted

0 vote

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

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.

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

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,327 views 14 20
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
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot