KQL join query to extract compliance report from resource

Question

I am trying to get left outer join or join with extend or mv-expand on Graph API to list all non-compliant disk with specific path (non-comp reason). In other words, extracting fields from policy and fields from resource e.g.-

resources
| where type == "microsoft.compute/disks"  and subscriptionId == '3mmmmm333333####e35'
    | join kind=leftouter (policyresources
        | where type == "microsoft.policyinsights/policystates"
  

Some resource I am going through are https://www.youtube.com/watch?v=r_3Ydr6fCHQ and https://github.com/globalbao/azure-resource-graph/blob/master/README.md but the join statement to correlate resource query are not working with both outer or join. Maybe I need to narrow down to related item under the resource and resource policy? Azure Management Talk: Azure Resource Graph Zero to Hero - YouTube In this session, Microsoft Consultant Billy York will go over the basics of Azure Resource Graph, including how Kusto Query Language (KQL) is used and its li...

If anyone has similar report extraction code with leftouter join between policy with compute resource that would be helpful

KQL join query to extract compliance report from resource. To get some fields from resource and some from compliance related to resource

Answer 1

To achieve, KQL query to extract compliance disk resources:

By referring MSDoc, I've included the relevant part of the query and tried by changing the resource type "resourceType = "microsoft.compute/disks" , I was able to receive the expected results.

PolicyResources
| where type =~ 'Microsoft.PolicyInsights/PolicyStates'
| extend complianceState = tostring(properties.complianceState)
| extend
resourceId = tostring(properties.resourceId),
resourceType = "microsoft.compute/disks",
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

Output:

I've pulled up all the relevant fields here, but you can extend and project the fields you want based on your requirements.