The query I intended should have returned 2 columns, lower_user_name and directory_name. I am baffled that it returned user and last login for you. To be clear, the query is:
SELECT DISTINCT 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 PERMTYPE='USECONFLUENCE' AND u.active = 'T' AND d.active = 'T' ORDER BY d.directory_name;
If you have an LDAP user directory you are familiar with the Confluence Admin>User Directories page, where the connection to LDAP was configured.There is also a Confluence Internal user directory on that page.
I did not mean to imply that you had duplicate users in your LDAP user directory groups, rather that you could have a user in the Confluence Internal Directory which has the same user name as a user in the LDAP user directory. Because of aggregating group memberships across directories you may verify that the user is not in the group in the LDAP directory, yet that user may be getting permissions from group membership in the other user directory.
Please review the example scenario under Managing Multiple Directories (You have to expand the example.) I would paste it here for your convenience but it has graphics.
The article with the query warns:
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.
Could I trouble you to run this one to see if there are any duplicate users?
having count(lower_user_name) > 1;
I just did some testing and it looks like aggregating group memberships apply even to disabled users.
Please attach a screen shot of the kpelzman user's View User page in User Management and a screen shot or shots that show the global permissions, from Confluence Admin > Global Permissions.
Thanks for collecting all these materials and running the queries, etc.
As so eloquently stated in How to get a list of active users counting towards the Confluence license:
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
The article linked above has SQL queries to find the users with permission to use Confluence. However, in your case, since you only have 10 or 11 users you could list them on the Confluence Admin>User Management page using the show all users link, and see who the 11 are.
As part of the Confluence installation, a super user is created to administer Confluence. Is it possible that this is the 11th user? If so, it should show in the list.
I look forward to hearing what you find out.
Yeah, the only problem is that I am pulling in all my users from LDAP, so I cant just look at the users page. Ill have to look the SQL way. As for the Global permissions, there are only 10 users with those permissions, and that is why I am so confused. It was working for a day or two, and then just stopped.
I thought about the super user as well, but, I disabled that account. (I know, it is not recommended).
I understand you are pulling in more users from LDAP than you are granting Global can-use permission to. In that case, if you cannot chase down the extra user in the UI, the SQL queries should hopefully do the job. I hope you have time to update this thread to tell us how it turns out.
I am curious how you generated the list you posted.
I expected the results of the query to look something like this, so we could see what directory each licensed user is in:
The list shows 9 users but the Licensed users field says 10. Because of aggregating group membership, you can remove a user from a group in one directory but it can retain membership in that group in another directory Managing Multiple Directories:
If the same username exists in more than one directory, the application will aggregate (combine) group membership from all directories where the username appears.
That means a user can be removed from a group in the LDAP user directory, but if the same username is in the Confluence Internal directory it could retain the membership there and be granted permission to use Confluence.
Please try the queries from How to get a list of active users counting towards the Confluence license. They aren't perfect but may give us a hint as to why we can't account for all your licensed users.
Badges are a great way to show off community activity, whether you’re a newbie or a Champion.Learn more
Hi Community! me again 🙂 If you’re a marketing team using Confluence, we want to hear your story! How did you start using Confluence? What are your use cases? What have been some of the benefits?...
Connect with like-minded Atlassian users at free events near you!Find a group
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no AUG chapters near you at the moment.Start an AUG
You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs