JIRA Data into Microsoft PowerBI - Database Table Relationship Question

Question

Hello -

I have recently begun to experiment with my JIRA data to be visually represented in Microsoft PowerBi. I am beginning to understand how some of the database tables relate in order to create relationships.

An example of a relationship in PowerBI is importing tables jiraissue and customfieldvalue. I then establish the relationship with jiraissue.ID to customfieldvalue.ISSUE.

I am trying to answer the question of "from the date an Issue had arrived on a particular status, how long did it take for that Issue to get resolved?" I am struggling with this at the moment.

Does anyone know which database tables / relationships I should be looking into in order to determine the dates of when Issues were transitioned to statuses? I am lost at the moment.

I'd be very grateful for any advice / direction through JIRA's database.

Andy Heinzer · Answer

Hi,

This can be tricky to do with Jira's database until you get more familiar with the tables in there.  Even I can't remember where everything is, I have to frequently lookup the structures and relearn the layouts when trying to do this.   However I found a helpful KB that can guide you in some regards with trying to find this information.  Please see How to obtain issue change history from database

In this guide there is a section on only getting the status changes to a specific issue for a specific project:

* To get only STATUS changes:

STATUS CHANGESSELECT 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;

To make this query work, you need to change two things, both are on the 2nd the last line of the query:  The issuenum value needs to be the number of the issue in that project  AND you need to enter the project name value.   So for example, I have a project called 'Sample Kanban Project' (with a project key of SKP) and I want to see what the status changes for the 18th issue in that project.  This means the issue key for this issue is SKP-18.  I changed my issuenum=18 and project name (pname) to be 'Sample Kanban Project'.  So my query looked like this:

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 = 18 and project in (select id from project where pname = 'Sample Kanban Project')) 
order by 1,3,4;

In turn I got the results of

* "Sample Kanban Project";"SKP";18;10106;10214;"admin";"admin";"2019-03-06 09:07:31.113-06";"jira";"status";"10000";"To Do";"3";"In Progress"
* "Sample Kanban Project";"SKP";18;10107;10214;"admin";"admin";"2019-03-08 18:59:31.113-06";"jira";"status";"3";"In Progress";"10001";"Done"

Which only shows two entries for this issue, but that's all the more the status has changed for this particular issue.  The first is when the issue when to in progress, the latter is when it was changed to the Done status.  In this case the changegroup.CREATED field is the timestamp of the change itself, not when the issue was created.  We can in turn use those dates to figure out the distance between statuses.  In this case it was just short of 2 days and 10 hours.

In my case I know this works in postgresql.  It is possible that the sql syntax might need adjustments for different database types to find the same kind of data.

I hope this helps.

Andy