KQL Search

// This query can help you to detect unexpected requests for MFA, because for example the user has declined the MFA request, or several requests were left unresponded
//
// Click "Save as function", in Parameters write in the fields:
// "timespan" "query_frequency" "1h"
// "timespan" "query_period"    "14d"
//
// If you name the function "PotentialMFASpam", you can check the function with queries like the following:
//
// PotentialMFASpam()
//
// PotentialMFASpam(1h, 14d)
//
//let query_frequency = 1h;
//let query_period = 14d; // A CorrelationId can extend over several weeks
//let Function = (query_frequency:timespan = 1h, query_period:timespan = 14d){
let mfa_denied_key_strings = dynamic(["MFA denied", "User denied", "SuspiciousActivityReported"]);
let unreported_denied_message_types = dynamic([
    "MFA denied; user did not respond to mobile app notification",
    "MFA denied; invalid authentication",
    "MFA denied; invalid verification code",
    "MFA denied; user did not select the correct number",
    "MFA denied; Verification code already used"
]);
// Other messages:
//     MFA denied; duplicate authentication attempt
//     MFA denied; user declined the authentication
//     MFA denied; Phone App Reported Fraud
//     MFA denied; user is blocked
//     SuspiciousActivityReported
let _SuccessResultTypes = toscalar( // "0","50140", "50199"
    _GetWatchlist('ResultType-SignInLogsErrorCodes')
    | where Notes has_all ("[Success]", "[Complete]") and isnotempty(ResultDescription)
    | summarize make_list(ResultType)
);
let _ExpectedLocations = toscalar(
    _GetWatchlist("Activity-ExpectedSignificantActivity")
    | where Activity == "CorporateGeolocation"
    | summarize make_list(Auxiliar)
);
let _ExpectedIPRanges = toscalar(
    union
        (_GetWatchlist("IP-Vendors")
        | where Vendor == "Microsoft"
            or (Vendor == "Proxy")
        ),
        (_GetWatchlist("IP-CorporateCollaborators")
        )
    | summarize make_list(IPAddress)
);
let _ExpectedDomain = toscalar(
    _GetWatchlist("UUID-AADTenantIds")
    | where Notes has "[HomeTenant]"
    | summarize make_list(strcat("@", Domain))
);
let _CorrelationIds = toscalar(
    SigninLogs
    | where TimeGenerated > ago(query_frequency) and (AuthenticationDetails has_any (mfa_denied_key_strings) or Status has_any (mfa_denied_key_strings) or ResultType == "500121")
    | summarize make_set(CorrelationId)
);
SigninLogs
| where TimeGenerated > ago(query_period) and CorrelationId in (_CorrelationIds)
| as _SigninEvents
| mv-expand AuthStep = todynamic(AuthenticationDetails)
| evaluate bag_unpack(AuthStep): (UserId: string, CorrelationId: string, OriginalRequestId: string, authenticationStepDateTime: datetime, authenticationStepResultDetail: string, authenticationMethod: string, authenticationMethodDetail: string, succeeded: bool, RequestSequence: long, StatusSequence: long, AuthenticationRequirement: string, authenticationStepRequirement: string)
| union (
    _SigninEvents
    | extend
        AuthenticationRequirement = tostring(AuthenticationRequirement),
        authenticationStepResultDetail = tostring(Status["additionalDetails"])
    | extend
        succeeded = case(
            ResultType in (_SuccessResultTypes) and authenticationStepResultDetail has_any ("MFA completed", "requirement satisfied", "MFA requirement skipped"), true,
            bag_has_key(Status, "failureReason"), false,
            bool(null)
        ),
        authenticationMethod = tostring(MfaDetail["authMethod"]),
        authenticationMethodDetail = tostring(MfaDetail["authDetail"])
    | summarize
        authenticationStepDateTime = min(TimeGenerated)
        by UserId, CorrelationId, OriginalRequestId, AuthenticationRequirement, authenticationStepResultDetail, succeeded, authenticationMethod, authenticationMethodDetail
)
// Remove requirements satisfied by token
| where not(authenticationStepResultDetail has_any ("requirement satisfied by claim in the token", "MFA requirement skipped") and succeeded)// and authenticationMethod has "Previously satisfied")
// Remove Password events where afterwards strong authentication will fail
| where not(authenticationMethod == "Password" and not(succeeded) and authenticationStepResultDetail == "Authentication failed during strong authentication request.")
// Remove "Previously satisfied" events
| where not(authenticationMethod == "Previously satisfied")
// Remove successful Password events
| where not(succeeded and authenticationMethod == "Password") //and isempty(authenticationStepResultDetail) and authenticationMethodDetail in ("Password Hash Sync", "Password in the cloud"))
// Remove expired MFA claims
| where not(authenticationStepResultDetail == "MFA claim has expired due to the policies configured on tenant" and not(succeeded) and isempty(authenticationMethod))
// Empty "successful" authenticationStepResultDetails that have not succeeded yet
| extend authenticationStepResultDetail = iff(not(succeeded) and authenticationStepResultDetail in ("MFA successfully completed", "MFA completed in Azure AD", "MFA requirement satisfied by claim in the token"), "", authenticationStepResultDetail)
// Remove empty events
| where not(isempty(authenticationStepResultDetail) and isempty(authenticationMethod) and isempty(authenticationMethodDetail) and (isempty(succeeded) or not(succeeded)) and isempty(RequestSequence) and isempty(StatusSequence) and isempty(authenticationStepRequirement))
// Deduplicate
| summarize
    take_anyif(authenticationStepRequirement, isnotempty(authenticationStepRequirement)),
    AuthenticationRequirement = strcat_array(array_sort_desc(make_set(AuthenticationRequirement)), ", "),
    authenticationStepDateTime = min(authenticationStepDateTime)
    by UserId, CorrelationId, OriginalRequestId, authenticationStepResultDetail, authenticationMethod, authenticationMethodDetail, succeeded, RequestSequence, StatusSequence
