Powershell script to extract long running refreshes

[Arvind07]

Hi, I want to extract long running refreshes (status = unknown) and set an alert for these long running refreshes, through powershell as I would like to send an alert to an alert tool we use.

As a calculation, compare the average duration of the dataset with the start time of the specific dataset, take average duration + start time, in conjunction with the current time of day, if the current runtime is higher than the normal average duration by more than 1hr, it needs to be looked at. I've identified the RestAPI to use but need guidance to follow through with this.

Is there anyone who can provide a code example or guidance on how to write a Powershell script that can return such result?

Guidance is highly appreciated. Thank you

[v-rzhou-msft · Accepted solution]

Hi @Arvind07 ,

As far as I know, we can set alert for Power BI refreshing failure by Power Shell.

I hope this code could help you.

$myCred = Get-Credential
Connect-PowerBIServiceAccount -Credential $myCred

$Workspaces = Get-PowerBIWorkspace
$ExportFile =  'C:\Users\emeazzo\Desktop\Scripts\PowerBI\PowerShell\RefreshHistory\PowerBiRefreshHistory.csv'
Remove-Item $ExportFile -Force -ErrorAction SilentlyContinue

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
            $row | Export-Csv -Path $ExportFile -Append -Delimiter ';' -NoTypeInformation
        }

    }

}

For reference:

Creating a Report and distributed Alerts for PowerBI Scheduled Refreshes in your organization (starr...

Best Regards,
Rico Zhou

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