Have a screen that shows a list of users counting towards Confluence license

Problem Definition

We currently have a count of the licenses currently in use in Confluence, but we don't have a list of which users are consuming these licenses.

Suggested Solution

• Have a screen that will list out the users that are consuming towards the license, complete with the details on which group/user directory they belong to
  • Somewhat like what JIRA have in their Application Access page. See screenshot for details

Why this is important

When users have a Confluence instance with a large set of users and nested groups, sometimes the count is different than the actual number of users. It is important to know exactly which users are consuming these licenses.

Workaround

If using Confluence 7.0.2, or enterprise releases 6.6.17+ or 6.13.9+:

1. Go to the "Logging and profiling" administration screen
2. Add an entry for "com.atlassian.confluence.user.DefaultUserAccessor" with level "DEBUG"
3. Go to the "License Details" administration screen
4. Tail your Confluence log file
5. Hit the "Refresh" button under the "Licensed users" section
6. Look for a line like this in your log file

2019-09-17 10:36:34,271 DEBUG [read-only-transaction:thread-1] [atlassian.confluence.user.DefaultUserAccessor] getUserNamesWithConfluenceAccess Found n licensed users: [user1, user2, ...] 

Ensure that only selected groups are counted towards the license:

1. Create a group with all users that should be counting towards the license;
2. Create a new filter using the group created's DN;
3. In Confluence, go to the cog icon >> User Management;
4. On the left sidebar, select User Directories;
5. Click to Edit your current directory;
6. Under User Schema >> User Object Filter, paste the filter created;
7. Save the changes and synchronize.

Use SQL query to list out users that are counted towards the license:

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

However, the above SQL query will not list users that are counted towards the license as a result of nested groups.

Create a user macro:

Create a User Macro
Use the following code into the macro:

## 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>

Add the macro into a page
Do note that this is a macro created by third party. There is no guarantee that this user macro will be working in your version and in the future version.
Also note that this user macro will not count if the user(s) has no view permission to the space this macro is placed in. It is recommended to place this in a space that everyone has view permission. (Page Restriction however, is not affected)

Script based on the REST API

If you use a specific group to manage access to Confluence, then the following shell script will give you the list of all users that are assigned to that group.
jq CLI is needed to format the API method output.

USRNAME=admin
USRPWD=admin
CONFBASEURL=http://localhost:6682/confluence
GROUP_NAME=confluence-users
echo "Username;Fullname" > /tmp/confluence_users.list

REST_NEXT_LINK=/rest/experimental/group/$GROUP_NAME/member
while [ "$REST_NEXT_LINK" != "null" ]; do
 REST_FULL_RESULT=$(curl --user $USRNAME:$USRPWD -H "Content-Type: application/json" -H "Accept: application/json" $CONFBASEURL$REST_NEXT_LINK 2>/dev/null)
 echo $REST_FULL_RESULT | jq -r '.results[] | "\(.username);\(.displayName)"' >> /tmp/confluence_users.list
 REST_NEXT_LINK=$(echo $REST_FULL_RESULT | jq -r '._links.next' 2>/dev/null)
done