Compartir a través de

Azure Resource Graph sample queries for Azure Policy

This page is a collection of Azure Resource Graph sample queries for Azure Policy.

Azure Policy

Compliance by policy assignment

Provides compliance state, compliance percentage, and counts of resources for each Azure Policy assignment.

PolicyResources
| where type =~ 'Microsoft.PolicyInsights/PolicyStates'
| extend complianceState = tostring(properties.complianceState)
| extend
  resourceId = tostring(properties.resourceId),
  policyAssignmentId = tostring(properties.policyAssignmentId),
  policyAssignmentScope = tostring(properties.policyAssignmentScope),
  policyAssignmentName = tostring(properties.policyAssignmentName),
  policyDefinitionId = tostring(properties.policyDefinitionId),
  policyDefinitionReferenceId = tostring(properties.policyDefinitionReferenceId),
  stateWeight = iff(complianceState == 'NonCompliant', int(300), iff(complianceState == 'Compliant', int(200), iff(complianceState == 'Conflict', int(100), iff(complianceState == 'Exempt', int(50), int(0)))))
| summarize max(stateWeight) by resourceId, policyAssignmentId, policyAssignmentScope, policyAssignmentName
| summarize counts = count() by policyAssignmentId, policyAssignmentScope, max_stateWeight, policyAssignmentName
| summarize overallStateWeight = max(max_stateWeight),
nonCompliantCount = sumif(counts, max_stateWeight == 300),
compliantCount = sumif(counts, max_stateWeight == 200),
conflictCount = sumif(counts, max_stateWeight == 100),
exemptCount = sumif(counts, max_stateWeight == 50) by policyAssignmentId, policyAssignmentScope, policyAssignmentName
| extend totalResources = todouble(nonCompliantCount + compliantCount + conflictCount + exemptCount)
| extend compliancePercentage = iff(totalResources == 0, todouble(100), 100 * todouble(compliantCount + exemptCount) / totalResources)
| project policyAssignmentName, scope = policyAssignmentScope,
complianceState = iff(overallStateWeight == 300, 'noncompliant', iff(overallStateWeight == 200, 'compliant', iff(overallStateWeight == 100, 'conflict', iff(overallStateWeight == 50, 'exempt', 'notstarted')))),
compliancePercentage,
compliantCount,
nonCompliantCount,
conflictCount,
exemptCount

az graph query -q "PolicyResources | where type =~ 'Microsoft.PolicyInsights/PolicyStates' | extend complianceState = tostring(properties.complianceState) | extend resourceId = tostring(properties.resourceId), policyAssignmentId = tostring(properties.policyAssignmentId), policyAssignmentScope = tostring(properties.policyAssignmentScope), policyAssignmentName = tostring(properties.policyAssignmentName), policyDefinitionId = tostring(properties.policyDefinitionId), policyDefinitionReferenceId = tostring(properties.policyDefinitionReferenceId), stateWeight = iff(complianceState == 'NonCompliant', int(300), iff(complianceState == 'Compliant', int(200), iff(complianceState == 'Conflict', int(100), iff(complianceState == 'Exempt', int(50), int(0))))) | summarize max(stateWeight) by resourceId, policyAssignmentId, policyAssignmentScope, policyAssignmentName | summarize counts = count() by policyAssignmentId, policyAssignmentScope, max_stateWeight, policyAssignmentName | summarize overallStateWeight = max(max_stateWeight), nonCompliantCount = sumif(counts, max_stateWeight == 300), compliantCount = sumif(counts, max_stateWeight == 200), conflictCount = sumif(counts, max_stateWeight == 100), exemptCount = sumif(counts, max_stateWeight == 50) by policyAssignmentId, policyAssignmentScope, policyAssignmentName | extend totalResources = todouble(nonCompliantCount + compliantCount + conflictCount + exemptCount) | extend compliancePercentage = iff(totalResources == 0, todouble(100), 100 * todouble(compliantCount + exemptCount) / totalResources) | project policyAssignmentName, scope = policyAssignmentScope, complianceState = iff(overallStateWeight == 300, 'noncompliant', iff(overallStateWeight == 200, 'compliant', iff(overallStateWeight == 100, 'conflict', iff(overallStateWeight == 50, 'exempt', 'notstarted')))), compliancePercentage, compliantCount, nonCompliantCount, conflictCount, exemptCount"

