How to list users who are counted towards the Crowd License
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
Crowd only shows the number of users counting towards the license; However, it does not show the username of the users who are actually counted towards the license.
Solution
In order to list all the users that counts towards the license in Crowd 3.1.3 or newer please enable following log in DEBUG level and recalculate your user total:
1
com.atlassian.crowd.manager.licenseThe license count, list of users (and the directory those users are from) will be displayed in log files.
Example
1
2
2018-03-28 07:12:15,789 Caesium-2-2 DEBUG [crowd.manager.license.CrowdLicenseManagerImpl] Finished counting licensed users, 9 total
2018-03-28 07:12:15,790 Caesium-2-2 DEBUG [crowd.manager.license.CrowdLicenseManagerImpl] Licensed users are: [leela (131074), professor (131074), test (753665), zoidberg (131074), admin (131073), hermes (131074), bender (131074), amy (131074), fry (131074)]For Crowd versions lower than 3.1.3, please follow the instructions listed below.
Versions pre 3.1.3
Before Crowd 2.11, a user that existed in more than one directory was counted multiple times (once per directory). However, due to the implementation of CWD-4735 - Crowd license count same user in different directory as only 1 user license., since Crowd 2.11 this scenario only counts the user once.
Having said that, please run the database query below according to your Crowd version to list the usernames that count against the license.
If running Crowd 2.11 or newer:
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 cu.lower_user_name
FROM cwd_user cu
INNER JOIN cwd_directory cd ON cu.directory_id = cd.id
WHERE cd.active = 'T'
AND (cu.lower_user_name IN
(SELECT u.lower_user_name
FROM cwd_user u
JOIN cwd_membership m ON u.id = m.child_id
JOIN cwd_group g ON m.parent_id = g.id
JOIN cwd_app_dir_group_mapping gm ON gm.group_name = g.group_name
JOIN cwd_app_dir_group_mapping gm_1 ON gm_1.directory_id = g.directory_id
JOIN cwd_application ca ON ca.id = gm.application_id
JOIN cwd_app_dir_mapping dm ON dm.application_id =ca.id
JOIN cwd_directory cd ON cd.id = dm.directory_id
WHERE dm.allow_all ='F'
AND u.active ='T'
AND ca.active='T'
AND gm_1.directory_id=gm.directory_id
AND cd.active = 'T')
OR lower_user_name IN
(SELECT u.lower_user_name
FROM cwd_user u
JOIN cwd_app_dir_mapping dm ON dm.directory_id =u.directory_id
JOIN cwd_application ca ON dm.application_id =ca.id
JOIN cwd_directory cd ON cd.id = dm.directory_id
WHERE dm.allow_all ='T'
AND u.active ='T'
AND ca.active='T'
AND cd.active = 'T'));Note that this query will not count users that belong to a directory that has Caching Disabled. See more about this configuration setting here: Configuring Caching for an LDAP Directory
If running Crowd 2.11 or newer and have nested memberships:
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
30
31
32
33
34
35
36
37
38
39
40
SELECT DISTINCT cu.lower_user_name
FROM cwd_user cu
INNER JOIN cwd_directory cd ON cu.directory_id = cd.id
WHERE cd.active = 'T'
AND (cu.lower_user_name IN
(SELECT DISTINCT u.lower_user_name
FROM cwd_group g
LEFT JOIN cwd_membership mem1 ON mem1.parent_id = g.id
LEFT JOIN cwd_membership mem2 ON mem2.parent_id = mem1.child_id
LEFT JOIN cwd_membership mem3 ON mem3.parent_id = mem2.child_id
LEFT JOIN cwd_membership mem4 ON mem4.parent_id = mem3.child_id
LEFT JOIN cwd_membership mem5 ON mem5.parent_id = mem4.child_id
LEFT JOIN cwd_membership mem6 ON mem6.parent_id = mem5.child_id
LEFT JOIN cwd_user u ON u.id IN (mem1.child_id,
mem2.child_id,
mem3.child_id,
mem4.child_id,
mem5.child_id,
mem6.child_id)
JOIN cwd_app_dir_group_mapping gm ON gm.group_name = g.group_name
JOIN cwd_app_dir_group_mapping gm_1 ON gm_1.directory_id = g.directory_id
JOIN cwd_application ca ON ca.id = gm.application_id
JOIN cwd_app_dir_mapping dm ON dm.application_id =ca.id
JOIN cwd_directory cd ON cd.id = dm.directory_id
WHERE dm.allow_all ='F'
AND u.active ='T'
AND ca.active='T'
AND gm_1.directory_id=gm.directory_id
AND cd.active = 'T')
OR lower_user_name IN
(SELECT DISTINCT u.lower_user_name
FROM cwd_user u
JOIN cwd_app_dir_mapping dm ON dm.directory_id =u.directory_id
JOIN cwd_application ca ON dm.application_id =ca.id
JOIN cwd_directory cd ON cd.id = dm.directory_id
WHERE dm.allow_all ='T'
AND u.active ='T'
AND ca.active='T'
AND cd.active = 'T'))
ORDER BY cu.lower_user_name;Note that this query will only count 5 levels of nesting memberships (Parent group + 5 levels of child groups). Modify the query accordingly if you need more levels.
Note that this query will not count users that belong to a directory that has Caching Disabled. See more about this configuration setting here: Configuring Caching for an LDAP Directory
If running Crowd older than 2.11:
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
SELECT cu.lower_user_name,
cu.directory_id
FROM cwd_user cu
INNER JOIN cwd_directory cd ON cu.directory_id = cd.id
WHERE (cu.lower_user_name IN
(SELECT u.lower_user_name
FROM cwd_user u
JOIN cwd_membership m ON u.id = m.child_id
JOIN cwd_group g ON m.parent_id = g.id
JOIN cwd_app_dir_group_mapping gm ON gm.group_name = g.group_name
JOIN cwd_app_dir_group_mapping gm_1 ON gm_1.directory_id = g.directory_id
JOIN cwd_application ca ON ca.id = gm.application_id
JOIN cwd_app_dir_mapping dm ON dm.application_id =ca.id
JOIN cwd_directory cd ON cd.id = dm.directory_id
WHERE dm.allow_all ='F'
AND u.active ='T'
AND ca.active='T'
AND gm_1.directory_id=gm.directory_id)
OR lower_user_name IN
(SELECT u.lower_user_name
FROM cwd_user u
JOIN cwd_app_dir_mapping dm ON dm.directory_id =u.directory_id
JOIN cwd_application ca ON dm.application_id =ca.id
JOIN cwd_directory cd ON cd.id = dm.directory_id
WHERE dm.allow_all ='T'
AND u.active ='T'
AND ca.active='T'));If you're running a Crowd version older than 2.7.3, please mind this bug: CWD-3997 - Disabled applications still counting under "Current Users" total.
This query for Crowd older than 2.11 will not include users in Nested Groups.
Was this helpful?