How to Retrieve Sprint History of an issue/project from JIRA Database

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Except Fisheye and Crucible


Purpose

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

Solution


  • 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_HERE and ISSUE_NUM 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_NUM and project in (select id from project where pkey = 'PROJECT_KEY_HERE'))
    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;


Last modified on Aug 15, 2023

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.