Get Issue Status History into Data mart

Hi, We are building a datamart with all the issues and their status history, including elapsed time in each status. We are having issues aligning the status history on the web interface with the result from the database query.
Our query is as follows (which I adapted from the forum).
select prj.pname as project
, ji.issuenum as issuenum
, isc.pname as issue_status
, ji.workflow_id
, osh.id as history_status_id
, osh.action_id as history_actionid
, osh.start_date as history_start_date
, osh.finish_date as history_finish_date
, osh.status as history_status
, osh.caller as history_user
, osh.finish_date - osh.start_date as elapsedtime
from jiraissue ji
, os_historystep osh
, os_currentstep osc
, project prj
, issuestatus isc
where ji.workflow_id = osc.entry_id
and osc.entry_id = osh.entry_id
and ji.project = prj.id
and osh.step_id = isc.sequence
AND JI.project = '15602'

Below are the result from the query which clearly shows a different status from the UI transition tab.

What could I be missing?  Thanks for the kind help.
ORDER BY ISSUENUM DESC;
ETFI-2204.PNGETFI-2204_SQL.PNG

2 answers

1 accepted

0 vote
Vasiliy Zverev Community Champion Jun 21, 2016

Because you analise workflow itself, but you need to investigate change history. Here is an example how to get all change histories for all issues in JIRA:

Select
	jiraissue.*
	, changegroup.*
	, changeitem.*
from
	jiraissue
	join changegroup	
		join changeitem	on	changegroup.ID = changeitem.groupid
	on jiraissue.ID = changegroup.issueid

Hi Vasilliy,

Fantastic!  It did work and I am getting the output we expect.

Thank you for your help.

Best Regards

Erwin

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 Thursday in Jira

5 ways you can make the most of Jira Software and Bitbucket Cloud

As part of the Bitbucket product team I'm always interested in better understanding what kind of impact the use of our tools have on the way you work. In a recent study we conducted of software devel...

64 views 0 5
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