Forum Discussion
copperleaf
Feb 13, 2023Copper Contributor
KQL Policy Definition ID to displayName and Description
I'm new to KQL and I have a KQL query (CIS Benchmark). Among other things, the query returns me the policyDefinitionId. Unfortunately, this is not readable. How do I do a join so I can retrieve the policy definition displayname and description?
Here is the query:
PolicyResources
| where type =~ 'Microsoft.PolicyInsights/PolicyStates' and properties.policyAssignmentId =~ '/providers/microsoft.management/managementgroups/xxx/providers/microsoft.authorization/policyassignments/8e0161c630a04095a6f38306'
|project subscriptionId, properties,id, resource_id=tolower(tostring(properties.resourceId))
| join kind=leftouter (resources
| project resource_id=tolower(tostring(id)),resource_name=name) on resource_id
| join kind=inner (resourcecontainers
| where type == 'microsoft.resources/subscriptions'
| project subscriptionId,subscription_contact=tostring(tags.resourcecontact), sbg=tostring(tags.sbg), management_group=tostring(properties.managementGroupAncestorsChain[0].displayName),subscription_name=name)on subscriptionId
| project management_group, subscription_name, subscriptionId, subscription_contact, properties.complianceState, properties.policyDefinitionReferenceId, AssignmentID = tostring(id), properties.resourceType, InstanceID = tostring(properties.resourceId), resource_name
No RepliesBe the first to reply