Azure Resource Graph Query - List policy Compliance State and excluded scope i.e. not scope from policy assignment

Question

My goal is to create an Azure dashboard widget with a Compliance status of policy and also include the count of excludedscope i.e. notscopes from Policy assignment

+-------------------+-------+-----------+--------+---------------+------------+ For that I am using the Azure Resource Graph Query. I am able to list the policy states but how to join the excludedscope count also how to do cross-join of output coming from 2 different queries?

query 1

// Policy Compliance test
// Click the "Run query" command above to execute the query and see results.
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)))))
| where policyAssignmentName == 'StorageminimumTLS1_2'  
| 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)
complianceState = iff(overallStateWeight == 300, 'noncompliant', iff(overallStateWeight == 200, 'compliant', iff(overallStateWeight == 100, 'conflict', iff(overallStateWeight == 50, 'exempt', 'notstarted')))),
compliancePercentage,
compliantCount,
nonCompliantCount,
exemptCount

query 2

policyresources
| where type == "microsoft.authorization/policyassignments"
| where name == "StorageminimumTLS1_2"
| project excludedScopesCount = array_length(properties['notScopes'])

Answer 1

For that I am using the Azure Resource Graph Query. I am able to list the policy states but how to join the excludedscope count also how to do cross-join of output coming from 2 different queries?

Query 1 Result

Query 2 Result:

To join Query 1 and Query2 with the excludedscope count, you can use below Query.

PolicyResources
| where type contains  "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)))))
| where policyAssignmentName == 'CloudGov_ER_Network_Only'
| 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),
complianceState = iff(overallStateWeight == 300, 'noncompliant', iff(overallStateWeight == 200, 'compliant', iff(overallStateWeight == 100, 'conflict', iff(overallStateWeight == 50, 'exempt', 'notstarted')))),
compliantCount,
nonCompliantCount,
exemptCount
| extend dummy =1
| join kind=inner (
policyresources
| where type == "microsoft.authorization/policyassignments"
| where name == "CloudGov_ER_Network_Only"
| project excludedScopesCount = array_length(properties['notScopes'])
| extend dummy =1) on dummy
| project-away dummy,dummy1

Join Result with excludedscope count