Purpose of this article

The purpose of this article is provide SQL queries to retrieve from the Database the updates made to a Jira issue.

Environment

• Jira Server/Data Center on any version from 8.0.0.
• These SQL queries have been tested only on PostgreSQL 9.3, PostgreSQL 12, and MySQL 5.7. They may require modifications to run for other database types and versions. 

Solution

Execute the following SQL queries to get the issue change history for one Jira Issue:

• To retrieve the change history for an issue (including status, transitions, change in values, etc):  

  • This query can be used to confirm that the dates and order of changes are the same in the source and target Jira instances. 

    All changes

    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
    inner join app_user au on cg.author = au.user_key
    WHERE cg.issueid=(select id from jiraissue where issuenum = <Issue Number> and project in (select id from project where pkey = '<Project key>')) 
    order by 1,3,4;

• To get only STATUS changes:

  Status changes

  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 = <Issue Number> and project in (select id from project where pkey = '<Project key>')) 
  order by 1,3,4;

• To get one Custom Field changes:

  Custom field value changes

  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='custom' AND ci.FIELD='<Custom Field Name>'
  inner join app_user au on cg.author = au.user_key
  WHERE cg.issueid=(select id from jiraissue where issuenum = <Issue Number> and project in (select id from project where pkey = '<Project key>')) 
  order by 1,3,4;

• To get the assignee changes for a specific issue:

  Assignee changes for a specific issue

  select i.field,i.oldstring,i.newstring,a.lower_user_name,g.created from 
  changeitem i join changegroup g on g.id = i.groupid
  join jiraissue j on j.id = g.issueid
  join project p on p.id = j.project
  join app_user a on a.user_key = g.author
  where i.field = 'assignee' and j.issuenum = <Issue Number> and p.pkey = '<Project key>';

In the above queries

• replace <Issue Number>  with Issue's number
• replace <Project Key> with Issue's project key
• replace <Custom Field Name> with the custom field name

Examples

Using Jira Issue KBAN-18

• <Issue Number> = 18
• <Project Key>     = KBAN

Query all changes

Query Status changes

Query custom field 'Compass Bearing' changes

Query assignee changes

Using Jira Issue KBAN-17

• <Issue Number> = 17
• <Project Key>     = KBAN