Thursday, June 19, 2014

How difficult is to report JIRA Worklog?

How difficult is to report JIRA Worklog? There are several plugins and a couple of API call for free but none of them so far can report on a basic metric: How many hours each member of the team worked per ticket in a particular date or date range.

I do not like to go to the database directly but rather I prefer API endpoints, however while I wait for a free solution to this problem I guess the most effective way to pull such information is unfortunately to query the jira database directly.

Below is an example to get the hours per team member and ticket for yesterday. You could tweak this example to get all kind of information about worklog.
# edit through crontab -e
JIRA_PWD=jiraPassword
JIRA_TO1=teammail1@sample.com
JIRA_TO2=teammail2@sample.com
JIRA_FROM=jira@sample.com
MAIL_SERVER=mail.sample.com:25
30 08 * * * mysql -vvv -u jira -p$JIRA_PWD jira -e 'select concat(c.pkey, "-", b.issuenum) as issuekey, AUTHOR, issueid, a.STARTDATE, timeworked / 3600 as yesterday_hours, timespent / 3600 as total_hours from worklog a inner join jiraissue b on a.issueid=b.ID inner join project c on b.PROJECT=c.id where date(a.STARTDATE) = date_sub(CURDATE(), interval 1 day) order by AUTHOR, c.pkey, b.issuenum, a.STARTDATE;' | sendEmail -f $JIRA_FROM -t $JIRA_TO1 $JIRA_TO2 -s $MAIL_SERVER -u "JIRA worklog for yesterday" > /dev/null
If you want to include custom fields like 'Team' see below:
# The below query allows to extract worklog from the previous day in JIRA
# It returns the the JIRA identifier, the author, a custom field called Team,
# the start date, the hours worked yesterday
# and the total hours being worked so far
SELECT Concat(p.pkey, "-", ji.issuenum) AS issuekey,
author,
cfo.customvalue AS Team,
wl.startdate,
timeworked / 3600 AS yesterday_hours,
timespent / 3600 AS total_hours
FROM worklog wl
INNER JOIN jiraissue ji
ON wl.issueid = ji.id
INNER JOIN project p
ON ji.project = p.id
INNER JOIN customfieldvalue cfv
ON cfv.issue = wl.issueid
INNER JOIN customfield cf
ON cf.id = cfv.customfield
INNER JOIN customfieldoption cfo
ON cfo.customfield = cf.id
AND cfo.id = cfv.stringvalue
WHERE cf.cfname = "Team"
AND Date(wl.startdate) = Date_sub(Curdate(), INTERVAL 1 day)
ORDER BY author,
p.pkey,
ji.issuenum,
wl.startdate;

No comments:

Followers