Detecting a ticket that has moved between projects

[wvacchiani]

Hi,

I have access to the Jira API, so I am wondering what there is, behind the scenes, in the Jira database that would hold information about the movement of tickets from one project to another.  I'm hoping there's some sort of history table this sort of activity is stored on.

Jira Cloud is the product I am using. 

I've asked this question before if there was a way via JQL to detect when a ticket has moved from one project to another.  JQL doesn't support this.

We'd like to be able to report on this.

Any help would be appreciated.

Regards

Ward

[Evgenii · Accepted solution]

Hi, @wvacchiani 

You can use the REST API to search for issues where the issue key was changed.
This change is recorded in the issue's changelog, where you can find both the old and new project keys.

Unfortunately, you won’t be able to query the database directly for this information, as Jira Cloud instances do not provide database access.

Let me know if you need help with the API request or how to retrieve the changelog data!

Best regards,
Evgeniy

[wvacchiani]

HI Evgeniy,

Is this the approach you're suggesting?

• Use the Get changelogs (GET) api call to retrieve the changelogs.
• Check each changelog entry to detect the situation I am looking for.
  • In this case I am looking for a "updated the key" history entry. 
  • I can see what the payload looks like in the Jira doco (link below) but would need to see an example of a key update to know what to look for specifically.  Thats something for me to work through.
• Assuming I can pick out the "Updated the Key" event, act on it as required.

There's a bit more logic to it for me to build out what I need but I think that's the basis of your suggestion.  

Jira API reference: The Jira Cloud platform REST API

Any thoughts?

Regards

Ward

[Evgenii]

Hi, @wvacchiani 

No need to use additional methods. You can use basic "Get Issue" method.

Url will look like https://your-company.atlassian.net/rest/api/3/issue/TEST-94?expand=changelog

You will see there such array:

"items": [
{
"field": "issuetype",
"fieldtype": "jira",
"fieldId": "issuetype",
"from": "10138",
"fromString": "Bug",
"to": "10012",
"toString": "Task"
},
{
"field": "project",
"fieldtype": "jira",
"fieldId": "project",
"from": "10065",
"fromString": "ACCESS 2.0",
"to": "10088",
"toString": "TEST"
},
{
"field": "Key",
"fieldtype": "jira",
"from": null,
"fromString": "ACCESS2-1494",
"to": null,
"toString": "TEST-94"
}
]

[Serhii Kibitkin_SAASJet_]

Hi @wvacchiani 
If you are open to using third-party apps, Issue History for Jira, developed by my team, can help with this case.

You can filter several projects with JQL and explore ticket changes in the project field. 

[Mohanraj Thangamuthu]

Hello, Good day. We don't have a direct JQL to find the moved issues. Work item history will have the old and new entry. An alter approach shown in https://community.atlassian.com/forums/Jira-questions/JQL-to-show-when-an-issue-moved-into-a-particular-status/qaq-p/1853332#:~:text=However%2C%20the%20data%20you%20need,Hope%20it%20helps.&text=More%20than%208%20types%20of,available%20for%20all%20status%20reports.

[wvacchiani]

HI Mohanraj,

Thanks.  

I have access to the Jira API now.

I am working with Jira Cloud.

I am wondering if there are table(s) within the Jira database schema that may record this detail - the movement of a ticket from one project to another.  That'd be easier than working through Issue History.

Regards

Ward

[Sumukh Shashidhar]

In Jira Cloud, when the ticket is being moved from one project to another:

Jira creates a new issue key.
The original issue key is being stored in the new issue;s history (as reference).
This is treated like a new issue.
The issue histroy (like CHANGELOG) doesnt store project changed as a field, so it cant be queried by JQL.