How to list the issues moved from one project to another in Jira

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

    

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.

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:

  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:

and kg.created between '2021-02-07' AND '2023-02-15'

To track source or target project, you could use these conditions:

-- 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-%')

Last modified on Jan 10, 2023

Was this helpful?

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