JIRA captures changes on an issue in its "change history". It is pretty easy to find them by going to the change history tab on the view issue page.
But often, we would like to find out specific details about the various workflow statuses that an issue has gone through in its lifecycle. Going through the change history and identifying the status changes is a painful task when there are tens of hundreds of changes on an issue. People normally write plugins to get around this, or go directly to the database.
Even when it is achieved using plugins, the background logic is to look at the tables in the database. In this recipe, we will look at the tables involved and write the SQL query to extract workflow changes for a given issue.
Make sure you have an SQL client installed and configured that will help you to connect to the JIRA database.
Follow these steps:
- Connect to the JIRA database.
- Find out the
idof the issue for which you want to extract the workflow changes. If you don't have the ID on hand, you can get it from the database using the issue key as follows:select id from jiraissue where pkey = "JIRA-123"
Where
JIRA-123is the issue key. - Extract all the change groups created for the issue. Every set of changes made on an issue during a single operation (for example, edit, workflow transition, and so on) is grouped into a single
changegroupby JIRA. It is on thechangegrouprecord that JIRA stores the associatedissueidand thecreateddate (the date when the change was made):select id from changegroup where issueid = '10010'
Where
10010is theissueid, the ID we extracted in the previous step. While extracting the change groups, we can even mention the created date if you want to see only changes on a specific date! Use the author field to restrict this to changes made by a user. - Extract
statuschanges for the group/groups selected:select oldstring, newstring from changeitem where fieldtype = "jira" and field = "status" and groupid in ( 10000, 10010 )
Here, the groupids
10000,10010, and so on, are IDs extracted in the previous step. Here,oldstringis the original value on the issue andnewstringis the updated value. Includeoldvalueandnewvalue, if you want to get the status IDs as well.
You can write it in a single query, as shown next, or modify it to include more details. But hopefully this gives you a starting point:
select oldstring, newstring from changeitem where fieldtype = "jira" and field = "status" and groupid in ( select id from changegroup where issueid = '10010');
Another example of how to extract the details, along with the created date, is to use inner join as follows:
select ci.oldstring, ci.newstring, cg.created from changeitem ci inner join changegroup cg on ci.groupid = cg.id where ci.fieldtype = "jira" and ci.field = "status" and cg.issueid = '10010';
Over to you DBAs now!
As mentioned, the changes at any single operation on an issue are stored as a changegroup record in the JIRA database. The main three columns, issueid, author, and created, are all parts of this table.
The actual changes are stored in the changeitem table with its foreign key groupid pointing to the changegroup record.
In our case, we are looking specifically at the workflow statuses, and so, we query for records that have the fieldtype value of jira and field of status.
A sample output of the query (that uses an inner join) is as follows:
- The Retrieving workflow details from a table recipe in Chapter 10, Dealing with the JIRA Database