User Activity SQL Query

Still need help?

The Atlassian Community is here for you.

Ask the community

Symptoms

Customers ask for a query to pull all the user activity from Jira for a specific user for a specific period of time. They need the exact information that displays on the Activity tab of a user's profile, without having to click the Show More... link dozens of times.

Diagnosis

In the User Profile you can retrieve this information but it takes too much time and sometimes you can't find all the information if it is too old.

Resolution

You can run the following query in your database:

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.

This query will list all the changes the user has made, the old value, the new value, and when it was made.

Last modified on Jan 24, 2024

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.