Azure update management generate patch status report in a csv format

Question

I'm creating a Azure Automation runbook to generate a report on the patch status of the virtual machines under a management group.

The query used is as below

union Update , workspace('xxxx').Operation,workspace('yyyy').Operation
| where TimeGenerated > ago(10d) | where Classification in ("Security Updates", "Critical Updates", "Critical and security updates") and ResourceType == "virtualMachines" | summarize updates=makeset(Title) by Computer,Classification, UpdateState,Product, PublishedDate, MSRCSeverity | order by UpdateState

$result = Invoke-AzOperationalInsightsQuery -WorkspaceId $WorkspaceID -Query $query

here I need to query log analytics workspace from different subscriptions under the same management group.. Run As Account has RBAC set to "Log Analytics Reader" at management group level. But the query results is empty record set, Same query fetches records when its executed on the Log Analytics workspace directly.

Any guidance on what I'm missing here will be a great help. Thank you

Joy Wang-MSFT Joy Wang-MSFT · Accepted Answer · 2021-03-03T08:57:19

This command Invoke-AzOperationalInsightsQuery can just do operations against one subscription, so in your case, you need to use a loop to set the subscription with Set-AzContext -Subscription <subscription-id>, to get all the subscriptions your RunAs account can access, use Get-AzSubscription.

Sample:

$connectionName = "AzureRunAsConnection"
try
{
    # Get the connection "AzureRunAsConnection "
    $servicePrincipalConnection=Get-AutomationConnection -Name $connectionName         

    "Logging in to Azure..."
    Connect-AzAccount `
        -ServicePrincipal `
        -TenantId $servicePrincipalConnection.TenantId `
        -ApplicationId $servicePrincipalConnection.ApplicationId `
        -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint 
}
catch {
    if (!$servicePrincipalConnection)
    {
        $ErrorMessage = "Connection $connectionName not found."
        throw $ErrorMessage
    } else{
        Write-Error -Message $_.Exception
        throw $_.Exception
    }
}

$query = "xxxxxx"
$subs = Get-AzSubscription
foreach($sub in $subs){
    Set-AzContext -Subscription $sub.Id
    #do the things you want e.g. $result = Invoke-AzOperationalInsightsQuery -WorkspaceId $WorkspaceID -Query $query
    
}

This is just a sample, to make it work, also remember to loop the different $WorkspaceID in the script.

Azure update management generate patch status report in a csv format

1 Answers