How to list the issues moved from one project to another in Jira Data Center
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
Jira doesn't provide a report on moved issues. Each of the move operations can only be manually verified on each issue's History tab.
If we require a list of all moved issues, we can query the database for that.
Environment
All Jira versions.
Solution
The below query was written for Postgres. Please modify it in case it doesn't work on your specific database.
1
2
3
4
5
6
7
8
9
10
select k.oldstring as "Old Key", coalesce(t.oldstring,it.pname) as "Old Type", k.newstring as "New Key", coalesce(t.newstring,it.pname) as "New Type", u.lower_user_name as "Username", kg.created as "Moved date"
from changeitem k
join changegroup kg on kg.id = k.groupid
join app_user a on a.user_key = kg.author
join cwd_user u on u.lower_user_name = a.lower_user_name
join changegroup gt on gt.id = k.groupid
left join changeitem t on t.groupid = gt.id and t.field = 'issuetype'
join jiraissue i on i.id = kg.issueid
join issuetype it on it.id = i.issuetype
where k.field = 'Key';The output should be similar to:
1
2
3
4
5
Old Key | Old Type | New Key | New Type | Username | Moved date
-----------+----------+-----------+----------+----------+----------------------------
DEV-6 | Task | SITEDV-13 | Task | admin | 2020-09-08 17:37:20.973-03
TEAMXS-12 | Bug | UX-52 | Task | admin | 2020-09-14 13:00:53.724-03
(2 rows)Username being the user who performed the move action on that given date.
If you'd like, you can add a condition to limit the results based on time by adding the following extra condition at the end of the original query:
1
and kg.created between '2021-02-07' AND '2023-02-15'To track source or target project, you could use these conditions:
1
2
3
4
-- Uncomment line below to filter based on source project key
-- and k.oldstring like ('SOURCEPKEY-%')
-- Uncomment line below to filter based on target project key
-- and k.newstring like ('TARGETPKEY-%')Was this helpful?