How to list users who are counted towards the Crowd License

Still need help?

The Atlassian Community is here for you.

Ask the community

Purpose

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:

com.atlassian.crowd.manager.license

The license count, list of users (and the directory those users are from) will be displayed in log files.

Example

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 - Getting issue details... STATUS , 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:

Click here to expand...
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'));
tip/resting Created with Sketch.

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:

Click here to expand...
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;
tip/resting Created with Sketch.

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:

Click here to expand...
SELECT cu.lower_user_name,
       cu.directory_id
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'));
tip/resting Created with Sketch.

If you're running a Crowd version older than 2.7.3, please mind this bug:  CWD-3997 - Getting issue details... STATUS

tip/resting Created with Sketch.

This query for Crowd older than 2.11 will not include users in Nested Groups.



Last modified on Nov 16, 2018

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.