Compliance by resource type

Provides compliance state, compliance percentage, and counts of resources for each resource type.

PolicyResources
| where type =~ 'Microsoft.PolicyInsights/PolicyStates'
| extend complianceState = tostring(properties.complianceState)
| extend
  resourceId = tostring(properties.resourceId),
  resourceType = tolower(tostring(properties.resourceType)),
  policyAssignmentId = tostring(properties.policyAssignmentId),
  policyDefinitionId = tostring(properties.policyDefinitionId),
  policyDefinitionReferenceId = tostring(properties.policyDefinitionReferenceId),
  stateWeight = iff(complianceState == 'NonCompliant', int(300), iff(complianceState == 'Compliant', int(200), iff(complianceState == 'Conflict', int(100), iff(complianceState == 'Exempt', int(50), int(0)))))
| summarize max(stateWeight) by resourceId, resourceType
| summarize counts = count() by resourceType, max_stateWeight
| summarize overallStateWeight = max(max_stateWeight),
nonCompliantCount = sumif(counts, max_stateWeight == 300),
compliantCount = sumif(counts, max_stateWeight == 200),
conflictCount = sumif(counts, max_stateWeight == 100),
exemptCount = sumif(counts, max_stateWeight == 50) by resourceType
| extend totalResources = todouble(nonCompliantCount + compliantCount + conflictCount + exemptCount)
| extend compliancePercentage = iff(totalResources == 0, todouble(100), 100 * todouble(compliantCount + exemptCount) / totalResources)
| project resourceType,
overAllComplianceState = iff(overallStateWeight == 300, 'noncompliant', iff(overallStateWeight == 200, 'compliant', iff(overallStateWeight == 100, 'conflict', iff(overallStateWeight == 50, 'exempt', 'notstarted')))),
compliancePercentage,
compliantCount,
nonCompliantCount,
conflictCount,
exemptCount

az graph query -q "PolicyResources | where type =~ 'Microsoft.PolicyInsights/PolicyStates' | extend complianceState = tostring(properties.complianceState) | extend resourceId = tostring(properties.resourceId), resourceType = tolower(tostring(properties.resourceType)), policyAssignmentId = tostring(properties.policyAssignmentId), policyDefinitionId = tostring(properties.policyDefinitionId), policyDefinitionReferenceId = tostring(properties.policyDefinitionReferenceId), stateWeight = iff(complianceState == 'NonCompliant', int(300), iff(complianceState == 'Compliant', int(200), iff(complianceState == 'Conflict', int(100), iff(complianceState == 'Exempt', int(50), int(0))))) | summarize max(stateWeight) by resourceId, resourceType | summarize counts = count() by resourceType, max_stateWeight | summarize overallStateWeight = max(max_stateWeight), nonCompliantCount = sumif(counts, max_stateWeight == 300), compliantCount = sumif(counts, max_stateWeight == 200), conflictCount = sumif(counts, max_stateWeight == 100), exemptCount = sumif(counts, max_stateWeight == 50) by resourceType | extend totalResources = todouble(nonCompliantCount + compliantCount + conflictCount + exemptCount) | extend compliancePercentage = iff(totalResources == 0, todouble(100), 100 * todouble(compliantCount + exemptCount) / totalResources) | project resourceType, overAllComplianceState = iff(overallStateWeight == 300, 'noncompliant', iff(overallStateWeight == 200, 'compliant', iff(overallStateWeight == 100, 'conflict', iff(overallStateWeight == 50, 'exempt', 'notstarted')))), compliancePercentage, compliantCount, nonCompliantCount, conflictCount, exemptCount"

List all non-compliant resources

Provides a list of all resources types that are in a NonCompliant state.

PolicyResources
| where type == 'microsoft.policyinsights/policystates'
| where properties.complianceState == 'NonCompliant'
| extend NonCompliantResourceId = properties.resourceId, PolicyAssignmentName = properties.policyAssignmentName

