How to get a list of licensed Jira Software users in Jira when using nested groups
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
Summary
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.
Solution
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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;Was this helpful?