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

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。

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.

*Fisheye および Crucible は除く


目的

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_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;


最終更新日 2023 年 8 月 15 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.