How to get a list of active users counting towards the Confluence license

Still need help?

The Atlassian Community is here for you.

Ask the community

Purpose

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

Feature request

If you would like a function in Confluence that will enable you to check a list of active users automatically, please vote on this feature request:  CONFSERVER-54271 - Getting issue details... STATUS

Solution

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.

Solution 1

If you are using Confluence 3.5 or later, 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;

Solution 2

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.

Last modified on May 20, 2018

Was this helpful?

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