KQL Search

Query Details

HomeAI ToolsDevice Query

AAD Non Interactive User Sign In Logs Unexpected Failures In Non Interactive Authentications From An App

Query

let query_frequency = 1h;
let query_period = 14d;
let user_threshold = 10;
let failures_threshold = 3;
let _UnexpectedNonInteractiveFailures = toscalar(
    _GetWatchlist("ResultType-SignInLogsErrorCodes")
    | where Notes has "[Failure]" and (Notes has_all ("[Password]", "[Invalid]") or Notes has "[Malicious]") and isnotempty(ResultDescription) // For example 50126, 50053, 500532
    | summarize make_list(ResultType)
);
let _SuccessResultTypes = toscalar(
    _GetWatchlist("ResultType-SignInLogsErrorCodes")
    | where Notes has_any ("[Expired]", "[Success]") and isnotempty(ResultDescription)
    | summarize make_list(ResultType)
);
AADNonInteractiveUserSignInLogs
| where TimeGenerated > ago(query_period)
| where ResultType in (_UnexpectedNonInteractiveFailures) and HomeTenantId == ResourceTenantId
| summarize
    FailureStartTime = min(TimeGenerated),
    FailureEndTime = max(TimeGenerated),
    FailureEventCount = count(),
    FailureUserCount = dcount(UserId)
    by AppDisplayName, AppId, ResourceDisplayName, ResourceIdentity
| where isnotempty(AppId) or isnotempty(ResourceIdentity)
| where FailureUserCount > user_threshold and FailureEventCount >= failures_threshold * user_threshold
| lookup kind=leftouter (
    AADNonInteractiveUserSignInLogs
    | where TimeGenerated > ago(query_frequency)
    | where ResultType in (_SuccessResultTypes)
    | summarize
        SuccessStartTime = min(TimeGenerated),
        SuccessEndTime = max(TimeGenerated),
        SuccessEventCount = count(),
        SuccessUserCount = dcount(UserId),
        SuccessUsers = array_sort_asc(make_set(UserPrincipalName))
        by AppId, ResourceIdentity
    ) on AppId, ResourceIdentity
| where case(
    array_length(SuccessUsers) > 0, true, // Authentication success or partial success
    FailureStartTime > ago(query_frequency), true, // First password spray in last query_frequency
    false
    )
| extend
    StartTime = min_of(FailureStartTime, SuccessStartTime),
    Endtime = max_of(FailureEndTime, SuccessEndTime),
    AlertSeverity = case(
        array_length(SuccessUsers) > 0, "High", // Authentication success or partial success
        FailureStartTime > ago(query_frequency), "Informational", // First password spray in last query_frequency
        "High"
    )
| project
    StartTime,
    Endtime,
    AppDisplayName,
    ResourceDisplayName,
    FailureEventCount,
    FailureUserCount,
    SuccessEventCount,
    SuccessUserCount,
    SuccessUsers,
    AppId,
    ResourceIdentity,
    AlertSeverity

Explanation

This query is designed to detect and analyze unusual non-interactive sign-in failures in Azure Active Directory (AAD) over a specified period. Here's a simplified breakdown:

  1. Parameters:

    • query_frequency: The recent time window (1 hour) for checking successful sign-ins.
    • query_period: The overall time period (14 days) for analyzing sign-in failures.
    • user_threshold and failures_threshold: Thresholds for identifying significant failure events.

  2. Watchlists:

    • _UnexpectedNonInteractiveFailures: A list of error codes indicating unexpected non-interactive sign-in failures, such as password issues or malicious attempts.
    • _SuccessResultTypes: A list of error codes indicating successful or expired sign-ins.

  3. Failure Analysis:

    • The query examines sign-in logs within the last 14 days to identify applications or resources with a high number of unexpected failures.
    • It filters for cases where the number of unique users and failure events exceed the defined thresholds.

  4. Success Analysis:

    • It checks for any successful sign-ins in the last hour for the same applications or resources.

  5. Alert Generation:

    • If there are any successful sign-ins or if the first failure occurred within the last hour, it flags the event.
    • The alert severity is set to "High" if there are successful sign-ins, or "Informational" if the first failure is recent.

  6. Output:

    • The query outputs details such as the time range of the events, application/resource names, counts of failure and success events, and the severity of the alert.

In essence, this query helps identify potential password spray attacks or other suspicious activities by correlating failure and success sign-in events over a specified period.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: January 24, 2025

Tables

AADNonInteractiveUserSignInLogs

Keywords

QueryFrequencyQueryPeriodUserThresholdFailuresThresholdUnexpectedNonInteractiveFailuresResultTypeSignInLogsErrorCodesNotesFailurePasswordInvalidMaliciousResultDescriptionSuccessResultTypesExpiredSuccessAADNonInteractiveUserSignInLogsTimeGeneratedHomeTenantIdResourceTenantIdFailureStartTimeFailureEndTimeFailureEventCountFailureUserCountAppDisplayNameAppIdResourceDisplayNameResourceIdentityUserIdUserPrincipalNameSuccessStartTimeSuccessEndTimeSuccessEventCountSuccessUserCountSuccessUsersAlertSeverity

Operators

lettoscalar_GetWatchlisthashas_allisnotemptysummarizemake_listinagominmaxcountdcountbyorandlookupkind=leftouterarray_sort_ascmake_setoncasearray_lengthtruefalseextendmin_ofmax_ofproject

Actions