User Activity Statistics

The Challenge

As a JIRA Administrator, probably from time to time you have to report to your superiors about usage activity.  Is the system being used?  How often?  Who are the heavy users?  Who needs a kick up the backside to use the system?

So we need a way to show a list of users, along with the number of issues they have created and commented.

It is possible to use a built in JIRA portlet to show the number of issues created by each user:

updatedTo do this, simply create a search filter showing issues created in the last 30 days, then add a new portlet to your dashboard of type ‘filter statistics’ and tell it to show this search filter along with the Reporter of each issue.

However this is not so useful because, such as in a helpdesk scenario, some users never create issues but always answer them.  So we also need some way to show the number of comments created by each user.  Unfortunately however  JIRA doesn’t offer anything out of the box.

The Solution

I asked Atlassian Support about this, and as usual they got back to me pretty quickly (within 90 minutes) with a suggestion to query the database directly.  Using the sample code they supplied, I was able to show a list of all usernames along with the number of times they had created a comment:


MySQL Code required

To show all users and number of comments:

SELECT author, count(author) as comments FROM jiraaction j group by author ORDER BY author ASC;

To show all users and number of comments this month:

SELECT author, count(author) as comments FROM jiraaction j WHERE UPDATED > "2009-01-01 00:00:00" group by author ORDER BY author ASC;

Obviously you can adjust the ORDER BY to sort it by highest number of comments rather than author name etc.


Showing custom fields in ‘sub-task’ columns

We are using a lot of custom fields on our JIRA and sometimes we need to show the values of subtask customfields on our issue screens.

Something like this:


Updating the regular issue screen

In order to do this, you have to update atlassian-jira/WEB-INF/classes/ in order to get the columns to display.

Simply add the customfield IDs to the list in the jira.table.cols.subtasks line:

jira.table.cols.subtasks = customfield_10270, customfield_10630, summary, …

You will also want to set the context of those custom fields so they only display for certain Projects and Issue Types.  This will prevent the column being shown for unrealted projects/issue types.

The right hand red box in the image above shows some neat ‘actions’ that are quick links to the workflow actions of the subtask.  This is not shipped with JIRA, you have to install a plugin to do it.  See Available Workflow Actions JIRA plugin.

Updating the Printable view

Also, you may wish to update the ‘printable view’ too.  To do that, you must update the atlassian-jira/WEB-INF/classes/templates/plugins/issueviews/single-word.vm Velocity macro template.  There are some instructions how to do it here, but they apply to issues not subtasks.

Look for this code and do the updates shown in red:

## List Sub Tasks
#set ($subTasks = $issue.subTaskObjects)
#if ($subtasksEnabled && $subTasks.empty == false)
<tr><td bgcolor=”#f0f0f0″ width=”20%” valign=”top”><b>$i18n.getText(‘issue.field.subtasks’):</b></td>
<td bgcolor=”#ffffff” valign=”top”>
<table class=”grid” cellpadding=”0″ cellspacing=”0″ border=”0″ width=”100%”>
<tr bgcolor=”#f0f0f0″>

#set ($prnew=$customFieldManager.getCustomFieldObject(“customfield_10630”))
#if (($prnew.isInScope(null, $subTask.getProject(), [$subTask.getIssueType().getString(“id”)])) && ($prnew.getValue($subTask)))

#foreach ($subTask in $subTasks)
<a href=”${requestContext.baseUrl}/browse/$subTask.key”>$subTask.key</a>
<td valign=”top” width=”25%”>
<a href=”${requestContext.baseUrl}/browse/$subTask.key”>$stringUtils.abbreviate($subTask.summary, 40)</a>

#set ($prnew=$customFieldManager.getCustomFieldObject(“customfield_10630“))
#if (($prnew.isInScope(null, $subTask.getProject(), [$subTask.getIssueType().getString(“id”)])) && ($prnew.getValue($subTask)))

$textutils.htmlEncode($subTask.issueTypeObject.nameTranslation, false)
$textutils.htmlEncode($subTask.statusObject.nameTranslation, false)
#if ($fieldVisibility.isFieldHidden($issue.project.getLong(‘id’), ‘assignee’, $ == false)
#if ($subTask.assignee) $subTask.assignee.fullName #end &nbsp;
## end List Subtasks

Please comment if there are any problems or improvements…

Using JIRA with overseas offices

When you use JIRA to communicate with colleagues in other offices around the world, it is nice to have some kind of central focus page that people can visit to get latest announcements and connect with other offices.

I have made a kind of community dashboard page called “INFO” and published it to all users.  This page contains:

  • Customized World Time
  • Shared Interactive ‘global calendar’
  • Announcements
  • Key JIRA usage statistics

Customized World Time


This little homemade ‘widget’ shows Current time at each of our office locations, and highlights the text to green if office is now open, red if it is closed.

I built this using some very simple PHP and it is hosted in a ‘secret place’ on the web server (it doesnt have any authentication or anything, but the webpage is blocked to search engines and has a weird URL).  I then used the Improved HTML plugin for JIRA, and inserted a little iframe to load the secret webpage.

See my related post about overcoming the JIRA timezone problem.

Continue reading

Overcoming the JIRA timezone problem

