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.

Synchronising JIRA custom field values with legacy systems

About 6 months ago, I was moving a legacy ticketing system to JIRA.  In addition to importing the old data, I also needed to synchronise not the JIRA issue data, but the actual options displayed in the drop down lists on the Create Issue screen.

I was planning to write my own plugin to do this, but just before I began I thought ‘lets have one more look in the user created JIRA plugin library first’.  By some amazing luck, a new plugin had been added in the last few months that did exactly what I required: Wim Deblauwe’s Database Values plugin.

searching-exampleThis plugin allows you to connect to an external database, fetch some values, and use them to populate the options in your custom fields.

We are using it in several ways, the most useful of which is to display our product model range in our JIRA.  When a user reports a problem with a product, then can select the product from a huge cascading drop down list (a 2 level drop down list containing Product Category/Model).  This prevents any ambiguity and reduces chance of user error.  It also allows us to standardise searches for specific models across multiple JIRA projects.

The list of models comes from a legacy system.  The added bonus of this is that when we later export the JIRA issue data to that legacy system, the drop down list values are the same so the data can be exported as is without any filter program in between.  The plugin retains not only the values (e.g. “Product XYZ”) but also any key associated with it (e.g. “018”).

I presume it would also be possible to connect to an MS Outlook address book in order to display customer names. This is something I may need to set up in future as we start to use JIRA for more CRM related activity.

You can also sort the values however you wish using an SQL query.  Another cool feature is that it can display the values using the JIRA AJAX textbox.  See Database Values for further information on this great plugin.

Monitoring JIRA for suspicious user activity

My company is in a very specialised industry with very few big players and very few new developments.  As as result, any activity to make new products/services is extremely valuable information.  My company is therefore understandably very concerned about data security.

In the past, confidential documents have found their way out of the company and into the hands of competitors, enabling them to use our new product ideas and try to get a similar product to market, or find some other way to steal our thunder when it is released.   Other times, sensitive customer information has been taken and distributed to the customer in an attempt to show that we do not properly protect sensitive customer information and therefore cannot be trusted/are incompetent etc.

So we needed a way to track who has accessed certain documents and discussions on JIRA, so that if such an incident occurs again, we can have some way to find out who did it, or at least narrow the options.

The things we wanted to achieve were:

  • be able to see who accessed Document-A between date X and Y.
  • have the system send an alarm email when a user downloads >50 binary files in 30 mins.

jira-user-filterThe solution we came up with was to use Sawmill which is a log analysis tool.  You install it on the web server and it parses log files regularly, updates it own database, and presents the information to you via a web interface.  You can set up various filters for the report information, and also set up email alarms such as above.  It was quite cheap and works well for our needs.   Probably there are other solutions, but this was the best I could find at the time.

I tried to use Sawmill for some other log analysis such as for my website, and for the SMTP mail server but it didnt work particularly well and I gave up on it without trying too much.  I use Google Analytics anyway for the website which is great, and I just look through the mail logs manually for now.