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:

authors

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.

Advertisements

5 thoughts on “User Activity Statistics

  1. You can also use the “Participants of an Issue” field that comes with the JIRA Toolkit addon, and filter issues on the “updated in the last 30 days”. Displaying the issue participants might be even better than just comments.

  2. How to show a list of issues that a user touched (user activities) on a particular day? Using JIRA 3.13. Filter on participants & update fields didn’t give me the result I am looking for. Thanks.

    • You can just make a simple search filter to do that:
      Reporter: johnny
      Updated from 2010/05/10 to 2010/05/11

      Or, you can use Sawmill or similar log analysis software if you do not need to see the results within JIRA.

  3. But this is not possible in hosted versions, right? what to do in hosted versions to get theuser’s comments posted in a specific date range?

    • I would imagine that even with hosted you could ask for them (Contegix or Atlassian) to provide you with access to the database.

      Our setup is very very similar to hosted, just not technically ‘hosted’ in terms of licensing. And we are able to access our DB direclty with the phpmyadmin application via the browser. Contegix set this up for me.

Comments are closed.