Query Details
Ca Mfa Exclude Rule Xdr
Query
let MfaCaPolicyName = "103 - ALL - User Access - All apps: Require MFA";
let MfaCaPolicyObject = AADSCA_CAP_CL
| where displayName_s == MfaCaPolicyName
| summarize arg_max(TimeGenerated, *) by id_g;
let ExcludedUsers = MfaCaPolicyObject
| mv-expand parse_json(conditions_users_excludeUsers_s)
| summarize by tostring(conditions_users_excludeUsers_s);
let NotAppliedSignIns = AADSignInEventsBeta
| mv-apply MfaPolicy = parse_json(ConditionalAccessPolicies) to typeof(dynamic) on (
where MfaPolicy.displayName == (MfaCaPolicyName)
)
| extend MfaPolicyStatus = tostring(parse_json(MfaPolicy)["result"])
| where MfaPolicyStatus == "notApplied"
| mv-expand parse_json(MfaPolicy)["excludeRulesSatisfied"]
| extend MfaPolicyExclude = parse_json(MfaPolicy_excludeRulesSatisfied)["ruleSatisfied"]
| project-reorder MfaPolicyStatus, MfaPolicyExclude, DeviceName, OSPlatform, Application, ResourceDisplayName;
NotAppliedSignIns
| extend Bypass = iff((AccountObjectId in (ExcludedUsers)), "excludedByUserId", tostring(MfaPolicyExclude))
// Comment Line 20 or 21
//| where parse_json(MfaPolicy_excludeRulesSatisfied)["ruleSatisfied"] == 'appId'
| summarize count() by tostring(Bypass)Explanation
This query is analyzing Azure Active Directory (AAD) sign-in events to identify instances where a specific Conditional Access policy, which requires Multi-Factor Authentication (MFA), was not applied. Here's a simplified breakdown of what the query does:
-
Define the Policy Name: The query starts by defining the name of the Conditional Access policy that requires MFA.
-
Retrieve Policy Details: It fetches the details of this policy from a table (
AADSCA_CAP_CL) and identifies the most recent version of the policy by its ID. -
Identify Excluded Users: The query extracts a list of users who are excluded from this policy.
-
Find Sign-Ins Where Policy Was Not Applied: It examines sign-in events (
AADSignInEventsBeta) to find instances where this MFA policy was not applied. It checks the policy's status and identifies the reason it was not applied, such as specific exclusion rules being satisfied. -
Determine Bypass Reason: For each sign-in where the policy was not applied, it determines whether the bypass was due to the user being explicitly excluded or due to other exclusion rules.
-
Summarize Results: Finally, it counts the number of sign-ins bypassed for each reason and summarizes the results.
In essence, this query helps identify and categorize the reasons why a specific MFA policy was not enforced during certain sign-in attempts, focusing on user exclusions and other exclusion criteria.
Details
Thomas Naunheim
Released: May 27, 2025
Tables
Keywords
Operators