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 premium & non premium), even if I'm not administrator on all workspaces.

 

I tried to get history refresh using the API "Datasets - Get Refresh History In Group", but it seems that I can not get history of datasets refresh  that I'm not the Workspace Administrator.

 

Is there a way to get all history refresh without being workspace Administrator? Because I'm Power BI administrator and Capacity administrator on my tenant. 

 

Thanks for your feedback

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.

6 Replies

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Community Support
    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

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Community Support
    5 years ago

    Hi sb991986

     

    Is your issue solved?

    If the issue has been solved, please adopt your solution to help others.

    Thanks! ğŸ˜‰

     

    Best Regards,
    Link

     

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

    • wojciech's avatar
      wojciech
      Helper II
      3 years ago

      Hi,

       

      How did you resolve it?

       

      I have the same issue, I can only get refresh history for workspaces I have access to, although I'm PBI Administrator.

       

      Thank you,

       

      WJ