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

joseph — Fri, 23 Sep 2016 22:38:19 GMT

What is the SQL query to display the active users of Alfresco.Thanks for your timeThanks for your time

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

kaynezhang — Sat, 24 Sep 2016 16:15:26 GMT

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?

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

afaust — Sat, 24 Sep 2016 20:55:52 GMT

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_id
WHERE
   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.

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

joseph — Thu, 29 Sep 2016 18:51:19 GMT

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"]);
}

topic Re: What is the SQL query to display the active users of Alfresco in Alfresco Archive

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

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

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

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