How to audit license usage and user activity in Jira

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。

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.

*Fisheye および Crucible は除く

    

本記事で説明している手順は、現時点でのものとなります。そのため、一部のお客様で特定の状況下で動作したという報告がありますが、正式にサポートされているわけではなく、お客様の特定のシナリオで動作することを保証するものではありません。

本番環境での実施の前に一通り非本番環境で検証し、成功しなかった場合にはサポートされている代替案にフォール バックしてください。

また、アトラシアン サポートのサポート対象外のご質問の場合には、Community もご活用ください。

要約

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 - 課題詳細を取得中... ステータス


環境

Jira Core or Software 7, 8, and 9.

Jira Service Management 3, 4, and 5.


ソリューション

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.

PostgreSQL
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;
Oracle 19c
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;
MySQL
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;
SQL Server
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.

Tested on Postgres/Oracle 19c/MySQL/SQL Server
select lower_user_name from app_user where user_key in (
select distinct author from changeitem CI join changegroup CG on CI.groupid = CG.id 
where created > '2022-03-01 00:00:00' and author in (
select user_key from app_user where lower_user_name in (
select lower_user_name from cwd_user where lower_user_name in (
select distinct lower_child_name from cwd_membership where lower_parent_name = 'jira-servicedesk-users'))));

(info) 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

JQL for Jira's issue search
status changed by "USER NAME" after startOfDay("-30")

Recent updates

JQL for Jira's issue search
assignee = "USER NAME" and updated > -30d

Issues Created with License (for Jira Service Management)

JQL for Jira's issue search
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.




Last modified on Mar 27, 2024

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.