Step-by-step: OneDrive Sync Health | Microsoft Community Hub (2024)

This blog shows a step-by-step guide to getting OneDrive Sync Health information using Microsoft Graph Data Connect for SharePoint. This includes detailed instructions on how to extract OneDrive device sync information and use that to run analytics for your tenant.

If you follow these steps, you will have a Power BI dashboard like the one shown below, which includes total devices per OneDrive version, number of devices by Backup Folder enabled and total devices by date last updated. You can also use the many other properties available in the OneDrive Sync Health dataset.

To get there, you can split the process into 3 distinct parts:

• Set upyour tenant for Microsoft Graph Data Connect, configuring its prerequisites.
• Configure andrun a pipelineto get OneDrive Sync Health using Azure Synapse.
• Use Power BI to read the data about OneDrive Sync Health andshow it in a dashboard.

The first step in the process is to enable Microsoft and its prerequisites. You will need to do a few things to make sure everything is ready to run the pipeline:

• Enable Data Connect in your Microsoft 365 Admin Center. This is where your Tenant Adminwill check the boxes to enable the Data Connect and enable the use of SharePoint datasets.
• Create an application identity to run your pipelines. This is an application created in Azure Active Directory which will be granted the right permissions to run your pipelines and access your Azure Storage account.
• Create an Azure Resource Group for all the resources we will use for Data Connect, like the Azure Storage account and the Azure Synapse workspace.
• Create an Azure Storage account. This is the place in your Azure account where you will store the data coming from your pipeline. This is also the place where Power BI will read the data for creating the dashboards.
• Create a container and folder in your Storage Account. This is the location where the data will go.
• Grant the application identity the required access to the Storage account. This makes sure that the application identity has permission to write to the storage.
• Add your Microsoft Graph Data Connect application in the Azure Portal. Your Microsoft Graph Data Connect application needs to be associated with a subscription, resource group, storage account, application identity and datasets.
• Finally, your Global Administrator needs to use Microsoft Admin Center to approve Microsoft Graph Data Connect application access.

Let us look at each one of these.

The first preparation step is to go into Microsoft 365 Admin Center and enable Microsoft Graph Data Connect.

• Navigate to Microsoft 365 Admin Centerathttps://admin.microsoft.com/and make sure you are signed in as a Global Administrator.
• Select the option toShow alloptions on the left.
• Click onSettings, then onOrg settings.

• Select the settings forMicrosoft Graph Data Connect.

• Check the box toturn Data Connecton.
• Make sure to also check the box to enable access to theSharePoint and OneDrive datasets.
• IMPORTANT: You must wait 48 hours for onboarding your tenant and another 48 hours for the initial data collection and curation. For example, if you check the boxes on August 1st, you will be able to run your first data pull on August 5th, targeting the data for August 3rd. You can continue with the configuration, but do not trigger your pipeline before that.

You will need to create an Application in Microsoft Entra ID (formerly Azure Active Directory) and set up an authentication mechanism, like a certificate or a secret. For these application configuration tasks, you will need the role of Application Administrator. You will use this Application later when you configure the pipeline.

IMPORTANT: Creating the application must be done by a different user than the user with the Global Administrator role who completes step 1h.

Here are the steps:

• Navigate to theAzure Portalathttps://portal.azure.com
• Find Microsoft Entra IDservice in the list of Azure services.
• Select the option forApp Registrationon the list on the left.
• Click the link toNew Registrationto create a new one.

• Enter an app name, select “this organizational directory only” and click on theRegisterbutton.
• On the resulting screen, select the link toAdd a certificate or secret.
• Select the “Client secrets” tab and click on the option forNew client secret.
• Enter a description, select an expiration period, and click theAddbutton.
• Copy the secret value(there is a copy button next to it). We will need that secret value later.
• Secret values can only be viewed immediately after creation. Save the secret before leaving the page.

• Click on theOverviewlink on the left to view the details about your app registration.
• Make sure to copy the application (client) ID. We will need that value later as well.

You will need to create an Azure Resource Group for all the resources we will use for Data Connect, including the Storage Account and Synapse Workspace.

Here are the steps.

• Navigate to theAzure Portalathttps://portal.azure.com
• Find theResource Groupsin the list of Azure services.

• Click on theCreatelink to create a new resource group.
• Select a name and a region.
• IMPORTANT: You must use a region that matches the region of your Microsoft 365 tenant.

