Search for all Jira issue updates made by a user in Jira Data Center database
Platform Notice: Data Center Only - This article only applies to Atlassian apps 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
Symptoms
The purpose of this article is so provide a SQL query allowing to retrieve all the updates made to Jira issues by a specific user.
ℹ️ Please note that the SQL queries provided in this article do not cover all the user activity that can be found in the Activity tab of a user profile, but only the Jira issue updates made by this user. You can refer to the KB articles below, if you are trying to query the database to search for a different type of user activity:
How to find all the comments related to one user in the database
How to obtain user information for the worklog database table on Jira
Environment
Jira Server/Data Center on any version from 8.0.0
Solution
You can run the following query in your database to fetch Jira issue updates made by a specific user for a given period of time. This query will list all the changes the user has made, the old value, the new value, and when it was made.
SELECT p.pkey,i.issuenum,CI.*,CG.*,AP.*
FROM changeitem CI
JOIN changegroup CG ON CI.groupid = CG.id
JOIN app_user AP ON CG.author = AP.user_key
JOIN jiraissue i ON CG.issueid=i.id
JOIN project p ON i.project=p.id
WHERE AP.lower_user_name = 'xxx'
AND CG.created BETWEEN '2013-07-01 00:00:00' AND '2022-07-31 00:00:00'ℹ️ You need to change the author from xxx to the lower username you want to search and adjust the period of time.
Was this helpful?