Verify issues with more than one sprint change 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
In Jira, we can use the Jira Query Language (JQL) to search for issue changes using the WAS or CHANGED operators. However, some fields, such as sprint, don't support those operators. Also, we can't search the number of changes for each field in each issue, and that could be particularly useful for sprints.
This article will demonstrate how to collect this information from the database.
The information on this page relates to accessing the database. Consequently, Atlassian Support can't guarantee support for the steps described on this page, as we don't recommend direct access to the database. Please be aware that this material is provided for your information only and that you use it at your own risk.
Environment
Jira Data Center
Solution
We can use a database query to find this information. This example was tested in PostgreSQL. It was made to get issues updated after Jan 1st 2025, limited to the "MYPROJ" project and the "Sprint" field:
WITH temp as (SELECT i.id,i.issuenum,p.pkey,COUNT(*) as count
FROM jiraissue i
JOIN project p ON i.project=p.id
JOIN changegroup cg ON cg.issueid=i.id
JOIN changeitem ci ON ci.groupid=cg.id
WHERE i.updated > '2025-01-01'
AND p.pkey = 'MYPROJ'
AND ci.field = 'Sprint'
GROUP BY i.id,i.issuenum,p.pkey)
SELECT temp.pkey,
temp.issuenum,
chg.created as "date",
chi.oldstring,
chi.newstring
FROM temp
JOIN changegroup chg ON chg.issueid=temp.id
JOIN changeitem chi ON chi.groupid=chg.id
WHERE chi.field = 'Sprint'
AND temp.count > 1
ORDER BY temp.pkey, temp.issuenum, chg.createdSee also How to retrieve issue change history from database in Jira
Was this helpful?