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:
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.
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.
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”.
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.
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.
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.
Here’s what your table can look like:
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 https://aka.ms/SharePointData.
Microsoft
