KQL Search

Query Details

HomeAI ToolsDevice Query

AAD Service Principal Sign In Logs Unexpected Authentication Failure From Service Principal

Query

let query_frequency = 1h;
let query_period = 14d;
let _ExcludedResultTypes = toscalar(
    _GetWatchlist("ResultType-SignInLogsErrorCodes")
    | where Notes has_any ("[Success-App]", "[Failure-App]") and not(Notes has_all ("[ClientSecret]", "[Invalid]"))
    | summarize make_list(ResultType)
);
let _ExcludedIPAddresses = toscalar(
    union
        (
        _GetWatchlist("IP-Vendors")
        | where Notes has_any ("[HomeTenant]", "[Proxy]")
        ),
        (
        _GetWatchlist("IP-CorporateCollaborators")
        | where Notes has_any ("[Egress]")
        )
    | summarize make_list(IPAddress)
);
AADServicePrincipalSignInLogs
| where TimeGenerated > ago(query_period)
| where not(ResultType in (_ExcludedResultTypes)) and not(ipv4_is_in_any_range(IPAddress, _ExcludedIPAddresses))
| summarize arg_min(TimeGenerated, *) by ResultType, ServicePrincipalId, AppId, ResourceIdentity, IPAddress //parse_ipv6_mask(IPAddress, 120)
| where TimeGenerated > ago(query_frequency)
| project
    TimeGenerated,
    CreatedDateTime,
    ServicePrincipalName,
    ResourceDisplayName,
    IPAddress,
    Location,
    ResultType,
    ConditionalAccessStatus,
    ConditionalAccessPolicies,
    AuthenticationProcessingDetails,
    ClientCredentialType,
    ServicePrincipalId,
    AppId,
    ResourceIdentity,
    ResourceServicePrincipalId,
    ServicePrincipalCredentialKeyId,
    ServicePrincipalCredentialThumbprint,
    Id,
    CorrelationId

Explanation

This query is designed to analyze Azure Active Directory (AAD) service principal sign-in logs over a specified period, while excluding certain result types and IP addresses. Here's a simplified breakdown:

  1. Define Timeframes:

    • query_frequency is set to 1 hour, meaning the query focuses on the most recent hour of data.
    • query_period is set to 14 days, indicating the overall time range for the data being analyzed.

  2. Exclude Specific Result Types:

    • A list of result types to exclude is created from a watchlist named "ResultType-SignInLogsErrorCodes". It filters out entries with notes containing "[Success-App]" or "[Failure-App]" but not both "[ClientSecret]" and "[Invalid]".

  3. Exclude Specific IP Addresses:

    • Two watchlists are used to gather IP addresses to exclude:
      • "IP-Vendors" for IPs marked as "[HomeTenant]" or "[Proxy]".
      • "IP-CorporateCollaborators" for IPs marked as "[Egress]".

  4. Filter Sign-In Logs:

    • The AADServicePrincipalSignInLogs table is filtered to include only entries from the last 14 days.
    • Entries with result types or IP addresses in the exclusion lists are removed.

  5. Summarize Data:

    • The query summarizes the logs to find the earliest (arg_min) occurrence of each combination of ResultType, ServicePrincipalId, AppId, ResourceIdentity, and IPAddress.

  6. Recent Data Focus:

    • Further filters the summarized data to include only entries from the last hour.

  7. Select Specific Fields:

    • Projects a set of fields to display, including timestamps, service principal details, IP address, location, result type, and various IDs and credentials related to the sign-in event.

In essence, this query is used to monitor and analyze recent AAD service principal sign-in activities, excluding certain known result types and IP addresses, and focusing on the most recent occurrences within the last hour.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: February 24, 2025

Tables

AADServicePrincipalSignInLogs

Keywords

AADServicePrincipalSignInLogs

Operators

lettoscalarhas_anynothas_allsummarizemake_listunioninipv4_is_in_any_rangearg_minbyprojectago

Actions