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.


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/>