User and Group count in alfresco

Hello everyone !

At some point of time you must have come across a requirement to find out what is the total number of users and groups available in the repository.

This can be achieved by performing database queries against alfresco database. I tried to query against the database and identified the queries to get this done. For me it was giving accurate results.

This is how I identified the total number of users -
Step-1 - get the qname id for the user.
SELECT ID FROM alf_qname WHERE local_name='person'

Step-2 - Perform a query against the alf_node to get the total user count.
SELECT COUNT(*) FROM alf_node WHERE type_qname_id = 'id retrieve from step-1'

This will give you the user count including deleted and non-deleted users. In alfresco, deleted user entries do remain present in the database. Hence, if you just want to eliminate deleted users from the total user's count then use following -

Step-3 - Get the store id
SELECT ID FROM alf_store WHERE protocol='workspace' AND identifier='SpacesStore'

Step-4 - Get user count eliminating deleted users.
SELECT COUNT(*) FROM alf_node WHERE type_qname_id = 'id retrieve from step-1' AND node_deleted = 0 AND store_id='store_id retrieved in step-3'

That's it. You have got the user count. Now if you want to find out group count, you already know how to do it based on above queries, you just need to manipulate them for groups.

I have not tried the queries on the newer version of alfresco.

Hope this helps.

Thanks,
Ramesh C