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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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:
Post a Comment