SQL Number of JIRA issues per user

Question

I am working to help identify who is using my JIRA application (server 7.13.x) and curious if someone could share a SQL query to display number of JIRA issues per user.

Would be nice to include the name, email and number of issues within the last X months (if possible)

TIA

Lady Di · Answer

Hello, Mike. I can offer you a ready-made report that works as a rest service on the free plugin mygroovy. Your users will at any time be able to access such a report via a link in Jira. The configuration is as simple as possible; just insert a piece of code by changing a few variables: filter, userName, password

If you need the ability to limit the report to groups, change the variable groups to the desired

I got a lot of code, so I’ll put it in here: https://paste.ofcode.org/cyDfHPAmncFvDXxVPzZLNM

Pavel Junek · Answer

Hi Mike,

We detect user activity not by user / number of issues, but we verify count and date of last login to Jira. Not every user has to create in Jira issue, but can only read or comment, etc.

Here is our SQL:

DISTINCT u.lower_user_name, to_timestamp(CAST(last_login AS BIGINT)/1000) AS "Last Login",  login_countFROM   cwd_user u  JOIN cwd_membership m    ON u.id = m.child_id       AND u.directory_id = m.directory_id  JOIN licenserolesgroup lrg    ON Lower(m.parent_name) = Lower(lrg.group_id)  JOIN cwd_directory d    ON m.directory_id = d.id  LEFT JOIN (SELECT ca.user_id, ca.attribute_value as last_login              FROM cwd_user_attributes ca              WHERE attribute_name = 'login.lastLoginMillis')             AS a ON a.user_id = u.id  LEFT JOIN (SELECT ca.user_id, ca.attribute_value as login_count              FROM cwd_user_attributes ca              WHERE attribute_name = 'login.count')             AS b ON b.user_id = u.idWHERE  d.active = '1'       AND u.active = '1'       AND license_role_name = 'jira-core'       AND u.lower_user_name not in           (SELECT u.lower_user_name            FROM   cwd_user u              JOIN cwd_membership m                ON u.id = m.child_id                   AND u.directory_id = m.directory_id              JOIN licenserolesgroup lrg                ON Lower(m.parent_name) = Lower(lrg.group_id)              JOIN cwd_directory d                ON m.directory_id = d.id            WHERE  d.active = '1'                   AND u.active = '1'                   AND license_role_name in ('jira-software','jira-servicedesk'))ORDER BY "Last Login" DESC;SELECT

Here is SQL result:

lower_user_name    |       Last Login       | login_count

----------------------+------------------------+-------------

user1                | 2017-12-13 11:50:18+01 | 901

user2                | 2017-12-13 11:48:28+01 | 899

user3                | 2017-12-13 11:32:54+01 | 295

Pavel