Query Details
Identity Muiltiple Conditional Access Failures
Query
//Alert when a user fails Azure AD Conditional Access policies to 5 or more unique applications within a short time period, this example uses 1 hour.
//Data connector required for this query - Azure Active Directory - Signin Logs
SigninLogs
| where TimeGenerated > ago (1d)
| project TimeGenerated, ConditionalAccessPolicies, UserPrincipalName, AppDisplayName
| mv-expand ConditionalAccessPolicies
| extend CAResult = tostring(ConditionalAccessPolicies.result)
| extend CAPolicyName = tostring(ConditionalAccessPolicies.displayName)
| where CAResult == "failure"
| summarize
['List of Failed Application']=make_set(AppDisplayName),
['Count of Failed Application']=dcount(AppDisplayName)
by UserPrincipalName, bin(TimeGenerated, 1h)
| where ['Count of Failed Application'] >= 5
//Data connector required for this query - Advanced Hunting - AADSignInEventsBeta
AADSignInEventsBeta
| where Timestamp > ago (1d)
| project Timestamp, ConditionalAccessPolicies, AccountUpn, Application
| extend ConditionalAccessPolicies = parse_json(ConditionalAccessPolicies)
| extend CAResult = tostring(ConditionalAccessPolicies.result)
| extend CAPolicyName = tostring(ConditionalAccessPolicies.displayName)
| where CAResult == "failure"
| summarize
['List of Failed Application']=make_set(Application),
['Count of Failed Application']=dcount(Application)
by AccountUpn, bin(Timestamp, 1h)
| where ['Count of Failed Application'] >= 5
Explanation
This query is used to create an alert when a user fails Azure AD Conditional Access policies to 5 or more unique applications within a short time period (1 hour).
The query uses two different data connectors - Azure Active Directory - Signin Logs and Advanced Hunting - AADSignInEventsBeta.
For the Azure Active Directory - Signin Logs data connector, the query filters the logs to retrieve data from the past 1 day. It then projects the TimeGenerated, ConditionalAccessPolicies, UserPrincipalName, and AppDisplayName fields. The mv-expand operator is used to expand the ConditionalAccessPolicies field into separate rows. The query extends the CAResult and CAPolicyName fields to convert them to strings. It filters the data to only include failures in the CAResult field. The query then summarizes the data by UserPrincipalName and TimeGenerated (binned into 1-hour intervals), and calculates the count of failed applications and creates a set of the failed application names. It filters the results to only include users with 5 or more failed applications.
For the Advanced Hunting - AADSignInEventsBeta data connector, the query filters the events to retrieve data from the past 1 day. It projects the Timestamp, ConditionalAccessPolicies, AccountUpn, and Application fields. The query extends the ConditionalAccessPolicies field by parsing it as JSON and converts the CAResult and CAPolicyName fields to strings. It filters the data to only include failures in the CAResult field. The query then summarizes the data by AccountUpn and Timestamp (binned into 1-hour intervals), and calculates the count of failed applications and creates a set of the failed application names. It filters the results to only include users with 5 or more failed applications.
Details
Matt Zorich
Released: April 19, 2023
Tables
Keywords
Operators