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:
To 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.
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.