Use transitions activity in issue navigator and as issue field

Molly Favret

Hi, I am interested in using information that is captured within the "Transitions" tab in an issue that records all workflow transition executions, users, duration in a particular step of the workflow and the date and time when the transition was executed.

I have a need to capture and report on some of this information, particularly the duration in each step.

I imagine that all the information is being captured in the system, so how would I use these additional fields in a filter in the issue navigator? Can I capture this as a field in an issue as well?

Daniele Gagliardi

Hi Molly, a collegue of mine and I have found two ways: querying history workflow tables or querying history tables. Below both solutions:

Querying changes history tables:

select ji.pkey,ji.summary,ji.created,cg.created as update_on,ci.oldstring,ci.newstring
from changegroup cg
inner join jiraissue ji on ji.id=cg.issueid
inner join changeitem ci on ci.groupid=cg.id
where ji.pkey = 'ISSUE_PKEY'
and ci.field='status' order by cg.created asc;

Querying workflow history tables:

select ji.created, osh.start_date, finish_date, osh.status
from OS_HISTORYSTEP osh
inner join jiraissue ji on ji.workflow_id = osh.entry_id
where ji.pkey = 'ISSUE_PKEY';

Using OS_CURRENTSTEP you will find also informations related to current step (missing from the query involving OS_HISTORYSTEP).

In my case the last query, maybe due to common transitions in my workflows, reported some wrong states, using the value of the attributes old-state in xml workflow definition, I've used the first of the two.

Hope this can help you

Regards

Daniele Gagliardi