How to get a list of active users counting towards the Confluence license
Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.
To know exactly which users count against your license in Confluence.
Confluence's license count is based on Global Permissions. Users will count towards the license in the following ways:
- If the user is a member of a group that has global permissions to use Confluence
If the user is individually granted global permissions to use Confluence
Within the UI, you can get a listing of groups and users that are assigned Global Permissions by navigating to Confluence Admin > Global Permissions. You can find the groups via Confluence Admin > Groups to reveal their members.
Confluence 7.0.2 and later
If you're running Confluence 7.0.2 or later, you can write the list of licensed users to the application log.
To obtain a list of licensed Confluence users:
- Go to > General Configuration > Logging and Profiling.
Add an entry for
com.atlassian.confluence.user.DefaultUserAccessorand set the level to DEBUG.
- Tail your
- Go to > General Configuration > License details.
- Under Licensed users, choose Refresh.
In the log file, look for a line similar to the following:
2019-09-17 10:36:34,271 DEBUG [read-only-transaction:thread-1] [atlassian.confluence.user.DefaultUserAccessor] getUserNamesWithConfluenceAccess Found 3 licensed users: [user1, user2, user3]
See Configuring Logging for more information on changing the logging and profiling settings in Confluence.
Good to know
- This workaround may have a performance impact on your site. You should remove the
com.atlassian.confluence.user.DefaultUserAccessorentry once you've successfully written the list of users to the log file.
- Usernames will be written to your logs in plain text. This may have privacy implications for your site. If you ever need to remove personally identifiable information you'll need to also remove it from the log files.
Confluence 6.15.x and earlier
If you are using Confluence 3.5 to Confluence 6.15.x, you can use the following SQL queries to return a list of users that count towards the license.
Return users that belong to a group which has global permissions:
This SQL query may not return accurate results if you are using nested groups in LDAP or Crowd, or if you have users with duplicated usernames across multiple directories.
SELECT u.lower_user_name, d.directory_name FROM cwd_user u JOIN cwd_membership m ON u.id = child_user_id JOIN cwd_group g ON m.parent_id = g.id JOIN SPACEPERMISSIONS sp ON g.group_name = sp.PERMGROUPNAME JOIN cwd_directory d on u.directory_id = d.id WHERE sp.PERMTYPE='USECONFLUENCE' AND u.active = 'T' AND d.active = 'T' GROUP BY u.lower_user_name, d.directory_name ORDER BY d.directory_name;
Return users that were individually granted global permissions:
SELECT u.lower_user_name, d.directory_name FROM SPACEPERMISSIONS sp JOIN user_mapping um ON sp.PERMUSERNAME = um.user_key JOIN cwd_user u ON um.lower_username = u.lower_user_name JOIN cwd_directory d on u.directory_id = d.id WHERE sp.PERMTYPE = 'USECONFLUENCE' AND u.active = 'T' AND d.active = 'T' GROUP BY u.lower_user_name, d.directory_name ORDER BY d.directory_name;
Return how many members are counting towards the license.
SELECT COUNT(distinct cwd_user.user_name), cwd_group.group_name FROM cwd_group INNER JOIN cwd_membership ON cwd_membership.parent_id= cwd_group.id INNER JOIN cwd_user ON cwd_user.id = cwd_membership.child_user_id WHERE cwd_user.active = 'T' AND cwd_user.lower_user_name IN (SELECT u.lower_user_name FROM cwd_user u JOIN cwd_membership m ON u.id = child_user_id JOIN cwd_group g ON m.parent_id = g.id JOIN SPACEPERMISSIONS sp ON g.group_name = sp.PERMGROUPNAME JOIN cwd_directory d ON u.directory_id = d.id WHERE sp.PERMTYPE='USECONFLUENCE' AND u.active = 'T' AND d.active = 'T' GROUP BY u.lower_user_name, d.directory_name ORDER BY d.directory_name) GROUP BY cwd_group.group_name ORDER BY COUNT(cwd_user.user_name) DESC;
It may include duplicate users depending on the number of groups the user is a member of.
You may also use the following User Macro pulled from one of our Atlassian Community post to list down the users who are counted towards the license.
## Macro title: Last Login ## Macro has a body: N ## Body processing: Selected body processing option ## Output: Selected output option ## ## Developed by: Andrew Frayling ## Modified by: Michael Seager [Atlassian Support] ## Date created: 11/02/2012 ## Installed by: <your name> ## Macro to display the last login date of users who have access to the current space ## @noparams #set($containerManagerClass = $content.class.forName('com.atlassian.spring.container.ContainerManager')) #set($getInstanceMethod = $containerManagerClass.getDeclaredMethod('getInstance',null)) #set($containerManager = $getInstanceMethod.invoke(null,null)) #set($containerContext = $containerManager.containerContext) #set($loginManager = $containerContext.getComponent('loginManager')) #set($users = $userAccessor.getUsers()) <table class="confluenceTable"> <tr> <th class="confluenceTh">Count</th> <th class="confluenceTh">User</th> <th class="confluenceTh">Last Successful Login</th> </tr> #set($count = 0) #foreach($user in $users) ## list the last login date of users who can view the current space #if ($permissionHelper.canView($user, $space)) #set($count = $count + 1) <tr> <td class="confluenceTd">$count</td> <td class="confluenceTd">#usernameLink($user.name)</td> <td class="confluenceTd">$action.dateFormatter.formatDateTime($loginManager.getLoginInfo($user.name).lastSuccessfulLoginDate)</td> </tr> #end #end </table>
For more information on writing user macro, please refer to this documentation.