| as hint.materialized=true _AuthenticationSteps
| lookup kind=leftouter (
    _AuthenticationSteps
    | summarize minOriginalRequestIdTimeGenerated = min(authenticationStepDateTime) by OriginalRequestId
    ) on OriginalRequestId
// | lookup kind=leftouter (
//     _AuthenticationSteps
//     | summarize minCorrelationIdTimeGenerated = min(authenticationStepDateTime) by CorrelationId
//     ) on CorrelationId
| partition hint.strategy=shuffle by OriginalRequestId (
    // Take the last event for each RequestSequence when it is unique
    sort by succeeded
    | extend RowNumber = iff(isempty(RequestSequence) or RequestSequence < 10, row_number(), int(null))
    | summarize arg_max(StatusSequence, *) by RequestSequence, RowNumber
    | project-away RowNumber
    | sort by
        authenticationStepDateTime asc,
        authenticationMethod asc,
        succeeded asc,
        authenticationStepResultDetail desc,
        AuthenticationRequirement desc,
        authenticationStepRequirement desc,
        RequestSequence asc,
        StatusSequence asc
    // Remove empty (and redundant) "MFA successfully completed"
    | where not(authenticationStepResultDetail == "MFA successfully completed" and succeeded and isempty(authenticationMethodDetail) and isempty(RequestSequence)
        and isnotempty(next(OriginalRequestId))
        )
    // Remove redundant ""
    | where not(authenticationStepResultDetail == "" and not(succeeded) and isempty(authenticationMethodDetail) and isempty(RequestSequence)
        and (isnotempty(next(OriginalRequestId)) or isnotempty(prev(OriginalRequestId)))
        )
    | where not(authenticationStepResultDetail == "" and not(succeeded) and isnotempty(authenticationMethod) and isnotempty(authenticationMethodDetail)
        and next(authenticationStepResultDetail) != "" and next(authenticationStepDateTime) == authenticationStepDateTime and next(authenticationMethod) == authenticationMethod and next(authenticationMethodDetail) == authenticationMethodDetail
        )
    // Remove duplicated "Other"
    | where not(authenticationStepResultDetail == "Other" and isnotempty(authenticationMethod)
        and next(authenticationStepResultDetail) != "Other" and next(authenticationStepDateTime) == authenticationStepDateTime and next(authenticationMethod) == authenticationMethod and next(RequestSequence) == RequestSequence and next(StatusSequence) == StatusSequence and next(AuthenticationRequirement) == AuthenticationRequirement
        )
    // Remove redundant "Other"
    | where not(authenticationStepResultDetail == "Other" and isnotempty(authenticationMethod) and isempty(authenticationMethodDetail)
        and next(authenticationStepResultDetail) != "Other" and next(authenticationMethod) == authenticationMethod and isnotempty(next(authenticationMethodDetail)) and next(succeeded) == succeeded
        )
    // Remove duplicate "MFA required in Azure AD"
    | where not(authenticationStepResultDetail == "MFA required in Azure AD" and isempty(authenticationMethod)
        and next(authenticationStepResultDetail) == "MFA required in Azure AD" and isnotempty(next(authenticationMethod))
        and next(authenticationStepDateTime) == authenticationStepDateTime and next(succeeded) == succeeded and next(AuthenticationRequirement) == AuthenticationRequirement
        )
    // Remove redundant "MFA required in Azure AD"
    | where not(authenticationStepResultDetail == "MFA required in Azure AD"
        and next(authenticationStepResultDetail) != "MFA required in Azure AD" and ((isempty(authenticationMethod) and isnotempty(next(authenticationMethod))) or authenticationMethod == next(authenticationMethod))
        )
    // Remove duplicated end states with the same time as "Authentication in progress"
    | where not(authenticationStepResultDetail != "Authentication in progress" and isempty(authenticationMethodDetail) and isempty(RequestSequence) and isempty(StatusSequence)
        and (
            (prev(authenticationStepResultDetail) == "Authentication in progress" and prev(authenticationStepDateTime) == authenticationStepDateTime and isempty(prev(authenticationMethodDetail)) and isempty(prev(RequestSequence)) and isempty(prev(StatusSequence)))
            or
            (next(authenticationStepResultDetail) == "Authentication in progress" and next(authenticationStepDateTime) == authenticationStepDateTime and isempty(next(authenticationMethodDetail)) and isempty(next(RequestSequence)) and isempty(next(StatusSequence)))
        )
        )
    // Remove duplicated end states with the same time as a not succeeded yet event
    | where not(succeeded and isempty(authenticationMethodDetail) and isempty(RequestSequence) and isempty(StatusSequence)
        and not(prev(succeeded)) and prev(authenticationStepDateTime) == authenticationStepDateTime and isempty(prev(authenticationMethodDetail)) and isempty(prev(RequestSequence)) and isempty(prev(StatusSequence))
        )
    // Remove duplicated "MFA completed in Azure AD"
    | where not(authenticationStepResultDetail == "MFA completed in Azure AD" and isempty(authenticationMethodDetail)
        and next(authenticationStepResultDetail) == "MFA completed in Azure AD" and isnotempty(next(authenticationMethodDetail)) and next(authenticationMethod) == authenticationMethod and next(authenticationStepDateTime) == authenticationStepDateTime
        )
    // Remove redundant "Authentication in progress"
    | where not(authenticationStepResultDetail == "Authentication in progress" and isempty(authenticationMethodDetail) and isempty(RequestSequence)
        and next(authenticationMethod) == authenticationMethod
        )
    // Change "MFA successfully completed" info
    | extend Auxiliar = (authenticationStepResultDetail == "MFA successfully completed" and succeeded
        and next(authenticationStepResultDetail) == "MFA completed in Azure AD" and next(succeeded) == succeeded and next(authenticationMethod) == authenticationMethod and (next(authenticationMethodDetail) == authenticationMethodDetail or (isempty(next(authenticationMethodDetail)) and isnotempty(authenticationMethodDetail)) or (isnotempty(next(authenticationMethodDetail)) and isempty(authenticationMethodDetail)))
        )
    | extend
        authenticationStepDateTime = iff(Auxiliar, next(authenticationStepDateTime), authenticationStepDateTime),
        authenticationMethodDetail = iff(Auxiliar and isnotempty(next(authenticationMethodDetail)), next(authenticationMethodDetail), authenticationMethodDetail)
    | where not(isnotempty(prev(Auxiliar)) and prev(Auxiliar)
        )
    | project-away Auxiliar
    // Remove redundant authenticationStepResultDetail
    | where not(isempty(authenticationMethodDetail) and isempty(RequestSequence)
        and next(authenticationStepResultDetail) == authenticationStepResultDetail and next(authenticationMethod) == authenticationMethod and (isnotempty(next(authenticationMethodDetail)) or isnotempty(next(RequestSequence)))
        )
    | scan declare (
        AttemptCount: long=0,
        //Accounted:bool = false,
        UsedMethods: dynamic = dynamic([]),
        UsedRequestSequences: dynamic = dynamic([]),
        SuccessMethods: dynamic = dynamic([]),
        SuccessMethodDetails: dynamic = dynamic([]),
        DeniedResults: dynamic = dynamic([]),
        minauthenticationStepDateTime: datetime = datetime(null),
        maxauthenticationStepDateTime: datetime = datetime(null)
        ) with
        (
        step Step1 output=last:
            true
            =>  AttemptCount = case(
                    // First events
                    isempty(Step1.authenticationStepDateTime) and authenticationStepResultDetail == "MFA denied; duplicate authentication attempt", Step1.AttemptCount,
                    isempty(Step1.authenticationStepDateTime) and authenticationStepResultDetail has "MFA denied; invalid", Step1.AttemptCount + 1,
                    // Other events
                    Step1.authenticationStepResultDetail has "MFA denied; invalid" and Step1.succeeded == succeeded and Step1.authenticationMethod == authenticationMethod and (isnotempty(authenticationMethodDetail) or isnotempty(RequestSequence)), Step1.AttemptCount,
                    Step1.authenticationStepResultDetail has "MFA denied; invalid" and Step1.authenticationMethod == authenticationMethod and succeeded, Step1.AttemptCount + 1,
                    isnotempty(authenticationMethod) and Step1.UsedMethods has authenticationMethod and isempty(RequestSequence), Step1.AttemptCount,
                    isnotempty(authenticationMethod) and not(Step1.UsedMethods has authenticationMethod), Step1.AttemptCount + 1,
                    isnotempty(RequestSequence) and Step1.UsedMethods has authenticationMethod and Step1.AttemptCount > array_length(Step1.UsedRequestSequences), Step1.AttemptCount,
                    isnotempty(RequestSequence) and Step1.RequestSequence != RequestSequence, Step1.AttemptCount + 1,
                    // End events
                    authenticationStepResultDetail == "MFA completed in Azure AD" and succeeded and array_length(Step1.SuccessMethods) > 0, Step1.AttemptCount,
                    authenticationStepResultDetail == "MFA completed in Azure AD" and succeeded and array_length(Step1.SuccessMethods) == 0, Step1.AttemptCount + 1,
                    Step1.AttemptCount
                ),
                // Accounted = case(
                //     // First events
                //     isempty(Step1.authenticationStepDateTime) and authenticationStepResultDetail == "MFA denied; duplicate authentication attempt", true,
                //     isempty(Step1.authenticationStepDateTime) and authenticationStepResultDetail has "MFA denied; invalid", true,
                //     // Other events
                //     Step1.authenticationStepResultDetail has "MFA denied; invalid" and Step1.authenticationMethod == authenticationMethod and not(succeeded) and (isnotempty(authenticationMethodDetail) or isnotempty(RequestSequence)), true,
                //     Step1.authenticationStepResultDetail has "MFA denied; invalid" and Step1.authenticationMethod == authenticationMethod and succeeded, true,
                //     isnotempty(authenticationMethod) and Step1.UsedMethods has authenticationMethod and isempty(RequestSequence), true,
                //     isnotempty(authenticationMethod) and not(Step1.UsedMethods has authenticationMethod), true,
                //     isnotempty(RequestSequence) and Step1.UsedMethods has authenticationMethod and Step1.AttemptCount > array_length(Step1.UsedRequestSequences), true,
                //     isnotempty(RequestSequence) and Step1.RequestSequence != RequestSequence, true,
                //     // End events
                //     authenticationStepResultDetail == "MFA completed in Azure AD" and succeeded and array_length(Step1.SuccessMethods) > 0, true,
                //     authenticationStepResultDetail == "MFA completed in Azure AD" and succeeded and array_length(Step1.SuccessMethods) == 0, true,
                //     false
                // ),
                UsedMethods = iff(isnotempty(authenticationMethod), array_concat(Step1.UsedMethods, pack_array(authenticationMethod)), Step1.UsedMethods),
                UsedRequestSequences = iff(isnotempty(RequestSequence), array_concat(Step1.UsedRequestSequences, pack_array(RequestSequence)), Step1.UsedRequestSequences),
                SuccessMethods = iff(succeeded and not(Step1.SuccessMethods has authenticationMethod), array_concat(Step1.SuccessMethods, pack_array(authenticationMethod)), Step1.SuccessMethods),
                SuccessMethodDetails = iff(succeeded and isnotempty(authenticationMethodDetail) and not(Step1.SuccessMethodDetails has authenticationMethodDetail), array_concat(Step1.SuccessMethodDetails, pack_array(authenticationMethodDetail)), Step1.SuccessMethodDetails),
                DeniedResults = iff(authenticationStepResultDetail has_any (mfa_denied_key_strings) and not(authenticationStepResultDetail has "duplicate authentication attempt"), array_concat(Step1.DeniedResults, pack_array(authenticationStepResultDetail)), Step1.DeniedResults),
                minauthenticationStepDateTime = iff(isempty(Step1.minauthenticationStepDateTime), authenticationStepDateTime, min_of(Step1.minauthenticationStepDateTime, authenticationStepDateTime)),
                maxauthenticationStepDateTime = iff(isempty(Step1.maxauthenticationStepDateTime), authenticationStepDateTime, max_of(Step1.maxauthenticationStepDateTime, authenticationStepDateTime))
        ;
    )
    | extend
        AttemptCount = case(
            not(succeeded) and AttemptCount == 0 and (isempty(next(authenticationStepDateTime)) and isempty(prev(authenticationStepDateTime))), AttemptCount + 1,
            AttemptCount
        )//,
        // Accounted = case(
        //     not(succeeded) and AttemptCount == 0  and (isempty(next(authenticationStepDateTime)) and isempty(prev(authenticationStepDateTime))), true,
        //     Accounted
        // )
)
| partition hint.strategy=shuffle by UserId (
    sort by minOriginalRequestIdTimeGenerated asc
    | scan declare (
        CorrelationIds: dynamic = dynamic([]),
        OriginalRequestIds: dynamic = dynamic([])
        ) with
        (
        step Step1 output=last:
            true
            =>  AttemptCount = iff(isnotempty(Step1.AttemptCount), Step1.AttemptCount + AttemptCount, AttemptCount),
                UsedMethods = iff(isnotempty(Step1.UsedMethods), array_concat(Step1.UsedMethods, UsedMethods), UsedMethods),
                UsedRequestSequences = iff(isnotempty(Step1.UsedRequestSequences), array_concat(Step1.UsedRequestSequences, UsedRequestSequences), UsedRequestSequences),
                DeniedResults = iff(isnotempty(Step1.DeniedResults), array_concat(Step1.DeniedResults, DeniedResults), DeniedResults),
                minauthenticationStepDateTime = iff(isempty(Step1.minauthenticationStepDateTime), minauthenticationStepDateTime, min_of(Step1.minauthenticationStepDateTime, minauthenticationStepDateTime)),
                maxauthenticationStepDateTime = iff(isempty(Step1.maxauthenticationStepDateTime), maxauthenticationStepDateTime, max_of(Step1.maxauthenticationStepDateTime, maxauthenticationStepDateTime)),
                CorrelationIds = array_concat(Step1.CorrelationIds, pack_array(CorrelationId)),
                OriginalRequestIds = array_concat(Step1.OriginalRequestIds, pack_array(OriginalRequestId))
        ;
        step Step2 output=none:
            array_length(Step1.SuccessMethods) > 0 or authenticationStepDateTime - Step1.authenticationStepDateTime > 30m
        ;
    )
)
| extend
    CorrelationIds = set_union(CorrelationIds, dynamic([])),
    OriginalRequestIds = set_union(OriginalRequestIds, dynamic([]))