• Click onReview + Create, make sure you have everything correctly entered and clickCreate.

You will need to create an Azure Storage Account to store the data coming from SharePoint. This should be an Azure Data Lake Gen2 storage account. You should also authorize the Application you created to write to this storage account. Here are the steps.

• Navigate to theAzure Portalathttps://portal.azure.com
• Find theStorage accountsservice in the list of Azure services.

• Click on theCreatelink to create a new storage account.
• Select a subscription, resource group (created in step 1c), account name, region, and type (standard is fine).
• Make sure your new account name contains only lowercase letters and numbers.
• IMPORTANT: You must use a region that matches the region of your Microsoft 365 tenant.

• Click on theAdvancedtab. UnderData Lake Storage Gen2check the box toEnable hierarchical namespace.
• Click onReview, make sure you have everything correctly entered and clickCreate.
• Wait until the deployment is completed and click onGo to resource.
• Click on theAccess keysoption on the left to see the keys to access the storage account.
• Click on Show for one of the two keys and use the copy icon whenever you need the key.

You will need to grant the Application Id the required access to the Storage Account. Here are those steps:

• In theStorage accountyou just created, click theAccess Control (IAM)option on the left.
• Click on the link toAddon the horizontal bar.

• Click on the link toAddon the horizontal bar and click on the option toAdd role assignment.
• In theRoletab, select the built-inStorage Blob Data Contributorrole and click on theNextbutton.
• In theMemberstab, selectuser, group or service principaland click on theSelect memberslink.
• In theSelect memberswindow, click on the application id you created in item 1b and click the Select button.
• Then click on theReview + Assignbutton.

• Review the role assignment and click on theReview + assignbutton.
• You’ve now completed the role assignment.

The next step is to create a container and folder for the data you will bring from Data Connect. Follow these steps:

• In theStorage accountyou just created, click theContainersoption on the left.
• You will see only the default $logs container in the list. Click on theContainerlink on the horizontal bar.

• Click on the newly created container and in that container, click on+ Add Directory to create a new folder for your dataset. For instance, you could call it “synchealth”.
• With that, you have a location to later store your data with the path as container/folder.

Your Microsoft Graph Data Connect application needs to be associated with a subscription, resource group, storage account, application identity and datasets. This will define everything that the app will need to run your pipelines.

• Search for the “Microsoft Graph Data Connect” service in the Azure Portal athttps://portal.azure.comor navigate directly tohttps://aka.ms/MGDCinAzureto get started.

• Select the option toAdd a new application.

• UnderApplication ID, select the one from step 1b and give it a description.
• SelectSingle-Tenantfor Publish Type.
• SelectAzure Synapsefor Compute Type.
• SelectCopy Activityfor Activity Type.
• Fill the form with the correctSubscriptionandResource Group(from step 1c).
• UnderDestination Type, select Azure Storage Account.
• UnderStorage Account, select the Storage Account we created in step 1d.
• UnderStorage Account Uri, select the option with “dfs” in the name.
• Click on “Next: Datasets”.

• In the dataset page, underDataset, select BasicDataSet_v0.OneDriveSyncHealth_v0.
• UnderColumns, select all.
• Click on “Review + Create” and click “Create” to finish.
• You will now see the app in the list for Graph Data Connect.

Your last step to enable the Microsoft Graph Data Connect is to have a Global Administrator approve the application.

• Make sure this step is performed by a Global administrator who is not the same user that created the application.
• Navigate to Microsoft 365 Admin Centerathttp://admin.microsoft.com/
• Select the option toShow alloptions on the left.
• Click onSettings, then onOrg settings.
• Click on the tab forSecurity & privacy.

• Select the option for settings for‎Microsoft Graph Data Connect‎ applications.

• You will see the app you defined with the statusPending Authorization.
• Double-click the app name to start the authorization.
• Follow the wizard to review the app data, the datasets, the columns and the destination, clicking Next after each screen.

• In the last screen, click onApproveto approve the app.

Note: The Global administrator that approves the application cannot be the same user that created the application. If it is, the tool will say “app approver and developer cannot be the same user.”

If you haven't already done it, set up the OneDrive sync health dashboard. That ensures that device data is being sent to the dashboard.To complete this step, follow the instructions athttps://learn.microsoft.com/en-us/sharepoint/sync-health#set-up-the-onedrive-sync-health-dashboard.

Note:After you enable the setting on devices, it can take up to three days for reports to be available.