az graph query -q "PolicyResources | where type == 'microsoft.policyinsights/policystates' | where properties.complianceState == 'NonCompliant' | extend NonCompliantResourceId = properties.resourceId, PolicyAssignmentName = properties.policyAssignmentName"

Summarize resource compliance by state

Details the number of resources in each compliance state.

PolicyResources
| where type == 'microsoft.policyinsights/policystates'
| extend complianceState = tostring(properties.complianceState)
| summarize count() by complianceState

az graph query -q "PolicyResources | where type == 'microsoft.policyinsights/policystates' | extend complianceState = tostring(properties.complianceState) | summarize count() by complianceState"

Summarize resource compliance by state per location

Details the number of resources in each compliance state per location.

PolicyResources
| where type == 'microsoft.policyinsights/policystates'
| extend complianceState = tostring(properties.complianceState)
| extend resourceLocation = tostring(properties.resourceLocation)
| summarize count() by resourceLocation, complianceState

az graph query -q "PolicyResources | where type == 'microsoft.policyinsights/policystates' | extend complianceState = tostring(properties.complianceState) | extend resourceLocation = tostring(properties.resourceLocation) | summarize count() by resourceLocation, complianceState"

Compliance states for all policy assignments

This query gets the compliance state for all policies. This differs from the “Compliance states by policy assignment” in that it will still display the results for policies that have no resources in the select scope. Said policies will show as “compliant”.

Note

To mimic the UX behavior when selecting a management group scope you should set the Authorization Scope Filter to “atScopeAndAbove” overwise use “AtScopeAboveAndBelow”. See here for more information, see Understand the query language - Azure Resource Graph. The purpose of this is to limit the number of returned results when selecting management group scopes.

policyResources 
    | where type =~ 'Microsoft.Authorization/PolicyAssignments' 
    | project assignmentId = tolower(id), policyDefinitionId = tolower(properties.policyDefinitionId), policyAssignmentScope = tolower(properties.scope) 
    | join kind = leftouter  ( 
        policyresources 
        | where type == 'microsoft.authorization/policysetdefinitions' 
        | project policySetDefinitionId = tolower(id), policyDefinitions = properties.policyDefinitions 
        | mv-expand policyDefinitions limit 2000 
        | project policySetDefinitionId, policyDefinitionId = 
policyDefinitions.policyDefinitionId, 
policyDefinitionReferenceId = tolower(policyDefinitions.policyDefinitionReferenceId) 
    ) on $left.policyDefinitionId == $right.policySetDefinitionId 
    | project assignmentId, policyDefinitionId = coalesce(policyDefinitionId1, policyDefinitionId), policySetDefinitionId, policyDefinitionReferenceId 
    | join kind = leftouter ( 
        policyResources  
        | where type =~ 'Microsoft.PolicyInsights/PolicyStates' 
        | project assignmentId = tolower(properties.policyAssignmentId), 
            policyDefinitionId = tolower(properties.policyDefinitionId),  
            policySetDefinitionId = tolower(properties.policySetDefinitionId), 
            policyDefinitionReferenceId = tolower(properties.policyDefinitionReferenceId), 
            stateWeight = toint(properties.stateWeight) 
        | summarize max_stateWeight = max(stateWeight) by assignmentId, policyDefinitionId, policySetDefinitionId, policyDefinitionReferenceId 
        | project assignmentId, policyDefinitionId, policySetDefinitionId, policyDefinitionReferenceId, complianceState = case(max_stateWeight == 300, 'noncompliant', max_stateWeight == 200, 'compliant', max_stateWeight == 150, 'error', max_stateWeight == 100, 'conflict', max_stateWeight == 50, 'exempt', max_stateWeight == 10, 'unknown', max_stateWeight == 0, 'notapplicable', 'notapplicable') 
    ) on assignmentId and policySetDefinitionId and policyDefinitionId and policyDefinitionReferenceId 
    | project complianceState = coalesce(complianceState, 'compliant') 
    | summarize complianceCount = count() by complianceState

The logic for the query is:

  1. Pull all assignments
  2. Left outer join initiative (policy set) assignments with their definition. This is a left outer to preserve any policy assignments
  3. Expand the initiative so you have a record for every policy in the initiative
  4. Join the expanded records with the policy states

