How To Join OS_HISTORYSTEP and OS_CURRENTSTEP Tables to changeitem?

Question

I have been reviewing the tables in JIRA Software trying to come up with a query to figure out how long issues had been in the different workflow states.

I can see the from/to states in the changeitem table and the start/finish dates in the OS_HISTORYSTEP and OS_CURRENTSTEP tables.

But the only way I can see to join these together is to match the changeitem groupid to the changegroup table's ID and then the changegroup's CREATED time to OS_*STEP tables' FINISHED_DATE.

Is there a better/easier way?

Shelli · Answer

Thanks for taking the time to respond.

I am trying to get the amount of time spent in all of the different workflow statuses for all issues for a given project.  We want this info to track average times in states, identify bottlenecks etc.

Andy Heinzer · Answer

Hi Shelli,

I'm not confident there is a clear way to join the specific tables you are referring to here.  But if you're looking for when issues changed a specific status in Jira Server, I would recommend checking out the KB How to obtain issue change history from database.   This guide is not officially supported by Atlassian, but it does provide you some example SQL queries you can use to find when issues transitioned statuses, such as

SELECT p.pname, p.pkey, i.issuenum, cg.ID, cg.issueid, au.lower_user_name, cg.AUTHOR, cg.CREATED, ci.FIELDTYPE, ci.FIELD, ci.OLDVALUE, ci.OLDSTRING, ci.NEWVALUE, ci.NEWSTRING
FROM changegroup cg 
inner join jiraissue i on cg.issueid = i.id
inner join project p on i.project = p.id
inner join changeitem ci on ci.groupid = cg.id AND ci.FIELDTYPE='jira' AND ci.FIELD='status'
inner join app_user au on cg.author = au.user_key
WHERE cg.issueid=(select id from jiraissue where issuenum = 115 and project in (select id from project where pname = 'Project name')) 
order by 1,3,4;

When you run this query it will only return the 115th issue in the 'project name' project.  If that project has a project key of say ABC, this would correspond to the issue key of ABC-115 in this case.  This will return to you in SQL all the status changes for that specific issue including the date/times when they happened.

I hope this helps.

Andy