How to track tickets moved from/into project ?

[Sergey Kryazhev]

I have a project and I need to track which tickets are moved out of it during the week for example.

There is no 'was in' operator for PROJECT field.

The same question is how to track tickets which currently have my project but had another one week ago.

I spend a lot of time looking for this functionality in JQL.

Thanks for any help.

[leyvaen.taylor]

A possible solution is to set the initial status of an Issue created in the first project from "Backlog" to something specific like "Projectx backlog" by amending the workflow.

Then you can use the JQL: Status was "Projectx backlog".

In that way, whereever the ticket gets moved to, you can still identify the first Project the ticket was in.

Another way of doing this could be to use the JQL: reporter in membersOf("group")

That would allow you to see all tickets raised by members of a particular group.

A third way could be to have an automation rule that monitors when an Issue is moved from one Project to another, have a custom field called "Original Issue Key" and optionally a condition that checks whether the field is empty. 

Then an action Edit field: Original Issue Key with the value: 

{{changelog.key.fromString}}

This copies the Issue key to that field and then you can search on it in JQL using a wildcard such as:

Original Issue Key[Short text]" ~ "KEY-*"

[Sergey Kryazhev]

I took a look into the DB , looks like it is possible to get this info using tricky, having many JOINs SQL. There are 3 tables containing necessary data:  changegroup, changeitem and jiraissue.

Still would like to avoid direct access to DB (thanks for comment Nic)

Does anyone had experience with this plugin https://marketplace.atlassian.com/plugins/com.j-tricks.jql-plugin ?

movedIssues() and recentProjects() functions.

[Sergey Kryazhev]

Thanks, I see that it is not possible to do it using JQL. Does someone had a chance to solve this problem using direct access to MySQL ? Since "change project moment" is in the history , it should be possible to extract it .

[Nic Brough -Adaptavist-]

I'd avoid reading the database - there's good reasons it's abstracted. But if you happened to read the tables changeitem (for the record "key") and changegroup (for issue id, author, and date/time) you'll find what you need.

[Timothy]

You might not find it in the JQL. I know that it should be possible at least as JIRA still knows how to send you to the new issue key if you visit the old issue. Store in the DB perhaps? Change history tables?

[Nic Brough -Adaptavist-]

It's definitely in the history, you can see the key change from ABC-123 to XYZ-456, but no, there's no "was" function for project unfortunately.