Forum Discussion

copperleaf's avatar
copperleaf
Copper Contributor
Feb 13, 2023

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

Resources