How to find by whom and when was an issue transitioned from one status to another in Jira using SQL queries
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
You may need to pull some information about who/when transitioned an issue from one status to another for reporting purposes.
To retrieve this information from Jira UI, you can use the JQL "status changed FROM 'First' TO 'Second'"to filter tickets which were moved from 'First' to 'Second' status. Then go through the history of the tickets manually to see the user who performed the action.
Since the operation above is time consuming, this article offers an alternative to get the data directly from the database using SQL queries.
Environment
Any Jira version.
Solution
You can retrieve data from the Database using an SQL query as the one below. The sample query is written for the issue ABC-123 and for the transition from First to Second status. Please replace the statuses, issue number and project key with your relevant values.
1
2
3
4
5
6
7
8
select i.field,i.oldstring,i.newstring,a.lower_user_name,g.created from
changeitem i join changegroup g on g.id = i.groupid
join jiraissue j on j.id = g.issueid
join project p on p.id = j.project
join app_user a on a.user_key = g.author
where i.field = 'status' and
i.oldstring = 'First' and i.newstring = 'Second'
and j.issuenum = 123 and p.pkey = 'ABC';Was this helpful?