Update Sept 30 2011: JIRA 4.4 has timezone support.  The post below refers to previous versions of JIRA.


No doubt you are aware already that JIRA doesn’t handle timezones well.  It simply shows the server timezone on all issues, instead of letting users select their own timezone.  Our server uses EDT so it means that us in Japan, and our users in Europe etc, all have to do some maths to work out what time a comment was posted.  Its actually quite a pain.

This issue has been heavily requested to Atlassian to fix, but I am not hopeful it will ever be done.  It is issue number 9 (JRA-9) in their feature tracking system, meaning it was requested very early in JIRAs lifetime, and the issue has been open since Feb 2002 with 187 votes, 87 watchers. They keep promising to address it but I can only imagine that their code is too tied up in spaghetti to fix it.  If all dates were recorded consistently as timestamps, surely it would be very simple indeed to just allow the user to set a timezone, and then apply that timezone to the timestamp before outputting a date.  So I am assuming from the age of this issue that dates have not been handled well making this a huge bug to fix.  (I think it is fair to call it a bug when they market the product as enterprise class.).


So what you can do about this?  The idea I came up with is weak, but better than nothing.  I have updated the footer template used on every page in JIRA to show the ‘current server time’:


This then allows the user to compare the issue comment date to the server time, then compare the server time to his local time, to get an idea of how old the comment is.


  • comment date: yesterday at 4PM EDT
  • current server time: 5AM EDT (therefore comment is 13 hours old)
  • current user local time: 6PM JST (therefore comment was added at 5AM today, local time)


You need to update $JIRAHOMEincludesdecoratorsfooter.jsp

<div class="footer">
Current Server Time: <%= new java.util.Date() %> <br/>

Localisation / translation workflow management


I manage my company’s JIRA intranet, but at the same time I also develop our public facing “Global” (ie. non-Japanese) website.

At the moment the global website runs in 5 languages: English, French, German, Chinese and Korean, with all the content coming originally from our seperate Japanese site.  So we have a multilingual site and we have need to manage translation of all the contents. It cannot be managed manually for various reasons:

  • Volume is too high – 300 pages in 5 languages is 1500 translation sets.
  • Version management – Sometimes we have to restart translation because we find an error in the master.  We must then correct the master and resubmit it to all translators.
  • Time lag – Sometimes a translation gets stuck for months because of a sticky point in the text such as legal or cultural problem.

So I decided to set up a project on JIRA to manage the translations.

Statuses and Workflows

It basically works like this:

  1. Myself or a regional office requests a page to be translated from Japanese to their language
  2. Japan HQ approves/disapproves the request
  3. If approved, the Reporter assigns the issue to a suitable translator
  4. The translator submits it back to the reporter
  5. The reporter proof reads the translation then submits it to Japan HQ to create the webpage.
  6. I create the draft webpage (and Flash/images etc)
  7. Reporter proofs the draft and usually requests changes
  8. Repeat above two steps until satisified
  9. The page is then published live.
  10. We then enter ‘localization’ stage where subtasks are created automatically for the other languages.  They are auto-assigned to each regions default translator, then cancelled, re-prioritized or completed depending on each regions needs.

This translates into the following workflow diagram:


It is important to note that this workflow only manages the translation of a Japanese page to one other language.  It does not track the status of the other languages – that is done by Sub-tasks of this parent issue.

Dashboards and Search Filters

To track the progress we require two search filters:

  1. Parent Issue statuses (Japanese -> Lang1)
  2. Subtask issue statuses (Lang1 -> Chinese/German/French/etc)

This is the Parent Issue filter:


It is a standard filter but it has some nice extra columns that really help us:

  • Published field is a multi-checkbox field.  As we publish a page live on the website, we check the box for that language.  So we can see quickly that the first issue in the filter above is published live in English and French.
  • Global URL is a ‘URL’ type custom field that contains a direct link to the published English page on the website.  I dont bother to add the links for other langs on JIRA because the URL is almost identical for each lang.
  • Sub-tasks column lets me jump directly to the status of a specific language translation, e.g. German, for that webpage.  I think I had to add a plugin to show this column.

Here is the second filter showing the status of all subtasks:


To get the summary of the parent issue to display above, I used a the parent issue summary plugin from the JIRA plugin library.

Inside an Issue

I only need to attach the master translation file (we use excel files) to the parent issue.  The assignees of the subtasks know to go back up to the parent to get the master.  This makes it easy to update the master later if needed.

I mentioned that when the status of the parent issue moves to ‘Localization’, it automatically creates subtasks for each language. To do this, I used the create sub-task on transition plugin.  This is the result:


The subtask get assigned automatically (this is set up using the plugin), so there is not much more to do.  I just click ‘pass to translator’ and then the assignees get informed by email notification and later provide me the translation.  Sometimes I hold off on passing them the work, if I know they already have a big workload or that region has put all translation work on hold for a few months etc – in that case I just leave it as Open and it will not appear on their dashboards.

By the way these subtasks have their own workflow, though it is pretty similar to the workflow of the parent.


Well thats all I can think of right now.  I probably missed out a lot of other stuff here, please leave a comment if you have any questions about this and I will try to expand.