Query Details
Identity Conditional Access Most Failures
Query
//Find which users are failing the most Conditional Access policies, retrieve the total failure count, distinct policy count and the names of the failed policies
//Data connector required for this query - Azure Active Directory - Signin Logs
SigninLogs
| where TimeGenerated > ago (30d)
| project TimeGenerated, ConditionalAccessPolicies, UserPrincipalName
| mv-expand ConditionalAccessPolicies
| extend CAResult = tostring(ConditionalAccessPolicies.result)
| extend CAPolicyName = tostring(ConditionalAccessPolicies.displayName)
| where CAResult == "failure"
| summarize
['Total Conditional Access Failures']=count(),
['Distinct Policy Failure Count']=dcount(CAPolicyName),
['Policy Names']=make_set(CAPolicyName)
by UserPrincipalName
| sort by ['Distinct Policy Failure Count'] desc Explanation
This query is used to find which users are failing the most Conditional Access policies. It retrieves the total number of failures, the number of distinct policies that have failed, and the names of the failed policies. The query uses the Azure Active Directory - Signin Logs data connector and filters the data for the past 30 days. It then expands the ConditionalAccessPolicies column, converts the result and display name to strings, and filters for failures. Finally, it summarizes the data by user principal name, counts the total failures, counts the distinct policy failures, and creates a set of policy names. The results are sorted by the number of distinct policy failures in descending order.
Details
Matt Zorich
Released: June 17, 2022
Tables
Keywords
Operators