In the past days I've been trying to using KQL to retrieve some information from policies and initiatives but once I'm not familiar with Kusto I'm having issues. Background: We have some custom policies (included in one initiative by the way) and I need to retrieve all policies that their effect are set to "Deny". These policies are also using custom parameters.
After looking in the "microsoft.authorization/policydefinitions" table and in the policy JSON, I built the code below, however, I got an error.
policyresources
| where type == "microsoft.authorization/policydefinitions"
| where properties['displayName'] startswith "[Custom]"
| where properties['parameters']['effect']['defaulValue'] == "Deny"
| extend
displayName = tostring(properties.displayName)
effect = tostring(properties.parameters.effect.defaultValue)
| project displayName, effect
| Error |
|---|
| Query is invalid. Please refer to the documentation for the Azure Resource Graph service and fix the error before retrying. (Code:InvalidQuery) |
Assuming that my logic is completely wrong, how do I get the list of the policies that are set to "Deny"?
You are on the right track but need some adjustments:
policyresources
| where type == "microsoft.authorization/policydefinitions"
| extend effect = properties.parameters.effect.defaultValue
| extend displayName = properties.displayName
| where effect == "Deny"
| where displayName startswith "[Custom]"
| project displayName, effect
Each property needs its own extend. Also, accessing the custom properties can be done much easier.
Do mind you are looking at the definition. If you want to get details regarding the actual policy state you need to filter the type microsoft.policyinsights/policystates.