How can I list the folders synchronized with Nuxeo Drive?

[ataillefer_]

We would like to know the folders / workspaces marked as Nuxeo Drive synchronization roots by our users.

This would allow us to raise awareness among our users who synchronize big volumes of documents by setting synchronization roots "too high" in the document hierarchy.

Do you have an SQL query to list such documents?

[ataillefer_ · Accepted solution]

The following query allows to list the documents (id and name) marked as Nuxeo Drive synchronization roots for each user:

select p.id, p.name, s.username, s.enabled from subscriptionitem s, hierarchy h, hierarchy p where s.id = h.id and h.parentid = p.id order by username asc;

It returns something like:

id                                         name                username        enabled
"a66c5b71-eb18-42eb-8c6c-5ba3caae4d40";    "Documentation";    "user1";        t
"a66c5b71-eb18-42eb-8c6c-5ba3caae4d40";    "TestFolder";       "user1";        f
"7cfe624d-5edf-412e-a822-cd6e7524be5d";    "TestFolder";       "user2";        t

The "enabled" field flags the documents that are actually registered as a synchronization root, in which case it is set to 't' for 'true'. It can be set to 'f' for 'false' if the document has once been registered as a synchronization root then unregistered.

You can also find the list of users who registered the document with id XXX as a synchronization root with this query:

select username, enabled from subscriptionitem s, hierarchy h where s.id = h.id and h.parentid = 'XXX';

Note that we have created NXP-17617 to track a new feature which consists of having a UI in the Admin Center to list the synchronization roots for a given user and the other way around the users registered for a given document. Administrators could use it to track the "abusive" synchronization roots such as top level folders, then ask users to unregister from them or do it directly.

[#3]

NXQL query SELECT * FROM Document WHERE ecm:mixinType='DriveSynchronized' AND ecm:isVersion=0 AND ecm:isTrashed=0

curl command:

curl -su Administrator:Administrator -H 'Content-Type: application/json' http://localhost:8080/nuxeo/api/v1/search/lang/NXQL/execute -G \
--data-urlencode "query=SELECT * FROM Document WHERE ecm:mixinType='DriveSynchronized' AND ecm:isVersion=0 AND ecm:isTrashed=0" \
| jq

[Mickaël_Schoent]

The DriveSynchronized facet is not the only “flag” identifying items that are synced with Nuxeo Drive. To list all enabled sync roots, one can also use that NXQL query:

SELECT ecm:uuid FROM Document WHERE drv:subscriptions/*1/enabled = 1