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 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

Posted by:.

One thought on “Tempo Timesheets for Jira Software SQL worklogs extraction query”

  1. 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):

    select
w.ID as "Worklog Id",
w.issueid as "Issue Id",
concat(p.pkey,"-",i.issuenum) as "Issue Key",
i.summary as "Issue Summary",
format(w.timeworked/3600,2,'es_ES') as "Hours",
date_format(w.startdate, '%d-%m-%Y') as "Work Date",
date_format(w.startdate, '%M') as "Month",
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",
u.directory_id as "Directory",
date_format(w.updated, '%d-%m-%Y') as "updated"
from worklog w
inner join jiraissue i on
i.id=w.issueid
inner join app_user app on
app.user_key = w.author
inner join cwd_user u on
u.user_name= app.lower_user_name
inner join project p on
p.id = i.PROJECT
inner join issuetype ty on
ty.id = i.issuetype
where w.startdate >= '2019-04-29' AND w.startdate <= '2019-09-30'
and u.user_name = 'raul.pelaez'
group by w.ID
order by w.ID;

    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!

    Liked by 1 person

    Reply

Leave a Reply

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

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com 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 )

Cancel

Connecting to %s