KQL Search

Query Details

HomeAI ToolsDevice Query

AAD Service Principal Sign In Logs Suspicious Multiple Service Principal Authentication From IP Address

Query

let query_frequency = 1h;
let query_period = 14d;
let new_activity_threshold = 1d;
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)
);
union AADServicePrincipalSignInLogs
| where TimeGenerated > ago(query_period)
| where isnotempty(IPAddress) and isnotempty(ServicePrincipalId) and isnotempty(ResultType)
| summarize arg_min(TimeGenerated, *) by IPAddress, ServicePrincipalId, ResultType
| summarize
    StartTime = min(TimeGenerated),
    EndTime = max(TimeGenerated),
    FailedStartTime = minif(TimeGenerated, not(ResultType == 0)),
    SuccessfulStartTime = minif(TimeGenerated, not(ResultType == 0)),
    FailedServicePrincipalIds = make_set_if(ServicePrincipalId, not(ResultType == 0), 100),
    SuccessfulServicePrincipalIds = make_set_if(ServicePrincipalId, ResultType == 0, 100),
    FailedServicePrincipalNames = make_set_if(ServicePrincipalName, not(ResultType == 0), 100),
    SuccessfulServicePrincipalNames = make_set_if(ServicePrincipalName, ResultType == 0, 100)
    by IPAddress
| where EndTime > ago(query_frequency)
| extend
    FailedServicePrincipalCount = array_length(FailedServicePrincipalIds),
    SuccessfulServicePrincipalCount = array_length(SuccessfulServicePrincipalIds)
| where not(FailedServicePrincipalCount == 1 and SuccessfulServicePrincipalCount == 0)
| where not(FailedServicePrincipalCount == 0 and SuccessfulServicePrincipalCount == 1)
| where not(FailedServicePrincipalCount == 0 and SuccessfulServicePrincipalCount > 1 and SuccessfulStartTime < ago(new_activity_threshold))
| where not(FailedServicePrincipalCount == 1 and SuccessfulServicePrincipalCount == 1 and tostring(FailedServicePrincipalIds[0]) == tostring(SuccessfulServicePrincipalIds[0]))
| where not(FailedServicePrincipalCount <= 1 and SuccessfulServicePrincipalCount > 0 and ipv4_is_in_any_range(IPAddress, _ExcludedIPAddresses))
| where not(FailedServicePrincipalCount > 1 and SuccessfulServicePrincipalCount > 0 and ipv4_is_in_any_range(IPAddress, _ExcludedIPAddresses) and FailedStartTime < ago(new_activity_threshold))
| project
    StartTime,
    EndTime,
    IPAddress,
    FailedStartTime,
    FailedServicePrincipalCount,
    FailedServicePrincipalNames,
    SuccessfulStartTime,
    SuccessfulServicePrincipalCount,
    SuccessfulServicePrincipalNames,
    FailedServicePrincipalIds,
    SuccessfulServicePrincipalIds

Explanation

This query is designed to analyze sign-in activities of Azure Active Directory (AAD) service principals over a specified period and identify potentially suspicious activities based on certain criteria. Here's a simplified breakdown:

  1. Timeframe and Frequency:

    • The query looks at sign-in logs from the past 14 days (query_period).
    • It focuses on activities that occurred within the last hour (query_frequency).

  2. Exclusion of Certain IPs:

    • It excludes IP addresses from specific watchlists, such as "IP-Vendors" and "IP-CorporateCollaborators", if they have certain notes like "[HomeTenant]", "[Proxy]", or "[Egress]".

  3. Data Filtering and Aggregation:

    • It retrieves sign-in logs where the IP address, service principal ID, and result type are not empty.
    • It summarizes the earliest occurrence of each unique combination of IP address, service principal ID, and result type.

  4. Activity Analysis:

    • It calculates the start and end times of activities, and separately tracks the start times of failed and successful sign-ins.
    • It compiles lists of service principal IDs and names for both failed and successful sign-ins, up to 100 entries each.

  5. Filtering Criteria:

    • It filters out cases where there is only one failed sign-in and no successful ones, or vice versa.
    • It excludes cases where there are no failed sign-ins, more than one successful sign-in, and the first successful sign-in occurred more than a day ago.
    • It removes cases where there is one failed and one successful sign-in from the same service principal.
    • It excludes activities from certain IP addresses based on the exclusion list and specific conditions related to the number of failed and successful sign-ins.

  6. Output:

    • The query projects a set of fields including start and end times, IP address, counts of failed and successful sign-ins, and lists of service principal IDs and names for both failed and successful sign-ins.

Overall, this query is used to identify unusual patterns in service principal sign-ins, potentially indicating security issues, while excluding known and trusted IP addresses.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: April 23, 2025

Tables

AADServicePrincipalSignInLogs

Keywords

AADServicePrincipalSignInLogsIPAddressServicePrincipalIdResultTypeServicePrincipalName

Operators

lettoscalarunionwhereisnotemptysummarizearg_minminmaxminifmake_set_ifagoextendarray_lengthtostringipv4_is_in_any_rangeproject

Actions