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 as a Document Library


One of the most effective ways I am using JIRA is…as a document library!

Most readers will now be thinking – “eh? why don’t you use Confluence to manage your documents?”.  Well the answer is that my companies employees are not IT-friendly, and are therefore definitely not ready for a wiki.  And when making a new intranet site, I didn’t want to confuse people by having two systems.  So I decided to pick the most convenient system for our needs, which was JIRA, and try to somehow hack it to also act as a document library.  It was tough but I managed it.

This Document Library is easily the most popular feature on our JIRA, and it is definitely the ‘killer app’ that got everyone using the system.  All our employees need access to these documents, so by moving them all to JIRA we could force them all to use it and thus encourage them to use the various collaboration projects we have.

How it works

Here is a screenshot of our ‘Document Library’ dashboard page:


Here is a close up of one of the tables on the dashboard above:


As you can  see each table contains a many hyperlinks, a bit like Craigslist.   You can do this with the ‘Text Portlet’ (it must be turned on in Administration->Plugins) from JIRA 3.12+, pre 3.12 you can use the Improved HTML plugin.  Then you can use HTML to create the tables and hyperlinks. (By the way this feature is amazing and I use it for many other things, especially for showing iframes of custom-made PHP pages to support the intranet.  It may have security issues if you allow users to use it.).

When you click on one of the hyperlinks, for example “Manuals / English / A-G”, the following issue navigator page is shown:

doc-agThe cool thing here is there is a ‘Hyperlink’ to each document displayed right here, so you dont have to open each issue to download the attachment.  You can achieve this in one of two ways:

  1. Set up a customfield of ‘URL’ type, and just insert a direct hyperlink to the file (hosted on your JIRA or on an external website).
  2. Set up a customfield of ‘single line text’ type, and in the Field Configuration options set the Renderer to WIKI.  You can then use WIKI code to put a hyperlink to a file attached to the JIRA issue.  The WIKI code is like this: [^filename.pdf]. Genius or what?  An added bonus is that you can keep updating the file (filename.pdf) and so long as you do not change the filename you never have to update the WIKI customfield, because it will automatically link to the latest version of filename.pdf.

As you can see we also use Status field to indicate if the document is active or not.  We also keep an archive of old versions of the document inside the issue.  And of course the document can be discussed inside the issue, or linked to from other JIRA projects.

Notice also that we show ‘new/updated documents’ filter on the dashboard.  This is nice to show people that we are actively updating things, and for them to stumble upon documents they may be interested in.  Of course they could make search filters for updated documents in their language, and subscribe to the filter in order to get email notifications, however they are not IT-savvy enough to do it.  But the feature is there waiting for them once they get more used to JIRA.


So that is basically it.  Sorry it is not a step by step guide, but I think this should be enough to guide you to do a similar thing.  Of course, it takes  a long time to setup all the search filters that will be linked to by your HTML tables, but once done it is done forever.  Maybe someone knows a good way to quickly setup 100’s of similar filters, but I used a temp staff to do it.   If you setup the columns you want, then you can use ‘save as’ to carry those columns across to the next search filter.