We have talked about the Servlet extraction of worklogs:

We have talked also about the Tempo API REST worklog extraction using groovy:

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
customfieldvalue c on c.id=i.id
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 >= '2018-10-01' and w.startdate <= '2019-02-28';

Use this prefix to extract the result in a CSV file

SELECT FROM WHERE INTO OUTFILE ‘file.csv’ FIELDS TERMINATED BY ‘,’

by MrAddon

Advertisements
Posted by:.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s