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 = EntraIdSignInEvents
    | 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 KQL (Kusto Query Language) query is designed to analyze sign-in events related to a specific Conditional Access policy that requires Multi-Factor Authentication (MFA). Here's a simple breakdown of what the query does:

Define the Policy Name: It starts by setting a variable MfaCaPolicyName to the name of the Conditional Access policy that requires MFA.
Identify the Policy Object: It retrieves the specific policy object from the AADSCA_CAP_CL table where the policy's display name matches the specified name. It selects the most recent entry for each policy ID.
Find Excluded Users: It extracts a list of users who are excluded from this MFA policy by expanding the conditions_users_excludeUsers_s field and summarizing the results.
Identify Sign-Ins Where MFA Was Not Applied: It looks at sign-in events from the EntraIdSignInEvents table and checks if the MFA policy was not applied. It does this by examining the ConditionalAccessPolicies field for each sign-in event and filtering for those where the policy result was "notApplied."
Determine Why MFA Was Not Applied: For each sign-in where MFA was not applied, it checks if the user was excluded by user ID or by other exclusion rules. It extends the data with a new field Bypass that indicates the reason for the bypass.
Summarize the Results: Finally, it summarizes the count of sign-ins where MFA was not applied, grouped by the reason for the bypass.

In essence, this query helps identify and categorize instances where a specific MFA policy was not enforced during user sign-ins, providing insights into whether these instances were due to user exclusions or other policy rules.

Details

Thomas Naunheim

Released: November 8, 2025

Tables

AADSCA_CAP_CLEntraIdSignInEvents

Keywords

MfaCaPolicyNameAADSCA_CAP_CLEntraIdSignInEventsTimeGeneratedId_gDisplayName_sConditionsUsersExcludeUsersMfaPolicyConditionalAccessPoliciesResultRuleSatisfiedAccountObjectIdDeviceNameOSPlatformApplicationResourceDisplayNameBypassExcludedByUserIdAppId

Operators

letwheresummarizearg_maxmv-expandtostringmv-applyextendproject-reorderiffinparse_jsoncount

Actions

GitHub

KQL Search