Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Powershell script to extract long running refreshes

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

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    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 (starring Powershell, PowerBi & Microsoft Flow)

     

     

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    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 (starring Powershell, PowerBi & Microsoft Flow)

     

     

    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.