I needed assistance on my KQL query with regards to Policy | Compliance data

Question

Hi,

I needed help for my Defender for Cloud workbook. The following KQL allows me to show the selected custom policies or initiatives (from Control parameter) in a table. Unfortunately, I am unable to figure out how to improve the KQL to show the data from compliance policy on the complianceState, resourceCompliance, nonCompliantResources etc.

Kindly refer to screenshot 1 which is the table generated by this KQL.

But, I can see the data fine in the Policy | Compliance on the Compliance state, Resource compliance, non-compliant resources and non-compliant policies.

So how can I pull the data from Policy | Compliance into this table, so that it shows exactly like how it being displayed in Policy | Compliance?

Thank you so much

PolicyResources
| where type in ("microsoft.authorization/policydefinitions", "microsoft.authorization/policysetdefinitions") // Policy Definitions and Initiatives (Policy Sets)
| extend 
    displayName = tostring(properties.displayName),
    Type = case(type == "microsoft.authorization/policydefinitions", "Policy", 
                type == "microsoft.authorization/policysetdefinitions", "Initiative", 
                "Unknown"), // Determine if it's a Policy or Initiative
    category = tostring(properties.metadata.category),
    complianceState = tostring(properties.complianceState), // Fetch compliance state
    resourceCompliance = strcat(tostring(properties.complianceSummary.compliantResources), " out of ", tostring(properties.complianceSummary.totalResources)), // Calculate resource compliance
    nonCompliantResources = tostring(properties.complianceSummary.nonCompliantResources), // Fetch non-compliant resources
    nonCompliantPolicies = tostring(properties.complianceSummary.nonCompliantPolicies) // Fetch non-compliant policies
| where displayName in ({Control}) // Filter by selected custom initiatives/policies
| project subscriptionId, displayName, Type, category, complianceState, resourceCompliance, nonCompliantResources, nonCompliantPolicies
| order by displayName asc

Answer 1

Hi @Eric Yong Sze Guan ,

Welcome to Microsoft Q&A Platform. Thank you for reaching out & hope you are doing well.

I request to make use of Resource Graph Explorer it's very easy way to get your requirement

Please follow below step by step process to fulfill your requirement.

• In the Azure portal, search for Resource Graph Explorer in the top search bar and open it.

Query in Below Link:

• Use the below link which has the query to retrieve Azure Policy data to get the Compliance and Non-Compliant resources of the Initiatives Script Selection: Compliance by policy assignment

Refer: https://learn.microsoft.com/en-us/azure/governance/policy/samples/resource-graph-samples?tabs=azure-cli

Below is the output and Artifacts

Feel free to reach out if you have any further questions or need additional information—I’m happy to assist!

Please provide your valuable comments

Please do not forget to "Accept the answer” and “upvote it” wherever the information provided helps you, this can be beneficial to other community members.it would be greatly appreciated and helpful to others.