Query Details
Identity Multiple CA Failures
Query
//Detect when a user is blocked by Conditional Access after failing 3 unique CA policies or 3 unique applications over a 2 hour period
//Data connector required for this query - Azure Active Directory - Signin Logs
SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == "53003"
| mv-expand ConditionalAccessPolicies
| extend ['CA Policy Name'] = tostring(ConditionalAccessPolicies.displayName)
| where ConditionalAccessPolicies.result == "failure"
| summarize
['Total count of logon failures']=count(),
['Count of failed applications']=dcount(AppDisplayName),
['List of failed applications']=make_set(AppDisplayName),
['Count of failed policy names']=dcount(['CA Policy Name']),
['List of failed policy names']=make_set(['CA Policy Name'])
by UserPrincipalName, bin(TimeGenerated, 2h)
| where ['Count of failed applications'] >= 3 or ['Count of failed policy names'] >= 3Explanation
This query is used to detect when a user is blocked by Conditional Access after failing 3 unique Conditional Access policies or 3 unique applications over a 2-hour period. It requires the Azure Active Directory - Signin Logs data connector. The query filters the logs based on the time generated and the result type. It then expands the ConditionalAccessPolicies field and renames it to 'CA Policy Name'. It further filters the logs to only include failures in the Conditional Access policies. The query then summarizes the data by counting the total number of logon failures, the count of failed applications, the list of failed applications, the count of failed policy names, and the list of failed policy names for each user and time period. Finally, it filters the results to only include users with at least 3 failed applications or 3 failed policy names.
Details
Matt Zorich
Released: June 17, 2022
Tables
Keywords
Operators