You can able to get the list of all non-complaint Virtual Machines using the below PowerShell script:
param (
$SUBSCRIPTIONID,
$AUTOMATIONACCOUNTNAME,
$RESOURCEGROUPNAME,
$WORKSPACE
)
#region - Generate a bearer token
$azureRmProfile = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile
$currentAzureContext = Get-AzureRmContext
$profileClient = New-Object Microsoft.Azure.Commands.ResourceManager.Common.RMProfileClient($azureRmProfile)
$token = $profileClient.AcquireAccessToken($currentAzureContext.Subscription.TenantId)
#endregion
$Query = @"
{
"top": 1000000000,
"query": "Heartbeat\n| where TimeGenerated>ago(12h) and OSType==\"Linux\" and notempty(Computer) | where ComputerEnvironment=~\"Azure\"\n| summarize arg_max(TimeGenerated, Solutions, Computer, ResourceId, ComputerEnvironment, VMUUID) by SourceComputerId\n| where Solutions has \"updates\" \n| extend vmuuId=VMUUID, azureResourceId=ResourceId, osType=1,\nenvironment=iff(ComputerEnvironment=~\"Azure\", 1, 2),\nscopedToUpdatesSolution=true, lastUpdateAgentSeenTime=\"\"\n| join kind=leftouter (Update\n| where TimeGenerated>ago(5h) and OSType==\"Linux\" and SourceComputerId in ((Heartbeat\n| where TimeGenerated>ago(12h) and OSType==\"Linux\" and notempty(Computer)\n| summarize arg_max(TimeGenerated, Solutions) by SourceComputerId\n| where Solutions has \"updates\" | distinct SourceComputerId)) | where ComputerEnvironment=~\"Azure\"\n| summarize hint.strategy=partitioned arg_max(TimeGenerated, UpdateState, Classification, Product, Computer, ComputerEnvironment) by SourceComputerId, Product, ProductArch \n| summarize Computer=any(Computer), ComputerEnvironment=any(ComputerEnvironment), missingCriticalUpdatesCount=countif(Classification has \"Critical\" and UpdateState=~\"Needed\"),\nmissingSecurityUpdatesCount=countif(Classification has \"Security\" and UpdateState=~\"Needed\"),\nmissingOtherUpdatesCount=countif(Classification !has \"Critical\" and Classification !has \"Security\" and UpdateState=~\"Needed\"),\nlastAssessedTime=max(TimeGenerated), lastUpdateAgentSeenTime=\"\" by SourceComputerId\n| extend compliance=iff(missingCriticalUpdatesCount > 0 or missingSecurityUpdatesCount > 0, 2, 1)\n| extend ComplianceOrder=iff(missingCriticalUpdatesCount > 0 or missingSecurityUpdatesCount > 0 or missingOtherUpdatesCount > 0, 1, 3)) on SourceComputerId\n| project id=SourceComputerId, displayName=Computer ,sourceComputerId=SourceComputerId, azureResourceId, scopedToUpdatesSolution=true,\nmissingCriticalUpdatesCount=coalesce(missingCriticalUpdatesCount, -1), missingSecurityUpdatesCount=coalesce(missingSecurityUpdatesCount, -1), missingOtherUpdatesCount=coalesce(missingOtherUpdatesCount, -1), compliance=coalesce(compliance, 4), lastAssessedTime, lastUpdateAgentSeenTime, osType=1, environment=iff(ComputerEnvironment=~\"Azure\", 1, 2), ComplianceOrder=coalesce(ComplianceOrder, 2)\n | where compliance in (2) | union(Heartbeat\n| where TimeGenerated>ago(12h) and OSType=~\"Windows\" and notempty(Computer) | where ComputerEnvironment=~\"Azure\"\n| summarize arg_max(TimeGenerated, Solutions, Computer, ResourceId, ComputerEnvironment, VMUUID) by SourceComputerId\n| where Solutions has \"updates\" \n| extend vmuuId=VMUUID, azureResourceId=ResourceId, osType=2,\nenvironment=iff(ComputerEnvironment=~\"Azure\", 1, 2),\nscopedToUpdatesSolution=true, lastUpdateAgentSeenTime=\"\"\n| join kind=leftouter (Update\n| where TimeGenerated>ago(14h) and OSType!=\"Linux\" and SourceComputerId in ((Heartbeat\n| where TimeGenerated>ago(12h) and OSType=~\"Windows\" and notempty(Computer)\n| summarize arg_max(TimeGenerated, Solutions) by SourceComputerId\n| where Solutions has \"updates\" | distinct SourceComputerId)) | where ComputerEnvironment=~\"Azure\"\n| summarize hint.strategy=partitioned arg_max(TimeGenerated, UpdateState, Classification, Title, Optional, Approved, Computer, ComputerEnvironment) by Computer, SourceComputerId, UpdateID \n| summarize Computer=any(Computer), ComputerEnvironment=any(ComputerEnvironment), missingCriticalUpdatesCount=countif(Classification has \"Critical\" and UpdateState=~\"Needed\" and Approved!=false),\nmissingSecurityUpdatesCount=countif(Classification has \"Security\" and UpdateState=~\"Needed\" and Approved!=false),\nmissingOtherUpdatesCount=countif(Classification !has \"Critical\" and Classification !has \"Security\" and UpdateState=~\"Needed\" and Optional==false and Approved!=false),\nlastAssessedTime=max(TimeGenerated), lastUpdateAgentSeenTime=\"\" by SourceComputerId\n| extend compliance=iff(missingCriticalUpdatesCount > 0 or missingSecurityUpdatesCount > 0, 2, 1)\n| extend ComplianceOrder=iff(missingCriticalUpdatesCount > 0 or missingSecurityUpdatesCount > 0 or missingOtherUpdatesCount > 0, 1, 3)) on SourceComputerId\n| project id=SourceComputerId, displayName=Computer, sourceComputerId=SourceComputerId, azureResourceId, scopedToUpdatesSolution=true,\nmissingCriticalUpdatesCount=coalesce(missingCriticalUpdatesCount, -1), missingSecurityUpdatesCount=coalesce(missingSecurityUpdatesCount, -1), missingOtherUpdatesCount=coalesce(missingOtherUpdatesCount, -1), compliance=coalesce(compliance, 4), lastAssessedTime, lastUpdateAgentSeenTime, osType=2, environment=iff(ComputerEnvironment=~\"Azure\", 1, 2), ComplianceOrder=coalesce(ComplianceOrder, 2)\n | where compliance in (2)) | order by ComplianceOrder asc, missingCriticalUpdatesCount desc, missingSecurityUpdatesCount desc, missingOtherUpdatesCount desc, displayName asc"
}
"@
$result = Invoke-RestMethod -Uri "https://management.azure.com/subscriptions/$($SUBSCRIPTIONID)/resourcegroups/$($RESOURCEGROUPNAME)/microsoft.operationalinsights/workspaces/$($WORKSPACE)/query?api-version=2017-10-01&q_OrchestratorExtension.DataModels.Computer" -Headers @{
Authorization = "Bearer {0}" -f ($token.AccessToken)
} -Method Post -Body ($Query) -ContentType 'application/json'
$Collection = @()
$result.tables.rows | %{
$Collection += [pscustomobject]@{
VMName = $_[1]
CriticalUpdateMissing = $_[5]
SecurityUpdateMissing = $_[6]
}
}
$Collection
I hope this will help.
Back