Get historical refresh records for all datasets within organization via PowerShell

[v-rzhou-msft]

Scenario:

As more and more reports are configured to be refreshed, sometimes you may encounter refresh failures. So we need quickly detect whether a dataset refresh has succeeded or not succeed. Based on such a scenario, we can then use PowerShell's related commands to get historical refresh records of all datasets in workspace.

Detailed steps：

Permissions：

The users must have administrator rights--Office 365 Global Administration/ Power BI Service Administrator, or authenticate using a service principal when they want to get a list of workspaces in the organization or a list of datasets in the workspace. Delegated permissions are supported.

Install the Power BI Management module by entering the following code in your local PowerShell. This is the Power BI Management module for PowerShell. After successful installation of this module, you can manage Power BI from PowerShell window.

Code: Install-Module -Name MicrosoftPowerBIMgmt

It’s a bit complicated to get historical refresh records of all datasets in all workspaces through the code directly. Therefore, I will break it down into three parts and it is getting deeper gradually.

Part1: Get the history of refresh records for the specified dataset in the specified organization workspace.

1.After the installation is complete, use the following command to log into your Power BI service account. To operate Power BI in PowerShell, you must log into the Power BI account successfully. Only in this way, the username information will be visible in PowerShell.

Code: Connect-PowerBIServiceAccount

2.After successfully performing the above two steps, we can invoke commands in PowerShell to manage Power BI.

3.Then enter the following command to view all workspaces in my tenant and their related information. We need to pay attention to the ID and Name information, because we can access the corresponding workspace through it in the subsequent steps.

Code: Get-PowerBIWorkspace -Scope Organization

4.First we can start with the basics, getting the refreshed records of the specified dataset in the specified workspace.

5.According to the Workspace created in the Power BI Service, find the ID of the specified workspace in the above screenshot and get the ID of all the datasets in this workspace with the following code. Because in the subsequent steps, we need to get the historical refresh records of the specified dataset by ID.

Code: Get-PowerBIDataset -Scope Organization -WorkspaceId 1855d3a1-caf7***

6.From the above screenshot, we can see that the returned result contains the ID of the Dataset and the name of the creator (ConfiguredBy).

7.With the ID of Dataset, we can query the refresh history under the corresponding ID. However, since PowerShell command itself does not have the command to query the refresh history, we need to call the PowerShell API.

Attached: Invoke-PowerBIRestMethod -Url 'groups' -Method Get

8.We replace the {groupID} and {DatasetID} in the below sample code with the Premium workspace ID and Dataset ID we obtained earlier, and that's the API we need for our query. Call the following code in PowerShell to get the history refresh data in Dataset.

Attached:

GET https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetId}/refreshes

Code: Invoke-PowerBIRestMethod -Url 'groups/1855d3a1-caf7***/datasets/af2795e5-bd9a-4***/refreshes' -Method Get

You can also add the $top parameter after it to get the desired history refresh record.

Attached：

$top: The requested number of entries in the refresh history. If not provided, the default is all available entries.

Code: Invoke-PowerBIRestMethod -Url 'groups/1855d3a1-ca***/datasets/af2795e5-***/refreshes?$top=1' -Method Get

The above is a complete step-by-step procedure detailing how to get the history of refresh records for the specified data set in the specified workspace.

Part2: Get the historical refresh records of all datasets in the current organization workspace.

Going further, if you want to get the history refresh records of a given dataset you only need to get its corresponding dataset ID, and the rest of the code remains unchanged. Therefore, we set the line of code to get the dataset ID as a parameter, and get the dataset ID and then the corresponding refresh record in a circular way.

Part3. Get the historical refresh records of all datasets in the workspaces within the organization.

The logic is the same as the part2, and we set the line of code to get the workspace ID as a parameter and get it sequentially through the loop. Finally, the whole operation flow is: get workspace A ID, query all its dataset's history refresh records; get workspace B ID, query all its dataset's history refresh records. After iterating through all workspaces, we get the final query result.

Code: Connect-PowerBIServiceAccount

$Workspaces = Get-PowerBIWorkspace -Scope Organization

foreach($workspace in $Workspaces)

{

$DataSets = Get-PowerBIDataset -WorkspaceId $workspace.Id | where {$_.isRefreshable -eq $true}

foreach($dataset in $DataSets)

{

$URI = "groups/" + $workspace.id + "/datasets/" + $dataset.id + "/refreshes"

$Results = Invoke-PowerBIRestMethod -Url $URI -Method Get

Write-Host $Results

}}

Author: Henry Kong

Reviewer: Ula Huang, Kerry Wang