| mv-apply UsedMethods to typeof(string) on (
    summarize Count = count() by UsedMethods
    | summarize UsedMethods = make_bag(pack(UsedMethods, Count))
)
| mv-apply DeniedResults to typeof(string) on (
    summarize Count = count() by DeniedResults
    | summarize DeniedResults = make_bag(pack(DeniedResults, Count))
)
| mv-expand CorrelationId = CorrelationIds to typeof(string)
| lookup kind=leftouter (
    _SigninEvents
    | summarize
        take_anyif(UserPrincipalName, not(UserPrincipalName matches regex @"[a-f0-9]+-[a-f0-9]+-[a-f0-9]+-[a-f0-9]+-[a-f0-9]+")),
        Location = take_anyif(Location, isnotempty(Location))
        by CorrelationId, IPAddress
    | summarize
        take_any(UserPrincipalName, isnotempty(UserPrincipalName)),
        Sources = make_set(tostring(pack_array(IPAddress, Location)))
        by CorrelationId
    ) on CorrelationId
| summarize
    Sources = array_sort_asc(make_set(Sources)),
    take_any(*)
    by Auxiliar = tostring(CorrelationIds)
| project-away Auxiliar, Sources1
| mv-apply Source = Sources on (
    extend Source = todynamic(dynamic_to_json(Source))
    | extend
        ExpectedIPAddress = iff(isnotempty(parse_ipv4(tostring(Source[0]))), ipv4_is_in_any_range(tostring(Source[0]), _ExpectedIPRanges), bool(null)),
        ExpectedLocation = isempty(Source[1]) or tostring(Source[1]) in (_ExpectedLocations)
    | extend ExpectedSource = (isnotempty(ExpectedIPAddress) and ExpectedIPAddress) or (isnotempty(ExpectedLocation) and ExpectedLocation)
    | summarize Sources = make_bag(pack(tostring(Source[0]), tostring(Source[1]))), ExpectedSources = make_list(ExpectedSource), ExpectedIPAddresses = make_list(ExpectedIPAddress)
    | extend
        ExpectedIPAddresses = not(ExpectedIPAddresses has "false"),
        ExpectedSources = not(ExpectedSources has "false")
)
| extend BenignAlert = case(
    // Assume successful SMS code is benign
    array_length(SuccessMethods) == 1 and SuccessMethods[0] == "Text message", true,
    // Assume successful OATH verification code is benign
    array_length(SuccessMethods) == 1 and SuccessMethods[0] == "OATH verification code", true,
    // Assume successful Microsoft Authenticator notification is benign (because of number matching)
    array_length(SuccessMethods) == 1 and SuccessMethods[0] == "Mobile app notification", true,
    // Guest account
    not(UserPrincipalName has_any (_ExpectedDomain)), true,
    // Expected source - 1 attempt - unsuccessful
    ExpectedSources and AttemptCount == 1 and array_length(bag_keys(UsedMethods)) == 1 and array_length(SuccessMethods) == 0, true,
    // Expected source - 1 attempt - 0 denied result - successful // might be produced because "MFA denied; duplicate authentication attempt"
    ExpectedSources and AttemptCount == 1 and array_length(bag_keys(DeniedResults)) == 0 and array_length(bag_keys(UsedMethods)) == 1 and array_length(SuccessMethods) == 1 and tostring(bag_keys(UsedMethods)[0]) == tostring(SuccessMethods[0]), true,
    // Expected source - many attempts - unreported denied results - any result
    ExpectedSources and array_length(set_difference(bag_keys(DeniedResults), unreported_denied_message_types)) == 0, true,
    // Expected IP address - many attempts - 1 denied result - any result
    ExpectedIPAddresses and array_length(bag_keys(DeniedResults)) == 1 and DeniedResults[tostring(bag_keys(DeniedResults)[0])] == 1, true,
    false
)
| where not(BenignAlert)
| sort by UserId asc, minOriginalRequestIdTimeGenerated asc
| project
    UserPrincipalName,
    IPAddress = iff(array_length(bag_keys(Sources)) == 1, tostring(bag_keys(Sources)[0]), ""),
    Sources,
    ExpectedIPAddresses,
    ExpectedSources,
    minauthenticationStepDateTime,
    maxauthenticationStepDateTime,
    AttemptCount,
    UsedMethods,
    DeniedResults,
    UsedRequestSequences,
    SuccessMethods,
    SuccessMethodDetails,
    UserId,
    CorrelationIds,
    OriginalRequestIds
//};
//Function(query_frequency, query_period)