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 Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published yesterday in Jira Software

How large do you think Jira Software can grow?

Hi Atlassian Community! My name is Shana, and I’m on the Jira Software team. One of the many reasons this Community exists is to connect you to others on similar product journeys or with comparabl...

215 views 4 9
Read article

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