Licensed user seems too high

[Davide Trombini]

This seems to be an old question which I never found properly answered and possibly applicable to other Atlassian products.

Recently our confluence server is getting closer to its license limit and I've tried to find out more detailed information about the users.

In order to do that, I've looked into the db and, once again, I cannot get even close to the numbers that I can see in the browser, not even if I count the users twice.

I really would like a clear answer from Atlassian about this.

And yes, I've tried all the recommended queries which returns even lower numbers that I find with my own queries.

Any help is very much appreciated.

Regards,

Davide.

[Nic Brough -Adaptavist-]

It is very hard to answer that question by digging through the database.

In the UI, go to global permissions and look at whom has "use confluence" and "administrator" access.  The combination of those users and groups, minus deactivated people will give you the number of users chewing up your licences.

[Davide Trombini]

Hello Nic, thanks for the reply.

The goal for a proper user count is to remove users that haven't actually used Confluence in a defined period of time.

This can easily be done by reading information from the db and script the result so that poweshell can do the rest of the work on the Active Directory. The problem is that I can never match the license count shown in the UI and I never found a proper answer to this although Atlassian provides guidelines on how to do this.

Therefore if even the Atlassian own information aren't reliable, I cannot trust what I see in the UI. A bug, perhaps?

[Nic Brough -Adaptavist-]

The problem here is that you are using SQL to do the counting, and your SQL is incorrect, it is missing something.

You can't match the licence count in the UI because your SQL is not looking at the right things.

Does it take into account all directories in use, disabled users, and all the groups with all three possible "can use" rights? 

For debugging your SQL, have you looked at whether the number returned is higher or lower than the actual number?  By how much?  How much has it varied in different tests?

Have you tried comparing the list of active users you get from the UI and from your SQL and looking at users who mismatch?

[Davide Trombini]

I think the problem is actually mentioned in the Atlassian guidelines:

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.

I did compare the data, from the database as well as domain.

My guess is that only the direct members of a group are written in the database and not the nested ones. I also know that a typical ldap filter (memberOf:1.2.840.113556.1.4.1941:) doesn't work well with any Atlassian products. If this filter would work, you wouldn't need to Enable Nested Groups but perhaps this isn't such an easy problem to solve.

I guess one solution would be for the domain admins to rearrange the groups or, perhaps, Atlassian could make a change in their code so that the users that use a license are actually logged and stored in the database regardless of the nesting.

I also believe that this is a common problem among many of the Atlassian products.

[Nic Brough -Adaptavist-]

The systems work from the information you give them about the users according to the licencing rules Atlassian have decided on.

The problem is your usage of SQL that does not fully understand the logic of provisioning the users.  I'd strongly recommend not using SQL, as it's giving you incorrect information.

[Davide Trombini]

Fair enough, although it's still an inconsistent implementation since the numbers don't match which makes me question the result. The number is SQL are absolutely correct as written there by Confluence. They are simply incomplete.

From an administration point of view, I miss the possibility to properly find out users membership and login dates to better use the license pool. It's probably due to the business model so that a plugin can be developed.

I'll move the domain users so that the numbers will match.

@Nic Brough -Adaptavist- thanks for the support.