How to list 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.

    

Summary

Jira doesn't provide a report on moved issues. Each of the move operations must 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

Jira 7 or 8 & Jira Service Management 3 or 4.


Solution

The below query was written for Postgres. You'll have to edit it in case it doesn't run 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 before ";" on 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.oldstring like ('TARGETKEY-%')

Last modified on Sep 26, 2022

Was this helpful?

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