I was asked a question awhile back in our internal communities about how to generate a report of Azure Policies specifically non compliant states but filtered for only running Azure virtual machines. We wanted to exclude Azure VMs deallocated in the report.
To begin with this we look at a table called policyresources that includes a policystates
The query here helps show the Azure policies for VMs that are non compliant and are not Server Vulnerability Assessments.
Some key things to note:
Line 7, we want to make sure the VM name we are extracting is uppercase, this will be important later when we join and match tables between the VM names, the running state will have the VM name in uppercase.
policyresources
| where type == "microsoft.policyinsights/policystates"
| where properties.resourceType == "Microsoft.Compute/virtualMachines"
| where properties.complianceState == "NonCompliant"
| extend resourceId = properties.resourceId
| extend vmName = toupper(tostring(split(resourceId, "/")[8]))
| extend timeGenerated = properties.timestamp
| extend policyDescription = properties.policyDefinitionReferenceId
| where policyDescription != "servervulnerabilityassessment"
| extend policyDefId = tostring(split(properties.policyDefinitionId, "/")[4])
| project vmName, resourceGroup, policyDescription, policyDefId, timeGenerated, resourceId
Upon initial research and testing the following Azure Resource Graph Table and query can find the VM power status and filter on it for running VMs in Azure.
resources
| where type == "microsoft.compute/virtualmachines"
| extend powerStatus = properties.extended.instanceView.powerState.displayStatus
| where powerStatus == "VM running"
| project name, powerStatus, id
Previously when we would join these tables we would get the following error
| join kind = leftouter (resources) on $left.vmName == $right.name
Details on Azure Resource Graph tables can be found here:
The key here is the Can join other tables? column, some tables are not supported for Joins, this can limit oppurtunties to enrich reports like SubId column with SubName in another table, or filter if VM running include only in report.
With some of latest updates the policyresources table can now be joined. The following query can now be used with an UnsupportedCrossTableScenario
policyresources
| where type == "microsoft.policyinsights/policystates"
| where properties.resourceType == "Microsoft.Compute/virtualMachines"
| where properties.complianceState == "NonCompliant"
| extend resourceId = properties.resourceId
| extend vmName = toupper(tostring(split(resourceId, "/")[8]))
| extend timeGenerated = properties.timestamp
| extend policyDescription = properties.policyDefinitionReferenceId
| where policyDescription != "servervulnerabilityassessment"
| extend policyDefId = tostring(split(properties.policyDefinitionId, "/")[4])
| project vmName, resourceGroup, policyDescription, policyDefId, timeGenerated, resourceId
| join kind = leftouter (resources) on $left.vmName == $right.name
| extend powerStatus = properties.extended.instanceView.powerState.displayStatus
| where powerStatus == "VM running"
Armed with cross table scenarios in Azure Resource Graph what reports and dashboard workbooks will you create, enrich data upon, and filter off of ?
While writing queries if you do encounter a table that doesn’t support cross table scenario for join; open a Azure Feedback request like the this one that enabled policy state on Azure VMs running scenario.