How to get a list of licensed Jira Software users in Jira when using nested groups
プラットフォームについて: Data Center のみ。 - This article only applies to Atlassian apps on the Data Center プラットフォーム。
この KB は Data Center バージョンの製品用に作成されています。Data Center 固有ではない機能の Data Center KB は、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。 Server* 製品のサポートは 2024 年 2 月 15 日に終了しました。Server 製品を実行している場合は、 アトラシアン Server サポート終了 のお知らせにアクセスして、移行オプションを確認してください。
*Fisheye および Crucible は除く
要約
When granting access to Jira Software through nested groups, the queries provided in the Get list of licensed users in Jira server KB article may not return accurate results because of how nested group memberships are stored in the database.
ソリューション
Use the following SQL query to recursively get all nested groups that grant access to Jira Software:
⚠️ This query was only tested on PostgreSQL. Please contact your DBA to modify this query for other database management systems.
SELECT distinct b.directory_name AS "Directory",
b.user_name AS "Username",
to_timestamp(CAST(ca.attribute_value AS BIGINT)/1000) AS "Last Login"
FROM (select cu.user_name, cu.lower_user_name, d.directory_name, cu.id, RANK() OVER(PARTITION BY cu.user_name ORDER BY d.directory_position asc) as "Rank"
from (WITH RECURSIVE nested AS
(
select m.* from cwd_membership m where m.lower_parent_name in (select group_id from licenserolesgroup where license_role_name = 'jira-software')
UNION ALL
select m.* from cwd_membership m
join nested on m.lower_parent_name = nested.lower_child_name
),
all_users AS
(
select * from nested
where membership_type = 'GROUP_USER'
)
SELECT
parent_name,
child_name,
child_id,
directory_id
FROM all_users) u
JOIN cwd_directory d ON u.directory_id = d.id
join cwd_user cu on CU.id = u.child_id
where cu.active = 1
AND d.active = 1) as b
LEFT JOIN cwd_user_attributes ca ON b.id = ca.user_id AND ca.attribute_name = 'login.lastLoginMillis'
WHERE "Rank" = 1
ORDER BY "Last Login" DESC;この内容はお役に立ちましたか?