Next, you will configure a pipeline in either Azure Data Factory or Azure Synapse. We will use Synapse here. You will trigger this pipeline to pull SharePoint data from Microsoft 365 and drop it into the Azure Storage account.

Here is what you will need to do:

• Create a new Azure Synapse workspace. This is the place where you create and run your pipelines.
• Use the Copy Data tool in Azure Synapse. This tool will help you with the task.
• Create a new source to get the OneDrive Sync Health dataset from Microsoft 365.
• Create a new destination with a storage folder in Azure Storage to receive the data.
• Deploy and trigger the pipeline.
• Monitor the pipeline to make sure it has finished running and that the data is available.

Let us look at each one of these.

To get started, you need to create an Azure Synapse workspace, if you do not already have one.

Here are the steps:

• Navigate to theAzure Portalathttps://portal.azure.com
• Find theAzure Synapse Analyticsservice in the list of Azure services.
• Click on theCreatelink to create the new Azure Synapse workspace.
• Enter the subscription, resource group (created in step 1d), the new workspace name, region, storage account name (created in step 1e) and new file system name.
• IMPORTANT: You must use a region that matches the region of your Microsoft 365 tenant.

• Click on theSecuritytab. Select the option toUse only Microsoft Entra ID authentication (formerly AAD authentication). Click on theReview + createbutton.
• ClickCreate. Wait until the deployment is completed and click onGo to resource.

Note: After you create an Azure Synapse workspace, you might run into an error that says, “The Azure Synapse resource provider (Microsoft Synapse) needs to be registered with the selected subscription”. You might also run into a validation error later with a message like “Customer subscription GUID needs to be registered with Microsoft.Sql resource provider”. These providers might not be registered with your subscription by default. If you run into these issues, see this doc onhow to register a new resource providerand make sure your subscription is registered with both Microsoft.Synapse and Microsoft.Sql resource providers. Thanks to Carl Grzywacz for pointing these out.

Our Azure Data Factory pipeline will use a data source (Microsoft 365) and a data sink (Azure Storage). Let us start by configuring the data source in our Data Factory.

Follow the steps:

• Navigate to theAzure Portalathttps://portal.azure.com
• Find theAzure Synapse Analyticsservice in the list of Azure services.
• Click on the name of your Azure Synapse workspace (created in item 2a).
• Click on theOpenlink inside the big box forSynapse Studio.

• In the Synapse Studio, select the fourth icon on the left to go to theIntegratepage.
• Click on the bug+icon and select the option for theCopy Data toolto start.

• Keep options for theBuilt-in copy taskandRun once now. Then click theNextbutton.

• You will then have to define the source and destination.

The first step is to define your data source, which will be Microsoft Graph Data Connect (Data Connect source).

Here are the steps you should take:

• On theSource data store page, click on theNew connectionoption.

• On theNew connectionpage, enter “365” on the search box and selectMicrosoft 365 (Office 365).

• Click theContinuebutton to reach the page to define the details of the new connection.
• Enter theNameandDescriptionfor the new connection
• Also enter theService principal IDand theService principal key.These are the application ID and the secret that we captured in step 1b.

• Click on theTest connectionoption on the bottom right to make sure the credentials are working.
• Then click on theCreatebutton to create the new connection and go back to theSource data storepage.
• This time around, the connection will be filled in and the list of datasets will be available.
• Check the box next toBasicDataSet_v0.OneDriveSyncHealth_v0and click on theNextbutton.

• In theApply Filterpage, click on next.

• Click on theNextbutton to finish the source section and move to the destination section.

Next, you need to point to the location where the data will go, which is an Azure Storage account.

Here are the steps:

• On theDestination data storepage, click on theNew connectionoption.

• Select the option forAzure Data Lake Storage Gen 2

• Click theContinuebutton to reach the page to define the details of the new connection.
• Enter theNameandDescriptionfor the new connection.
• Change the Authentication type toService Principal, add theStorage account namefrom the drop-down list.
• Enter theService principal IDand theService principal key. Again, these are the application id and the secret that we captured in step 1b.

• Click on theTest connectionoption on the bottom right to make sure the credentials are working.
• Then click on theCreatebutton to create the new connection and go back to theDestination data storepage.
• This time around, the connection will be filled in and a few options will be available.
• Enter aFolder path. This is the container and folder you created in step 1f and you can browse to it.

• ClickNextto reach theReview and finishpage of the Copy Data tool.

