Retrieve issue change history from database in Jira server

お困りですか?

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

コミュニティに質問

Platform Notice: Server, Data Center, and Cloud By Request - This article was written for the Atlassian server and data center platforms but may also be useful for Atlassian Cloud customers. If completing instructions in this article would help you, please contact Atlassian Support and mention it.

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 information on this page relates to accessing the Jira database. Consequently, Atlassian Support cannot guarantee the provision of any support for the steps described on this page, as we do not recommend direct access into the database. Please be aware that this material is provided for your information only and that you use it at your own risk.


When exporting/importing XML Backup files or migrating from Cloud to Server, we might want to make sure that all the data was imported correctly into the database. To do so, execute the following SQL queries:

  • 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. 
すべての変更
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 = <115> and project in (select id from project where pname = <'Project name'>)) 
order by 1,3,4;


  • ステータスの変更のみを取得する方法
ステータスの変更
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 = <115> and project in (select id from project where pname = <'Project name'>)) 
order by 1,3,4;


  • To get only Custom Fields changes and their 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='Name of Custom Field'
inner join app_user au on cg.author = au.user_key
WHERE cg.issueid=(select id from jiraissue where issuenum = <115> and project in (select id from project where pname = <'Project name'>)) 
order by 1,3,4;


環境


  • This has been tested only in PostgreSQL 9.3 and MySQL 5.7. The queries may require modifications to run for other database types. 



最終更新日 2023 年 11 月 27 日

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

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