目的

The purpose of this article is to show how to retrieve the historical information of Sprints for a given JIRA issue. 

ソリューション

• Sprint custom field value on an Issue view screen will tell you all the Sprints the issue has been added into. The example below shows that issue MOK-3 has been added into Scrum Sprint 1, Scrum Sprint 2, Scrum Sprint 2, Scrum Sprint 3. (Scrum Sprint 2 exist twice because they are different Sprints - you will need to check in the database to get the ID of the Sprint and they will be unique)

• You can click on the History tab of the issue and check for the value changes of Sprint custom field.

• Use this SQL; please replace <Project key> and <issue number> to the respective values:

  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='Sprint'
  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 pname = 'Project key'))
  order by 1,3,4;

  Example result (JIRA issue with key KANBAN-6):

   pname  |  pkey  | issuenum |   id    | issueid | lower_user_name | author |          created           | fieldtype | field  | oldvalue |   oldstring   | newvalue |   newstring
  --------+--------+----------+---------+---------+-----------------+--------+----------------------------+-----------+--------+----------+---------------+----------+---------------
   KANBAN | KANBAN |        6 | 1010111 |   10028 | admin           | admin  | 2022-05-03 12:07:59.876+00 | custom    | Sprint |          |               | 4        | test Sprint 2
   KANBAN | KANBAN |        6 | 1010113 |   10028 | admin           | admin  | 2022-05-03 12:10:20.806+00 | custom    | Sprint | 4        | test Sprint 2 | 3        | test Sprint 1

• You can use the below SQL query to get the history of all sprint's issues from a specific project (changing the PROJECT_KEY_HERE)

• You can also, uncomment the SPRINT_NAME_HERE line and change the SPRINT_NAME_HERE for the respective values on both parameters, if you need to search for a specific sprint:

  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='Sprint'
  inner join app_user au on cg.author = au.user_key
  WHERE cg.issueid in (select id from jiraissue where project in (select id from project where pkey = 'PROJECT_KEY_HERE')) 
  -- and ci.oldstring like ('%SPRINT_NAME_HERE%') or ci.newstring like ('%SPRINT_NAME_HERE%')
  order by 1,3,4;