How to audit license usage and user activity in Jira
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
The steps outlined on this article are provided AS-IS. This means we've had reports of them working for some customers — under certain circumstances — yet are not officially supported, nor can we guarantee they'll work for your specific scenario.
You may follow through and validate them on your own non-prod environments prior to production or fall back to supported alternatives if they don't work out.
We also invite you to reach out to our Community for matters that fall beyond Atlassian's scope of support!
Summary
If licenses are close to exhaustion and upgrading to a higher tier isn't an option at the moment, Jira Admins might need to revoke licenses from some users to provide to others.
This article offers some tips on identifying users who are active but haven't been using Jira in a way that would require a license. Inactive users don't add up to license usage.
There are three types of licenses: Jira Core, Jira Software, and Jira Service Management, and their features are detailed (and compared) here: Jira applications and project types overview.
This feature was requested through: JRASERVER-71402 - As a Jira admin, I'd like to monitor privileged user activity.
Environment
Jira Core or Software 7, 8, and 9.
Jira Service Management 3, 4, and 5.
Solution
The queries presented in this article were written for the Postgres DB. You may need to rewrite portions of it to fit your particular database type.
Also, the queries are mostly examples and haven't been tuned for the best performance or execution plan.
Please note the instructions here are for helping the Jira Admin to make more assertive decisions on revoking licenses from users. No single method is a source of truth by itself and there are still caveats not covered by this article — which is updated as the gaps are addressed.
Jira Core (and Software) license usage
Execute this query on Jira's DB to retrieve the last access times on some features.
Please note, the last access times are by default, returned in epoch time format. In PostgreSQL, the function to_timestamp can be used to convert the numbers to a human-readable format. For other databases, please refer to the database's documentation/user manual on how to convert between epoch time format and regular, human-readable timestamp format.
For MySQL, for example, simply replace to_timestamp for from_unixtime.
select distinct u.directory_id, dir.directory_name, a.user_key, u.lower_user_name, u.display_name, sd.LICENSE_ROLE_NAME as SD, sw.LICENSE_ROLE_NAME as SW,
to_timestamp(CAST (ua.attribute_value AS numeric)/1000) as "Last Login",
to_timestamp(lastdash.lastviewed/1000) as "Viewed Dashboard",
to_timestamp(lastkanban.lastviewed/1000) as "Viewed Board",
to_timestamp(lastsearch.lastviewed/1000) as "Viewed Search",
to_timestamp(lastissue.lastviewed/1000) as "Viewed Issue",
i.created as "Created Issue"
from cwd_user u
join cwd_membership m on u.lower_user_name = m.lower_child_name
join cwd_group g on g.id = m.parent_id
join licenserolesgroup lic on lic.GROUP_ID = g.lower_group_name and lic.LICENSE_ROLE_NAME in ('jira-servicedesk', 'jira-software')
left join licenserolesgroup sd on sd.GROUP_ID = g.lower_group_name and sd.LICENSE_ROLE_NAME = 'jira-servicedesk'
left join licenserolesgroup sw on sw.GROUP_ID = g.lower_group_name and sw.LICENSE_ROLE_NAME = 'jira-software'
left join cwd_user_attributes ua on u.id = ua.user_id and ua.attribute_name = 'login.lastLoginMillis'
join app_user a on u.lower_user_name = a.lower_user_name
join cwd_directory dir on dir.id = u.directory_id
left join userhistoryitem lastdash on lastdash.id = (select id from userhistoryitem uhi where uhi.username = a.user_key and uhi.entitytype = 'Dashboard' order by uhi.lastviewed desc limit 1) and lastdash.username = a.user_key
left join userhistoryitem lastissue on lastissue.id = (select id from userhistoryitem uhi where uhi.username = a.user_key and uhi.entitytype = 'Issue' order by uhi.lastviewed desc limit 1) and lastissue.username = a.user_key
left join userhistoryitem lastkanban on lastkanban.id = (select id from userhistoryitem uhi where uhi.username = a.user_key and uhi.entitytype = 'RapidView' order by uhi.lastviewed desc limit 1) and lastkanban.username = a.user_key
left join userhistoryitem lastsearch on lastsearch.id = (select id from userhistoryitem uhi where uhi.username = a.user_key and uhi.entitytype = 'Searcher' order by uhi.lastviewed desc limit 1) and lastsearch.username = a.user_key
left join jiraissue i on i.id = (select id from jiraissue ji where ji.reporter = a.user_key order by created desc limit 1) and i.reporter = a.user_key
where u.active = 1 and dir.active = 1
order by 7 desc;select distinct u.directory_id, dir.directory_name, a.user_key, u.lower_user_name, u.display_name, sd.LICENSE_ROLE_NAME as SD, sw.LICENSE_ROLE_NAME as SW,
TO_CHAR(CAST(DATE '1970-01-01' + (1/24/60/60/1000) * ua.attribute_value AS TIMESTAMP), 'MM/DD/YYYY HH24:MI:SS') as "Last Login",
TO_CHAR(CAST(DATE '1970-01-01' + (1/24/60/60/1000) * lastdash.lastviewed AS TIMESTAMP), 'MM/DD/YYYY HH24:MI:SS') as "Viewed Dashboard",
TO_CHAR(CAST(DATE '1970-01-01' + (1/24/60/60/1000) * lastkanban.lastviewed AS TIMESTAMP), 'MM/DD/YYYY HH24:MI:SS') as "Viewed Board",
TO_CHAR(CAST(DATE '1970-01-01' + (1/24/60/60/1000) * lastsearch.lastviewed AS TIMESTAMP), 'MM/DD/YYYY HH24:MI:SS') as "Viewed Search",
TO_CHAR(CAST(DATE '1970-01-01' + (1/24/60/60/1000) * lastissue.lastviewed AS TIMESTAMP), 'MM/DD/YYYY HH24:MI:SS') as "Viewed Issue",
i.created as "Created Issue"
from cwd_user u
join cwd_membership m on u.lower_user_name = m.lower_child_name
join cwd_group g on g.id = m.parent_id
join licenserolesgroup lic on lic.GROUP_ID = g.lower_group_name and lic.LICENSE_ROLE_NAME in ('jira-servicedesk', 'jira-software')
left join licenserolesgroup sd on sd.GROUP_ID = g.lower_group_name and sd.LICENSE_ROLE_NAME = 'jira-servicedesk'
left join licenserolesgroup sw on sw.GROUP_ID = g.lower_group_name and sw.LICENSE_ROLE_NAME = 'jira-software'
left join cwd_user_attributes ua on u.id = ua.user_id and ua.attribute_name = 'login.lastLoginMillis'
join app_user a on u.lower_user_name = a.lower_user_name
join cwd_directory dir on dir.id = u.directory_id
left join userhistoryitem lastdash on lastdash.id = (select id from userhistoryitem uhi where uhi.username = a.user_key and uhi.entitytype = 'Dashboard' order by uhi.lastviewed desc FETCH FIRST 1 ROW ONLY) and lastdash.username = a.user_key
left join userhistoryitem lastissue on lastissue.id = (select id from userhistoryitem uhi where uhi.username = a.user_key and uhi.entitytype = 'Issue' order by uhi.lastviewed desc FETCH FIRST 1 ROW ONLY) and lastissue.username = a.user_key
left join userhistoryitem lastkanban on lastkanban.id = (select id from userhistoryitem uhi where uhi.username = a.user_key and uhi.entitytype = 'RapidView' order by uhi.lastviewed desc FETCH FIRST 1 ROW ONLY) and lastkanban.username = a.user_key
left join userhistoryitem lastsearch on lastsearch.id = (select id from userhistoryitem uhi where uhi.username = a.user_key and uhi.entitytype = 'Searcher' order by uhi.lastviewed desc FETCH FIRST 1 ROW ONLY) and lastsearch.username = a.user_key
left join jiraissue i on i.id = (select id from jiraissue ji where ji.reporter = a.user_key order by created desc FETCH FIRST 1 ROWS ONLY) and i.reporter = a.user_key
where u.active = 1 and dir.active = 1
order by 7 desc;select distinct u.directory_id, dir.directory_name, a.user_key, u.lower_user_name, u.display_name, sd.LICENSE_ROLE_NAME as SD, sw.LICENSE_ROLE_NAME as SW,
from_unixtime(floor(ua.attribute_value/1000)) as "Last Login",
from_unixtime(floor(lastdash.lastviewed/1000)) as "Viewed Dashboard",
from_unixtime(floor(lastkanban.lastviewed/1000)) as "Viewed Board",
from_unixtime(floor(lastsearch.lastviewed/1000)) as "Viewed Search",
from_unixtime(floor(lastissue.lastviewed/1000)) as "Viewed Issue",
i.created as "Created Issue"
from cwd_user u
join cwd_membership m on u.lower_user_name = m.lower_child_name
join cwd_group g on g.id = m.parent_id
join licenserolesgroup lic on lic.GROUP_ID = g.lower_group_name and lic.LICENSE_ROLE_NAME in ('jira-servicedesk', 'jira-software')
left join licenserolesgroup sd on sd.GROUP_ID = g.lower_group_name and sd.LICENSE_ROLE_NAME = 'jira-servicedesk'
left join licenserolesgroup sw on sw.GROUP_ID = g.lower_group_name and sw.LICENSE_ROLE_NAME = 'jira-software'
left join cwd_user_attributes ua on u.id = ua.user_id and ua.attribute_name = 'login.lastLoginMillis'
join app_user a on u.lower_user_name = a.lower_user_name
join cwd_directory dir on dir.id = u.directory_id
left join userhistoryitem lastdash on lastdash.id = (select id from userhistoryitem uhi where uhi.username = a.user_key and uhi.entitytype = 'Dashboard' order by uhi.lastviewed desc limit 1) and lastdash.username = a.user_key
left join userhistoryitem lastissue on lastissue.id = (select id from userhistoryitem uhi where uhi.username = a.user_key and uhi.entitytype = 'Issue' order by uhi.lastviewed desc limit 1) and lastissue.username = a.user_key
left join userhistoryitem lastkanban on lastkanban.id = (select id from userhistoryitem uhi where uhi.username = a.user_key and uhi.entitytype = 'RapidView' order by uhi.lastviewed desc limit 1) and lastkanban.username = a.user_key
left join userhistoryitem lastsearch on lastsearch.id = (select id from userhistoryitem uhi where uhi.username = a.user_key and uhi.entitytype = 'Searcher' order by uhi.lastviewed desc limit 1) and lastsearch.username = a.user_key
left join jiraissue i on i.id = (select id from jiraissue ji where ji.reporter = a.user_key order by created desc limit 1) and i.reporter = a.user_key
where u.active = 1 and dir.active = 1
order by 7 desc;select distinct u.directory_id, dir.directory_name, a.user_key, u.lower_user_name, u.display_name, sd.LICENSE_ROLE_NAME as SD, sw.LICENSE_ROLE_NAME as SW,
DATEADD(second, CAST (ua.attribute_value AS numeric)/1000,'1970/1/1') as "Last Login",
DATEADD(second, lastdash.lastviewed/1000,'1970/1/1') as "Viewed Dashboard",
DATEADD(second, lastkanban.lastviewed/1000,'1970/1/1') as "Viewed Board",
DATEADD(second, lastsearch.lastviewed/1000,'1970/1/1') as "Viewed Search",
DATEADD(second, lastissue.lastviewed/1000,'1970/1/1') as "Viewed Issue",
i.created as "Created Issue"
from cwd_user u
join cwd_membership m on u.lower_user_name = m.lower_child_name
join cwd_group g on g.id = m.parent_id
join licenserolesgroup lic on lic.GROUP_ID = g.lower_group_name and lic.LICENSE_ROLE_NAME in ('jira-servicedesk', 'jira-software')
left join licenserolesgroup sd on sd.GROUP_ID = g.lower_group_name and sd.LICENSE_ROLE_NAME = 'jira-servicedesk'
left join licenserolesgroup sw on sw.GROUP_ID = g.lower_group_name and sw.LICENSE_ROLE_NAME = 'jira-software'
left join cwd_user_attributes ua on u.id = ua.user_id and ua.attribute_name = 'login.lastLoginMillis'
join app_user a on u.lower_user_name = a.lower_user_name
join cwd_directory dir on dir.id = u.directory_id
left join userhistoryitem lastdash on lastdash.id = (select top 1 id from userhistoryitem uhi where uhi.username = a.user_key and uhi.entitytype = 'Dashboard' order by uhi.lastviewed desc) and lastdash.username = a.user_key
left join userhistoryitem lastissue on lastissue.id = (select top 1 id from userhistoryitem uhi where uhi.username = a.user_key and uhi.entitytype = 'Issue' order by uhi.lastviewed desc) and lastissue.username = a.user_key
left join userhistoryitem lastkanban on lastkanban.id = (select top 1 id from userhistoryitem uhi where uhi.username = a.user_key and uhi.entitytype = 'RapidView' order by uhi.lastviewed desc) and lastkanban.username = a.user_key
left join userhistoryitem lastsearch on lastsearch.id = (select top 1 id from userhistoryitem uhi where uhi.username = a.user_key and uhi.entitytype = 'Searcher' order by uhi.lastviewed desc) and lastsearch.username = a.user_key
left join jiraissue i on i.id = (select top 1 id from jiraissue ji where ji.reporter = a.user_key order by created desc) and i.reporter = a.user_key
where u.active = 1 and dir.active = 1
order by 7 desc;Viewed Dashboard, Viewed Search, and Viewed Issue require any license. Viewed Board requires a Jira Software license.
To create an issue any license is required, unless it's a Service Management issue, as customers can create issues without the need for a license through the Service Management Portal.
If a given user has "old" dates on those columns, he or she is eligible to have the license revoked. Empty values mean the user has never performed that action.
Alternative query
The query below will show users from the group jira-servicedesk-users who have changeitem table entries after the set date using the format: "2022-03-01 00:00:00"
This can be used for different Jira applications using a "default" group such as "jira-servicedesk-users", or "jira-software-users", or "jira-core-users", or any other group.
SELECT au.lower_user_name, author_activities.last_activity
FROM app_user au
JOIN (
SELECT cg.author, MAX(cg.created) AS last_activity
FROM changeitem ci
JOIN changegroup cg ON ci.groupid = cg.id
WHERE cg.created > '2022-03-01 00:00:00'
GROUP BY cg.author
) AS author_activities ON au.user_key = author_activities.author
JOIN (
SELECT cu.lower_user_name
FROM cwd_user cu
JOIN (
SELECT DISTINCT cm.lower_child_name
FROM cwd_membership cm
WHERE cm.lower_parent_name = 'jira-servicedesk-users'
) AS members ON cu.lower_user_name = members.lower_child_name
) AS user_names ON au.lower_user_name = user_names.lower_user_name;
The output list can then be used to inactivate these users on another query.
JQLs on user activity
It's also of utmost importance to check whether the eligible users have performed other actions in Jira recently.
Replace USER NAME and 30 with the appropriate user name and the number of days into the past you want to consider.
Recent status transition
status changed by "USER NAME" after startOfDay("-30")Recent updates
assignee = "USER NAME" and updated > -30dIssues Created with License (for Jira Service Management)
created > startOfDay("-30") and reporter = "USER NAME" and project not in projectsWhereUserHasRole("Service Management Customers")Issues on projects in which the reporter is a Service Management Customer can be created through the Customer Portal and don't require a Service Management License. The query above should return results if the user has created issues in a way that require a license.