How to Retrieve Sprint History of an issue/project from JIRA Database
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
Summary
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-3has 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:
1 2 3 4 5 6 7 8SELECT 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):
1 2 3 4pname | 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 1You 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:
1 2 3 4 5 6 7 8 9SELECT 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;
Was this helpful?