How to find OneDrive users not running Sync

1. Introduction

We recently introduced the OneDrive Sync Health dataset in Microsoft Graph Data Connect. This provides a list of all devices running Sync, including details on whether they have enabled folder backup and whether they are experiencing errors. This ensures that all files created or updated are properly synced to the OneDrive cloud. This ensures that your files are safe even if your computer is lost or broken.

But there is another potential issue that you need to track as a OneDrive administrator. You may have users who do not have OneDrive sync enabled at all. These users may save files locally on their desktop or laptop without enabling OneDrive sync. If something goes wrong on their computer, you may find that OneDrive in the cloud is not updated at all.

This article describes how to use MGDC to retrieve data about your OneDrive site and identify users who are not syncing properly through sync status.

2. Get the dataset

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

The Sites dataset contains one object for each SharePoint site in the tenant. This includes all OneDrives, which are essentially just a type of SharePoint site. You can identify a OneDrive site by its template ID (OneDrive uses template ID 21).

The sync status data set contains one object for each device that is running OneDrive sync. This includes sync attempts made over the last 30 days. It is important to note that if a user has never enabled OneDrive sync or has not attempted a sync in the last 30 days, there will be no data for that user in the sync status data set.

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

3. Load to Power BI

The final step in the above step-by-step instructions is to load the datasets into Power BI. For this step, you will need to use Power BI Desktop and load both datasets into the same PBIX file.

Once complete, you will see two datasets in the “Data” section on the right. I recommend renaming the datasets to “Sites” and “SyncHealth” instead of keeping the default names you get after loading the JSON file.

To rename them, simply collapse the two datasets in the “Data” section on the right (so they are easier to see). Then right-click on the dataset name and use the “Edit Query” option.

4. Site filtering for OneDrive

While editing a query for a Sites dataset, you can add a filter to only include sites of type OneDrive. This is basically a filter on the RootWeb.TemplateId column, which is equal to 21 here. Simply find the arrow next to that column and activate the filter.

5. Relationship Management

Now that both datasets are loaded into Power BI, you can add relationships between them. In the Relationships view, click “Manage Relationships” in the bar. Alternatively, you can right-click on the dataset set in the Data section and select “Manage Relationships.”

I want to create a one-to-many relationship between Sites and Sync Health, and match the Owner.Email in Sites with the UserEmail in Sync Health.

6. Visualization using tables

Finally, create a visualization to see which OneDrive sites have no matching devices in the Sync Health relationship. I used a simple “table” visualization and pulled in columns from Sites or SyncHealth. Now that there is a relationship between the two sites, Power BI knows how to match the data coming from both sides.

Note that I have renamed some of the columns in the table. If you right-click on a column in “Visualizations”, you will see an option to “Rename this visual”.

You should also check the “Show items with no data” option when adding the Sync Health column. This will allow Sync Health to show sites that don’t have matching devices.

The table is as follows:

7. Conclusion

I hope this post helped you understand how to find OneDrive users who are not running OneDrive sync. For more information on these and other scenarios with Microsoft Graph Data Connect for SharePoint, see the following overview posts: https://aka.ms/SharePointData.