This will give you the count of compliance states for all assigned policies, even if the policy does not have any resources in the scope or is part of an initiative.

To achieve a similar result using the Azure Policy REST APIs, you'll need to call Policy Assignments - List and Policy Set Definitions - List at the target scope and all parent scopes. You can then apply logic similar to the query to join this data with the output of Policy States - List Query Results For Subscription, scoped to the subscription that you're inspecting.

Compliance states for all initiative assignments

This query gets the compliance state for all initiatives. This differs from the “Compliance states by policy assignment” in that it will still display the results for initiatives that have no resources in the select scope. Said initiatives will show as “compliant”.

Note

To mimic the UX behavior when selecting a management group scope you should set the Authorization Scope Filter to “atScopeAndAbove” overwise use “AtScopeAboveAndBelow”. See here for more information, see Understand the query language - Azure Resource Graph. The purpose of this is to limit the number of returned results when selecting management group scopes.

policyResources 
    | where type =~ 'Microsoft.Authorization/PolicyAssignments' 
    | project assignmentId = tolower(id), definitionId = tolower(properties.policyDefinitionId), policyAssignmentScope = tolower(properties.scope) 
    | join kind = inner ( 
        policyresources 
        | where type == 'microsoft.authorization/policysetdefinitions' 
        | project definitionId = tolower(id) 
    ) on $left.definitionId == $right.definitionId 
    | project assignmentId 
    | join kind = leftouter ( 
        policyResources  
        | where type =~ 'Microsoft.PolicyInsights/PolicyStates' 
        | where properties.policySetDefinitionId != "" 
        | project assignmentId = tolower(properties.policyAssignmentId), stateWeight = toint(properties.stateWeight) 
        | summarize max_stateWeight = max(stateWeight) by assignmentId 
        | project assignmentId, complianceState = case(max_stateWeight == 300, 'noncompliant', max_stateWeight == 200, 'compliant', max_stateWeight == 150, 'error', max_stateWeight == 100, 'conflict', max_stateWeight == 50, 'exempt', max_stateWeight == 10, 'unknown', max_stateWeight == 0, 'notapplicable', 'notapplicable') 
    ) on $left.assignmentId == $right.assignmentId 
    | project complianceState = coalesce(complianceState, 'compliant') 
    | summarize complianceCount = count() by complianceState

The logic for the query is:

  1. Pull all assignments
  2. Inner join initiative assignments with their definition. This is an inner join to restrict the returned data to initiative assignments
  3. Join the initiative records with the policy states

This will give you the count of compliance states for all assigned initiatives, even if the policies in the initiative do not have any resources in the scope.

To achieve a similar result using the Azure Policy REST APIs, you'll need to call Policy Assignments - List and Policy Set Definitions - List at the target scope and all parent scopes. You can then apply logic similar to the query to join this data with the output of Policy States - List Query Results For Subscription, scoped to the subscription that you're inspecting.

Azure Policy exemptions

Policy exemptions per assignment

Lists the number of exemptions for each assignment.

PolicyResources
| where type == 'microsoft.authorization/policyexemptions'
| summarize count() by tostring(properties.policyAssignmentId)

Use the --management-groups parameter with an Azure management group ID or tenant ID. In this example, the tenantid variable stores the tenant ID.

tenantid="$(az account show --query tenantId --output tsv)"
az graph query -q "policyresources | where type == 'microsoft.authorization/policyexemptions' | summarize count() by tostring(properties.policyAssignmentId)" --management-groups $tenantid

Policy exemptions that expire within 90 days

Lists the name (exemption ID), display name, and expiration date.

PolicyResources
| where type == 'microsoft.authorization/policyexemptions'
| extend expiresOnC = todatetime(properties.expiresOn)
| where isnotnull(expiresOnC)
| where expiresOnC >= now() and expiresOnC < now(+90d)
| project name, properties.displayName, expiresOnC

az graph query -q "PolicyResources | where type == 'microsoft.authorization/policyexemptions' | extend expiresOnC = todatetime(properties.expiresOn) | where isnotnull(expiresOnC) | where expiresOnC >= now() and expiresOnC < now(+90d) | project name, properties.displayName, expiresOnC"

Next steps

  • Last updated on