Now we will deploy the pipeline and run it.

Follow the steps:

• In theReview and finishpage, click theEditlink on the top right to enter a name and description for your pipeline. Then clickSave.

• Click on theNextbutton to start the deployment.

• Once it is all finished, click on theMonitorbutton to see how the pipeline is running.

After the data copy tool finishes, you can monitor the running pipeline. You will land in the main"pipeline runs"pages, with a list of pipelines.

In your case, there should be only one:

• If you click on thePipeline name, you will see the details for eachactivityin the pipeline. In this case, you should see only one activity in the pipeline, which is the copy of the dataset.

• Wait until the status for the activity and pipeline reachesSucceeded. This could take a few minutes, depending on the number of sites in your environment.
• Once the pipeline has finished running, the data will be in Azure Storage, in the container and folder that you have specified. It shows as one or more JSON files, plus a metadata folder with information about the request.

The last step is to use the data you just got to build a Power BI dashboard.

You will need to:

• Create a new Power BI file.
• Query the data from the Azure Storage account.
• Create your dashboard.

Now that you have the data in Azure Storage, you can bring it into Power BI to build reports and dashboards.

Here is how to get started:

• You will start by opening the Power BI desktop application.
• If you don’t have the application, download fromhttps://powerbi.microsoft.com/en-us/downloads/

Now you can bring the data into Power BI, directly from Azure.

• In your new Power BI report, in theHometab, click on theGet Datadropdown menu and click onMore.

• In the list of sources, selectAzure, click onAzure Data Lake Storage Gen2and click onConnect.

• Enter theURLwith the full path to the ADLS Gen2 data, with container and folder, in the following format:
  https://accountname.dfs.core.windows.net/container/folder
• This is the Storage Account name that you created in steps 1e and 1g.
• ClickOK.

• In the next screen you need to authenticate to the storage account.
• Select the option to provide anaccount key,which was mentioned in step 1e.
• ClickConnect.

• In the following screen you will see the list of JSON files coming from the storage account.

• Note that you get a few JSON files, but keep in mind that two of them are just metadata.
• Click on theTransform Databutton to load all the files into a Power Query.
• The Power Query Editor window will show, with the files listed.
• First, change the queryNamefrom Query1 to a more meaningful name.
• Next, scroll to the left until you find theFolder Pathcolumn.
• You should see one of the paths that includes a metadata folder. We want to filter that out.

• On the row with theFolder Paththat includes the word metadata, right click that cell, select theText Filtersoption and then theDoes Not Containoption. That will get rid of that row only.

• In the formula, make sure to enter
  = Table.SelectRows(Source, each not Text.Contains([Folder Path], "metadata"))
• Now that you removed the rows for metadata, scroll all the way to the right to find theContentcolumn.
• On theContentcolumn, click on the icon with two down arrows calledCombine Files(see arrow below)

• At this point Power BI does a whole lot to the data, including loading the JSON file, renaming the columns, and expanding the columns with structures (like Storage Metrics and Owner).
• You can now just click on theClose and Applybutton to close the Query Editor.

Now that the data is available in Power BI, let’s create some dashboards.

• After you close the Query Editor and go back to the main Power BI window, you will have all the Sites data available to you to create reports and dashboards. They will be under the Fields pane on the right.

• The schema for this dataset is available publicly athttps://github.com/microsoftgraph/dataconnect-solutions/blob/main/Datasets/data-connect-dataset-onedrivesynchealth.md. This shows the data type and a brief description of each column.
• You can now drag visualizations and fields to the main canvas. For instance, you can just double-click on thestacked bar chartin the visualizations and resize the chart to span the entire page. Then drag the SyncAppVersion field to theY-axisand the OneDriveDeviceId to theX-axis. That’s it!

You have triggered your first pipeline and populated a dashboard. Now there is a lot more that you could do.

Here are a few suggestions:

• Investigate the many datasets in Data Connect, which you can easily use in yourSynapseworkspace.
• Trigger your pipeline on a schedule, to always have fresh data in your storage account.
• Extend your pipeline to do more, like join multiple data sources or take the data to a SQL database.
• Publish your Power BI dashboard to share with other people in your tenant.
• Get additional information about each error using the details in the Sync Errors dataset, as described atOneDriveSyncErrors_v1.

You can read more about Microsoft Graph Data Connect for SharePoint at https://aka.ms/SharePointData. There you will find many details, including a list of datasets available, complete with schema definitions and samples.