I am working on the following PowerShell script for Azure and I need Invoke-AzOperationalInsightsQuery to complete 100 seconds or less. How can I make this query return results faster?

$startTime = (Get-Date).ToUniversalTime().AddDays(-1).Date
    $endTime = $startTime.AddDays(1)
    
    # $query = "Union Event, Syslog"
    $query = "AzureNetworkAnalytics_CL | where SubType_s == 'FlowLog'"
    # $query = "AzureDiagnostics | where Category == "ApplicationGatewayFirewallLog"
    
    # $table = "Syslog"
    # $table = "Event"
    # $table = "AzureDiagnostics"
    # $table = "AzureNetworkAnalytics_CL"
    
    # [System.Net.ServicePointManager]::MaxServicePointIdleTime = 180000
    
    # $results = Invoke-AzOperationalInsightsQuery -WorkspaceId $workspaceID -Query $table
    # $results = Invoke-AzOperationalInsightsQuery -WorkspaceId $workspaceID -Query $table -Timespan (New-TimeSpan -Hours 2)
    # $results = Invoke-AzOperationalInsightsQuery -WorkspaceId $workspaceID -Query $table -Timespan (New-TimeSpan -Hours 24)
    $results = Invoke-AzOperationalInsightsQuery -WorkspaceId $workspaceID -Query $query -Timespan (New-TimeSpan -Start $startTime -End $endTime)
    # $results = Invoke-AzOperationalInsightsQuery -WorkspaceId $WorkspaceID -Query $Query -ExpandProperty Results
    
    # $csvFileName = "$((Get-Date).Year)$((Get-Date).Month.ToString('00'))$((Get-Date).Day.ToString('00'))$((Get-Date).Hour.ToString('00'))$((Get-Date).Minute.ToString('00'))$((Get-Date).Second.ToString('00'))-$table"
    $csvFileName = "$((Get-Date).Year)$((Get-Date).Month.ToString('00'))$((Get-Date).Day.ToString('00'))$((Get-Date).Hour.ToString('00'))-$table"
    
    $Results.Results | Export-Csv -Path "$csvFileName.csv"
    $Results.Results | ConvertTo-Json | Out-File -FilePath "$csvFileName.json"
    
    $compress = @{
        LiteralPath= ".\$csvFileName.csv",".\$csvFileName.json"
        # CompressionLevel = "Fastest"
        DestinationPath = ".\$csvFileName.zip"
    }
    Compress-Archive @compress -Force

CodePudding user response：

I need Invoke-AzOperationalInsightsQuery to complete 100 seconds or less. How can I make this query return results faster?

Usually, based on query complexity it will take time with respect to the query.

Invoke-AzOperationalInsightsQuery has a parameter called -wait you can use this to increase the time to avoid timeout.

The Invoke-AzOperationalInsightsQuery use .NET HttpClient send request and the default timeout has 100 sec.

To reduce the result time of Invoke-AzOperationalInsightsQuery you have to write the query efficiently. or you can use -wait parameter to complete the result.

$queryResults = Invoke-AzOperationalInsightsQuery -WorkspaceId "<Work space ID>" -Query "union * | take 10" -Timespan (New-TimeSpan -Hours 24) -wait 150
$queryResults.Results

MS-DOC for optimize the Query to reduce the execution time