CODEWITHSUNDEEP
jirajql
Justin C.
Justin C.

Reputation: 109

JIRA Query to track any and all activity between certain hours

A contractor I'm working with wants to give us a small bonus based on any "overtime" work we may or may not have done. Problem is, we didn't exactly keep a log of any activity we did outside of normal working day hours. I'm trying to use my Jira activity as a starting point. How can I track any and all activity (leaving a comment, changing a status, assigning the ticket to someone else, etc) conducted by me within certain hours, within certain dates.

For example, any and all activity conducted by me between the hours of 7PM - 8AM from February 3, 2021 to January 15, 2022.

Upvotes: 1

Views: 1026

Answers (1)

stuck
stuck

Reputation: 1560

You can get it from the Jira database.

For any activity, you can use following SQL query for retrieve any change in a project:

SELECT p.pname, p.pkey, i.issuenum, cg.ID, cg.issueid, au.lower_user_name, cg.AUTHOR, cg.CREATED, ci.FIELDTYPE, ci.FIELD, ci.OLDVALUE, ci.OLDSTRING, ci.NEWVALUE, ci.NEWSTRING
FROM changegroup cg
inner join jiraissue i on cg.issueid = i.id
inner join project p on i.project = p.id
inner join changeitem ci on ci.groupid = cg.id
inner join app_user au on cg.author = au.user_key
WHERE p.pkey = '<PROJECT_KEY>'
order by 1,3,4;

You can modify that query by filtering cg.CREATED in order to get activity between certain dates.

NOTE: Query can be changed depend on your database. NOTE2: You can look that Atlassian link for further information.

Upvotes: 1

Related Questions