Forum Discussion

sb991986's avatar
sb991986
Advocate I
5 years ago
Solved

Get Refresh History for all workspaces via API

Hi,   On my tenant, I'm Power BI administrator and Capacity administrator. But I am not administrator on each Workspace. I need to see all refresh duration of all datasets of my power BI (both pre...
Need Help
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    Hi sb991986

     

    Here is the powershell script that you can use in conjunction with the REST API to get the refresh history of all workspaces.

    Connect-PowerBIServiceAccount



$Workspaces = Get-PowerBIWorkspace



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"
#$OutFile = $ExportFolder + '\' + $workspace.Name + '-' + $dataset.Name + '.json'
$Results = Invoke-PowerBIRestMethod -Url $URI -Method Get | ConvertFrom-Json



foreach($result in $Results.value)
{
$errorDetails = $result.serviceExceptionJson | ConvertFrom-Json -ErrorAction SilentlyContinue



$row = New-Object psobject
$row | Add-Member -Name "Workspace" -Value $workspace.Name -MemberType NoteProperty
$row | Add-Member -Name "Dataset" -Value $dataset.Name -MemberType NoteProperty
$row | Add-Member -Name "refreshType" -Value $result.refreshType -MemberType NoteProperty
$row | Add-Member -Name "startTime" -Value $result.startTime -MemberType NoteProperty
$row | Add-Member -Name "endTime" -Value $result.endTime -MemberType NoteProperty
$row | Add-Member -Name "status" -Value $result.status -MemberType NoteProperty
$row | Add-Member -Name "errorCode" -Value $errorDetails.errorCode -MemberType NoteProperty
$row | Add-Member -Name "errorDescription" -Value $errorDetails.errorDescription -MemberType NoteProperty
Write-Host $row
}



}



}

    Here is the output:

    Hope it helps.😉

     

    Best Regards,
    Link

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

ibarrau's avatar
ibarrau
Super User
5 years ago

Hi, the only way to get all that data is being Power Bi Administrator. It's not necessary for you to be Workspace administrator. There are API Requests that onle the Administrator can do. You can use these two requests first:

Get all workspaces in the tenant:

https://docs.microsoft.com/en-us/rest/api/power-bi/admin/groups_getgroupsasadmin

Get all the datasets in a workspace

https://docs.microsoft.com/en-us/rest/api/power-bi/admin/datasets_getdatasetsingroupasadmin

Once you get all workspaces you can get all the datasets inside them. At the end you can use the get request of the historic refreshes for each dataset.

You will probably need to write a script. 

I hope this helps,

Anonymous's avatar
Anonymous
Not applicable
4 years ago

 

Install the following modules using Windows Powershell ISE if you are new to powershell. Above script works only after these two modules are installed.

 

You can also produce csv file by commenting out and adding following code 

#Write-Host $row
$row | Export-CSV C:\Users\Output\refresh.csv -Append