How to find OneDrive users not running Sync

[Guest Jose_Barreto]

1. Introduction

 

 

 

We recently introduced a OneDrive Sync Health dataset in Microsoft Graph Data Connect. This provides a list of all devices running Sync, including details about whether they have enabled Folder Backup and are running into any errors. This ensures that any files being created or updated are properly synchronized with the OneDrive cloud. This makes sure that even if your computer is lost or broken, your files are safely stored.

 

 

 

However, there is another potential issue you should track as a OneDrive administrator. There could be users that are not running OneDrive Sync at all. These users could be saving files locally to their desktops or laptops without enabling OneDrive Sync. If their computer runs into any issues, they might find that their OneDrive in the cloud was not being updated at all.

 

 

 

This article explains how you can use the MGDC to get data about OneDrive sites and Sync Health to identify users that are not properly running Sync.

 

 

 

2. Grab the datasets

 

 

 

To get started, you need to get the two required MGDC datasets: Sites and Sync Health. To pull them, follow the instructions below:

 

 

 

• Step-by-step: SharePoint Sites
  
• Step-by-step: OneDrive Sync Health
  

 

 

 

The Sites dataset includes one object for each SharePoint site in the tenant. That includes every OneDrive, which is essentially just a type of SharePoint site. You can identify a OneDrive site by the template id (OneDrive uses template id 21).

 

 

 

The Sync Health dataset includes one object for each device running OneDrive Sync. This includes any attempt to synchronize in the last 30 days. It is important to note that if the user never enabled OneDrive Sync or has not attempted to synchronize in the last 30 days, there will be no data for that user in the Sync Health dataset.

 

 

 

Note: Some of the instructions are the same for both datasets. You can re-use things like the application registration, Azure storage account, MGDC consent and some of the Synapse resources.

 

 

 

3. Load into Power BI

 

 

 

The last step in the step-by-step instructions above is to load the datasets into Power BI. For this step, make sure to use Power BI desktop and load the two datasets into the same PBIX file.

 

 

 

After you’re done, the “Data” section on the right should show the two datasets. I would suggest renaming the datasets to “Sites” and “SyncHealth” instead of keeping the default names you get after you load the JSON files.

 

 

 

To rename them, just collapse the two datasets on the “data” section on the right (to make it easier to see them), then right-click the dataset name and use the “Edit query” option.

 

 

 

[ATTACH type=full" alt="Jose_Barreto_0-1724292723566.png]63629[/ATTACH]

 

4. Filter the Sites for OneDrive

 

 

 

While editing the query for the Sites dataset, you can add a filter to include only the sites that are of type OneDrive. This is basically a filter on the RootWeb.TemplateId column where it equals 21. Simply find the arrow next to that column to enable a filter.

 

 

 

[ATTACH type=full" alt="Jose_Barreto_1-1724292723575.png]63630[/ATTACH]

 

 

 

5. Manage relationships

 

 

 

With both datasets loaded into Power BI, you can now add a relationship between them. In the relationship view, click the “Manage relationships” in the bar. Or you can right click the dataset set on the Data section and select “Manage Relationships”.

 

 

 

[ATTACH type=full" alt="Jose_Barreto_2-1724292723580.png]63631[/ATTACH]

 

 

 

You want to create a one-to-many relationship between Sites and Sync Health, matching the Site’s Owner.Email with Sync Health’s UserEmail.

 

 

 

[ATTACH type=full" alt="Jose_Barreto_3-1724292723583.png]63632[/ATTACH]

 

 

 

6. Visualize using a table

 

 

 

Finally, create the visualization to see which OneDrive sites have no matching device in the Sync Health relationship. I used a simple “Table” visualization, pulling columns from either Sites or SyncHealth. Now that we have a relationship between the two, Power BI knows how to match data coming from both sides.

 

 

 

[ATTACH type=full" alt="Jose_Barreto_4-1724292723593.png]63633[/ATTACH]

 

 

 

Note that I have renamed some columns in the table. There is an option to “rename for this visual” if you right click a column under “Visualizations”.

 

 

 

Also make sure to select the option to “Show items with no data” when adding Sync Health columns. This will make sure you show the sites with no matching device in Sync Health.

 

 

 

[ATTACH type=full" alt="Jose_Barreto_5-1724292723603.png]63634[/ATTACH]

 

Here’s what your table can look like:

 

 

 

[ATTACH type=full" alt="Jose_Barreto_6-1724292723607.png]63635[/ATTACH]

 

 

 

7. Conclusion

 

 

 

I hope this post helped you understand how to find OneDrive users that are not running OneDrive Sync. For more information about these and other scenarios in Microsoft Graph Data Connect for SharePoint, see the overview post at Links about Microsoft Graph Data Connect for SharePoint.

 

Continue reading...