Azure Resource Graph Query - List policy state by subscription name

Question

My goal is to create an Azure dashboard widget with a list of subscriptions with their according policy states.

+-------------------+-------+-----------+--------+---------------+------------+
| Subscription name | Total | Compliant | Exempt | Non-compliant | Percentage |
+-------------------+-------+-----------+--------+---------------+------------+
| foo-subscription  |   300 |       270 |      0 |            30 | 0.9        |
| bar-subscription  |   100 |        80 |      0 |            20 | 0.8        |
+-------------------+-------+-----------+--------+---------------+------------+

For that I am using the Azure Resource Graph Query. I am able to list the policy states related to the subscription-IDs. But not related to the subscription names.

This query

policyresources
| extend complianceState=tostring(properties['complianceState']), resourceId=tostring(properties['resourceId'])
| project subscriptionId, complianceState, resourceId
| summarize complianceStates=make_list(complianceState) by subscriptionId, resourceId
| summarize Total = count()
, Compliant = countif((complianceStates notcontains "NonCompliant") and (complianceStates contains "Compliant"))
, Exempt = countif((complianceStates notcontains "NonCompliant") and (complianceStates notcontains "Compliant") and (complianceStates contains "Exempt"))
, NonCompliant = countif (complianceStates contains "NonCompliant")
by subscriptionId
| extend OverallCompliancePerc = round(toreal(Compliant + Exempt) / toreal(Total), 2)
| order by OverallCompliancePerc desc

leads to

+--------------------+-------+-----------+--------+---------------+------------+
|  Subscription-ID   | Total | Compliant | Exempt | Non-compliant | Percentage |
+--------------------+-------+-----------+--------+---------------+------------+
| b4757628-9b24-447a |   300 |       270 |      0 |            30 | 0.9        |
| 86fa64ae-6c30-4157 |   100 |        80 |      0 |            20 | 0.8        |
+--------------------+-------+-----------+--------+---------------+------------+

The kusto language allows to join tables. However, the kusto language allows the join only for Resources and ResourceContainer tables. Not for Policy resources.

Is it possible to create a table related to the subscription name?

score 2 · Accepted Answer · answered Feb 04 '21 at 15:15

This is a great query and I wanted to thank you for sharing it.

If you run the query in the Azure Resource Graph Explorer, the results appear, there is a slider switch on the far above the results header titled "Formatted results", switch to On, that will replace SubscriptionId with the Subscription Name.

There is the union approach but I had difficulty and it was slow. Here is an example:

    Resources
    | summarize resourceCount=count() by subscriptionId
    | join (ResourceContainers | where type=='microsoft.resources/subscriptions' | project SubName=name, subscriptionId) on subscriptionId
    | project-away subscriptionId, subscriptionId1

If running from PowerShell, this is the method I use for resolving subscriptionId to SubscriptionName:

# Create a Hash Table for lookups of SubscriptionName by subscriptionId
$LkUpSubs = @{}
For ($sub in (Get-AzSubscription)) {
    $LkUpSubs.Add($sub.Id, $sub.Name)
}

// In the Kusto Query, Add and include SubscriptionName in the | project statement
| extend SubscriptionName = ''

# Run Search-AzGraph and assign output to a variable like $results
# Enrich the results
ForEach ($rec in $results) {
    $rec.SubscriptionName = $LkUpSub[$rec.subscriptionId]
}

Here is a working example based on your query:

$LkUpSubs = @{}
ForEach ($sub in (Get-AzSubscription)) {
    $LkUpSubs.Add($sub.Id, $sub.Name)
}

$Query = "policyresources | where tolower(properties.policyAssignmentName) != 'securitycenterbuiltIn'
| extend complianceState=tostring(properties['complianceState']), resourceId=tostring(properties['resourceId'])
| project subscriptionId, complianceState, resourceId
| summarize complianceStates=make_list(complianceState) by subscriptionId, resourceId
| summarize Total = count()
  , Compliant = countif((complianceStates notcontains 'NonCompliant') and (complianceStates contains 'Compliant'))
  , Exempt = countif((complianceStates notcontains 'NonCompliant') and (complianceStates notcontains 'Compliant') and (complianceStates contains 'Exempt'))
  , NonCompliant = countif (complianceStates contains 'NonCompliant') by subscriptionId
| extend OverallCompliancePerc = round(toreal(Compliant + Exempt) / toreal(Total), 2)
| extend SubscriptionName = ''
| project SubscriptionName, subscriptionId, Total, Compliant, Exempt, NonCompliant, OverallCompliancePerc
| order by OverallCompliancePerc desc
"
$Results = Search-AzGraph -Query $Query -First 5000

ForEach ($rec in $Results) {
    $rec.SubscriptionName = $LkUpSubs[$rec.subscriptionId]
}

$TimeStamp = (Get-Date -Format 'yyyy-MMdd-HHmm')
$FilePath = ('C:\Temp\ComplianceReports\ResourceCompliance_CountsBySubscription_{0}.csv' -f $TimeStamp)
$Results |
    Sort-Object -Property SubscriptionName | 
    Select-Object -Property SubscriptionName, Total, Compliant, NonCompliant, OverallCompliancePerc |  
    Export-Csv -Path $FilePath -Encoding UTF8 -Delimiter ',' -NoTypeInformation -Force

Best of luck

Thanks for the answer! The simple slider has solved it! When updating the tile the formatting is taken over to the dashboard. — mister.elastic, Feb 16 '21 at 15:01

score 0 · Answer 2 · edited Feb 11 '21 at 18:45

Great query, you saved me a lot of time. I have tweaked a bit your query trying to get just the last compliance results to be as close as possible to the portal dashboard, but faster.

policyresources
| extend complianceState=tostring(properties['complianceState']), resourceId=tostring(properties['resourceId']),timestamp=todatetime(tostring(properties['timestamp'])),policyDefinitionName=tostring(properties['policyDefinitionName'])
| where timestamp > ago(1d)
| project subscriptionId, complianceState, resourceId, timestamp,policyDefinitionName
| summarize max(timestamp) by subscriptionId, resourceId,complianceState,policyDefinitionName
| summarize complianceStates=make_list(complianceState) by subscriptionId, resourceId
| summarize Total = count()
, Compliant = countif((complianceStates notcontains "NonCompliant") and (complianceStates contains "Compliant"))
, Exempt = countif((complianceStates notcontains "NonCompliant") and (complianceStates notcontains "Compliant") and (complianceStates contains "Exempt"))
, ['Non-Compliant'] = countif (complianceStates contains "NonCompliant")
by subscriptionId
| extend ['Compliance %'] = round(toreal(Compliant + Exempt)*100 / toreal(Total), 2)
| order by ['Compliance %'] desc

Nice tweaks! However, my problem is still not solved, unfortunately. — mister.elastic, Feb 16 '21 at 14:58
If you just need to show the subscription name, just turn on "Formatted results". As you said, there is no way to join the "policyresources" table with any other one, at least in a Resource Graph query. — Jaime Palazuelos, Feb 17 '21 at 15:39

Azure Resource Graph Query - List policy state by subscription name

2 Answers2