We have talked about the Servlet extraction of worklogs:
We have talked also about the Tempo API REST worklog extraction using groovy:
- How to list your Jira Tempo Teams and members in a Confluence page
- Jira Groovy example script to get Tempo Teams Worklogs using REST API
- How to get the required team hours from JIRA Tempo plugin with groovy
- Jira Groovy Postfunction example to set Tempo Team and Tempo Account fields.
- Groovy REST WS call example to TEMPO JIRA plugin
Now its time to know how to extract the Jira worklogs using SQL queries, here the query:
select w.ID as "Worklog Id", w.issueid as "Issue Id", concat(p.pkey,"-",i.issuenum) as "Issue Key", i.summary as "Issue Summary", round(w.timeworked/3600,2) as "Hours", w.startdate as "Work Date", u.user_name as "User Name", u.display_name as "User Full Name", p.pkey as "Project Key", p.pname as "Project Name", ty.pname as "Issue Type Name" from worklog w inner join jiraissue i on i.id=w.issueid inner join cwd_user u on u.user_name=w.author inner join project p on p.id = i.PROJECT inner join issuetype ty on ty.id = i.issuetype where w.startdate >= '2019-01-01' and w.startdate <= '2019-02-01';
Use this prefix to extract the result in a CSV file
SELECT … FROM … WHERE … INTO OUTFILE ‘file.csv’ FIELDS TERMINATED BY ‘,’
by MrAddon
Query updated again:
New definitive query with track of username name changes:
(Atlassian official info: https://community.atlassian.com/t5/Jira-questions/What-about-the-data-in-app-user-table/qaq-p/787427 )
New query (with join with table app_user):
Also we have added support to “GROUP BY 1” in order to do not duplicate the worklog line for the directory reason/problem. This sentence avoid/fixes the problem of the duplicate rows caused by directory_id username duplicated.
Regards!
LikeLiked by 1 person