What is the SQL query to display the active users of Alfresco

[joseph]

What is the SQL query to display the active users of Alfresco.

Thanks for your time

Thanks for your time

[kaynezhang]

By default users are saved just as other nodes in content repository. user node  has "user" as type .It is complex to query database directly ,why do you want to execute SQL query?

[afaust]

Ideally you would determine that number by performing a FTS query in Alfresco directly and not via SQL. The query for this would be

TYPE:"cm:person"‍

This can be executed in the node browser but be aware that it may limit the number of results shown. Alternatively, you can query using the JavaScript console and access the numberFound result property (Alfresco 5.0 and above)

var resultSet = search.queryResultSet({   language : 'fts-alfresco',   query : 'TYPE:"cm:person"'});print(resultSet.meta.numberFound);‍‍‍‍‍‍

If you really MUST execute a SQL query, then - with minor adaptions depending on your specific database system - you can use to find out the number of person nodes created in Alfresco

SELECT count(n.id)   FROM alf_node n   LEFT JOIN alf_store s ON s.id = n.store_id   LEFT JOIN alf_qname q ON q.id = n.type_qname_id   LEFT JOIN alf_namespace ns ON ns.id = q.ns_idWHERE   s.protocol = 'workspace'   AND s.identifier = 'SpacesStore'   AND q.local_name = 'person'   AND ns.uri = 'http://www.alfresco.org/model/content/1.0'‍‍‍‍‍‍‍‍‍‍

Be aware though that all of this is only an approximation. If you have configured an authentication subsystem against an LDAP, AD or other external user directory, the number of users that can login may be much higher unless you have also configured a synchronization that ensures ALL users are proactively synchronized. Additionally, when users are disabled in LDAP / AD they are still contained in Alfresco though they can no longer login.

Another thing: If you want to find out the number of users that are CURRENTLY USING the system (instead of the number of users maintained in the system) I suggest you use the Support Tools addon if you are an Enterprise user or the "Liberated" Support Tools addon if you are running Community. Both provide a tool called "Active Sessions" that displays the number of currently logged in users whose tickets have not yet expired.

[joseph · Accepted solution]

Thanks for your answers. We finally did it using Lucene search:

var resultSet = search.luceneSearch('TYPE:"cm:person" AND -ASPECT:"cm:personDisabled"', "@sys:node-uuid", true, -1);
for (var i = 0; i < resultSet.length; i++) {
    print(resultSet[i].getProperties()["cm:userName"]);
}