2007-06-01

Jira time tracking report

We use Jira religiously as our issue tracking tool. Whenever we work on an issue, we add comments and log the amount of time we spent. At the end of the week, we want to run a report that tells us who worked on what, what happened, and how much time was spent.

Jira doesn't provide this kind of report out of the box, and there aren't any plugins available that do it. Short of writing my own plugin, here's some SQL code to generate these kinds of reports.

First, what did I work on this week? Here's the query and its output, with work time reported in seconds:

mysql> select i.pkey, i.summary, a.created, a.actionnum
-> from jiraissue as i, jiraaction as a
-> where a.actiontype in ('comment', 'worklog')
-> and a.created >= '2007-05-28 00:00:00'
-> and a.created < '2007-06-03 24:00:00'
-> and a.author='richard'
-> and i.id = a.issueid
-> order by i.pkey;
+---------+------------------------------------------------------------------+---------------------+-----------+
| pkey | summary | created | actionnum |
+---------+------------------------------------------------------------------+---------------------+-----------+
| ABC-150 | where does the user go if they select "no" | 2007-05-29 15:47:31 | NULL |
| ABC-188 | search results page button/results | 2007-05-29 15:46:27 | NULL |
| ABC-284 | button follows form not aligned to bottom of screen as in mockup | 2007-05-29 15:44:37 | NULL |
| DEF-26 | SecurityException | 2007-05-30 15:54:03 | NULL |
| DEF-63 | Exit client | 2007-05-30 15:49:43 | NULL |
| DEF-64 | We need a link | 2007-05-30 15:52:50 | NULL |
| GHI-379 | user agent | 2007-05-30 11:05:51 | NULL |
| JKL-1 | Be a good boy. | 2007-05-31 15:15:44 | 3600 |
| JKL-3 | Try to have fun. | 2007-05-31 13:18:28 | 10800 |
| JKL-3 | Try to have fun. | 2007-05-31 13:18:42 | 900 |
| MNO-1 | Content Integration with Customer | 2007-05-30 16:05:12 | NULL |
| MNO-1 | Content Integration with Customer | 2007-05-30 16:08:04 | NULL |
+---------+------------------------------------------------------------------+---------------------+-----------+

Next, what did everyone work on this week? Here's the query and its output:

mysql> select a.author, i.pkey, i.summary, a.created, a.actionnum
-> from jiraissue as i, jiraaction as a
-> where a.actiontype in ('comment', 'worklog')
-> and a.created >= '2007-05-28 00:00:00'
-> and a.created < '2007-06-03 24:00:00'
-> and i.id = a.issueid
-> order by a.author, i.pkey;
+----------+---------+------------------------------------------------------------------+---------------------+-----------+
| author | pkey | summary | created | actionnum |
+----------+---------+------------------------------------------------------------------+---------------------+-----------+
| alice | ABC-322 | ant script has targets that seem to crash some of us | 2007-05-29 14:46:42 | NULL |
| alice | ABC-322 | ant script has targets that seem to crash some of us | 2007-05-29 14:48:14 | NULL |
| alice | ABC-336 | "buy" link | 2007-05-31 12:42:31 | NULL |
| bob | PQR-2 | Improve User Interface of the Log Viewer | 2007-05-29 20:07:02 | 10800 |
| bob | PQR-23 | Uninformative error message | 2007-05-29 17:36:55 | NULL |
| bob | STU-62 | Error when opening | 2007-05-28 19:07:58 | 18000 |
| carol | ABC-12 | Featured Items | 2007-05-29 11:09:28 | 39600 |
| carol | ABC-264 | missing message | 2007-05-29 11:08:47 | 3600 |
| carol | ABC-271 | breadcrumb | 2007-05-29 11:08:20 | 3600 |
| dan | ABC-284 | button follows form not aligned to bottom of screen as in mockup | 2007-05-29 16:14:53 | NULL |
| dan | DEF-10 | Port | 2007-05-30 11:30:24 | NULL |
| dan | DEF-26 | SecurityException | 2007-05-30 11:25:20 | NULL |
+----------+---------+------------------------------------------------------------------+---------------------+-----------+

Finally, how much time did people log this week? Here's the query and its output.

mysql> select author, sum(actionnum)
-> from jiraaction
-> where actiontype = 'worklog'
-> and created >= '2007-05-28 00:00:00'
-> and created < '2007-06-03 24:00:00'
-> group by author
-> order by author;
+----------+----------------+
| author | sum(actionnum) |
+----------+----------------+
| bob | 75600 |
| carol | 88800 |
| emil | 93600 |
| francine | 15300 |
| gary | 54000 |
+----------+----------------+

The next step is to package this as a plugin. Want to help? Let me know.

3 comments:

Sohail Somani said...

Hi Richard,

You might be interested in this post I made about timesheets with JIRA:

http://blog.worklogassistant.com/2008/12/jira-timesheet-options.html

I'd be very interested in your thoughts.

Richard said...

Sohail, thank for your comment. Your Worklog Assistant looks interesting.

This blog entry was more about auditing engineers to make sure they log their time. These days, I am more interested in work burn down.

Sohail Somani said...

Indeed making sure they log their time is important. With JIRA though, it is a HUGE pain. I honestly could not blame co-workers or reports when they "forgot" because it is too easy to forget.

I know there aren't many options in this space but with a time tracker that integrates with JIRA, it becomes easier to remember. Then with the Timesheet plugin, it is easy to ensure that they are logging their time.

If I understand correctly, burndown charts are good if people continually reestimate AND track time properly.

Those engineers, it's like herding cats ;-)

LinkWithin

Related Posts with Thumbnails