Azure Active Directory: Conditional Access Policies with Excluded Users Summary
submitted
N
Noah Tatum
I based this off another metric created by David Chapman, so thanks to him for the original idea! I added the ability to also bring in users from excluded groups for a full picture of what users are excluded from CA policies. There might be a more elegant/streamlined way to run the query but this is what I came up with.
Policies.ConditionalAccess[].{ca_displayName:displayName,ca_excludedDisplayNames:join(',',map_by_key(conditions.users.excludeUsers[].{id: @}, ~.Users[],
id
)[].displayName)ca_excludedGroupMemberDisplayNames:join(',',map_by_key(conditions.users.excludeGroups[].{id:@},~.Groups[], id
)[].members[].userPrincipalName.{userPrincipalName: @} | map_by_key(@, ~.Users[],userPrincipalName
)[].displayName)}[].{ca_displayName:ca_displayName,ca_excludedDisplayNames:join(',',join(',',[ca_excludedDisplayNames,ca_excludedGroupMemberDisplayNames]).split(@,',').sort(@).unique_list(@)[?not_null(@)])}[].join(': ',[ca_displayName,ca_excludedDisplayNames])