KQL query with regards to policies

Joel Pangilinan 30

Hi, I'm trying to do a KQL query using Azure Resource Graph on policyresources and I'm having difficulty producing an output/csv that will show the following table below. I need to show all policies including policies under an initiative in the "Policy or Initiative Name" column. If you could please help crafting the KQL query needed for this.

AnuragSingh-MSFT 21,361 Reputation points

2023-02-21T06:12:44.6866667+00:00

@Joel Pangilinan Following up to check if you had a chance to review the answer below. Please let me know if you have any questions.

Please click Accept answer and Yes if the answer helped so that it can help others in the community looking for help on similar topics.

Luke Murray 11,076 MVP

Hi, Joel

Made a few changes to the, policy example here: Made a few changes to the, policy example here: https://video2.skills-academy.com/en-us/azure/governance/policy/samples/resource-graph-samples?tabs=azure-cli

User's image

This should be a great starting point:

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),
    definitionType = iif(properties.policyDefinitionType == 'Custom', 'Custom', 'Built-in'),
    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, policyDefinitionId, definitionType
| summarize counts = count() by policyAssignmentId, policyAssignmentScope, max_stateWeight, policyAssignmentName, policyDefinitionId, definitionType
| 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, policyDefinitionId, definitionType
| extend totalResources = todouble(nonCompliantCount + compliantCount + conflictCount + exemptCount)
| extend compliancePercentage = iff(totalResources == 0, todouble(100), 100 * todouble(compliantCount + exemptCount) / totalResources)
| project policyAssignmentId, policyAssignmentName, scope = policyAssignmentScope, policyDefinitionId, definitionType,
    complianceState = iff(overallStateWeight == 300, 'noncompliant', iff(overallStateWeight == 200, 'compliant', iff(overallStateWeight == 100, 'conflict', iff(overallStateWeight == 50, 'exempt', 'notstarted')))),
    compliancePercentage,
    compliantCount,
    nonCompliantCount,
    conflictCount,
    exemptCount

3 answers

AnuragSingh-MSFT 21,361 Reputation points

2023-02-22T08:51:01.3266667+00:00
@Joel Pangilinan Thank you for posting the question. Based on the requirement in question the following query should help you

PolicyResources | where kind != 'policyassignments' //the Policy/Initiative assigned | extend definitionType = iff(kind == 'policysetdefinitions', 'Initiative','Policy') | extend custom_Or_Builtin = iff(properties.policyType == 'Custom', 'Custom', 'BuiltIn') | project name = properties.displayName , definitionType, id, custom_Or_Builtin, createdDate = iff(custom_Or_Builtin == 'Custom', properties.metadata.createdOn, properties.metadata.lastSyncedToArgOn) //Policy or Initiative has created data only for 'Custom' Policy. For 'BuiltIn', has lastSyncedToArgOn property denoting when was thepolicy synced to subscription | join kind=leftouter (PolicyResources | where kind == 'policyassignments' | project id= tostring(properties.policyDefinitionId), assignedBy = properties.metadata.assignedBy, assignedScope = properties.scope) on id | project-away id1

Hope this helps. Please let me know if you have any questions.
Please sign in to rate this answer.
AnuragSingh-MSFT 21,361 Reputation points

2023-02-22T09:00:28.1433333+00:00

@Joel Pangilinan, We had received your feedback that the answer provided earlier on this thread was not helpful. Thank you for taking time to share your feedback.

Please review the answer above and let us know if this helps. We are here to help you and strive to make your experience better and greatly value your feedback.

You may consider retaking the survey by clicking 'Yes' once we have helped you to your satisfaction. You may also use the 'Accept answer' button to update the community that the answer helped you with your question. Your feedback is very important to us.

Looking forward to your reply. I much appreciate your feedback!

AnuragSingh-MSFT 21,361 Reputation points

2023-02-28T09:09:59.28+00:00

@Joel Pangilinan, following up to check if you had a chance to check the modified query provided above.

You may consider retaking the survey by clicking 'Yes' once we have helped you to your satisfaction. You may also use the 'Accept answer' button to update the community that the answer helped you with your question. Your feedback is very important to us.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
User A 0 Reputation points

2023-11-14T21:29:52.51+00:00

maybe this is good but in which table? reson for non compliance is found? eg version in 12 compared to expected 14? where the information on Policy dashboard is taken from? why is it that MS always sucks to give information what is where?
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
J.I. PRASHANDH 0 Reputation points

2024-09-03T12:43:20.83+00:00

Hello @AnuragSingh-MSFT

I am checking on the query that you provided. But I am working on something that should list the Policy assignment , and its Scope, and its Type and then for each policy assignment I need the Policy definition Version, Category, Definition Type (Custom or Built In )
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

KQL query with regards to policies

3 answers

Your answer