Currently, when support has to deal with a license count mismatch problem, it's very difficult to come up with an appropriate diagnostic, as the sql to generate a usable list is not database agnostic, nor does it cover every edge case (eg. individual adds, nested groups).

SQL to produce:

Users in Groups with Global Permissions:

SELECT distinct 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
WHERE permtype='USECONFLUENCE';


Individuals with Global Permissions:

SELECT distinct permusername
FROM spacepermissions
WHERE permtype='USECONFLUENCE';

Refinements for different DBRM's are available, such as using postgres9's string_agg function to list the directory id's (3.5+) or using mysql's group_concat aggregate function.

It would be very helpful if there was just a debug screen that would print out how Confluence arrived at the current license count.