KQL Search

Query Details

AAD Service Principal Risk Events Service Principal At Risk

Query

let query_frequency = 1h;
let query_period = 14d;
AADServicePrincipalRiskEvents
| where TimeGenerated > ago(query_period)
| summarize
    ConfirmedTimeGenerated = maxif(TimeGenerated, RiskState == "confirmedCompromised"),
    arg_min(TimeGenerated, *)
    by Id
| where case(
    isnotempty(ConfirmedTimeGenerated), false,
    isempty(ActivityDateTime) and isempty(DetectedDateTime) and isnotempty(LastUpdatedDateTime) and RiskState == "dismissed", false,
    //RiskDetail == "aiConfirmedSigninSafe" and RiskState == "dismissed", false,
    true
    )
| summarize arg_min(TimeGenerated, *) by ServicePrincipalId, RiskEventType, DetectionTimingType, RiskLevel, RiskState
| where TimeGenerated > ago(query_frequency)
| extend AlertSeverity = strcat(toupper(substring(RiskLevel, 0, 1)), substring(RiskLevel, 1))
| project
    TimeGenerated,
    ServicePrincipalDisplayName,
    IpAddress,
    Location,
    OperationName,
    RiskEventType,
    RiskLevel,
    DetectionTimingType,
    RiskState,
    RiskDetail,
    AdditionalInfo,
    Activity,
    ServicePrincipalId,
    AppId,
    CorrelationId,
    RequestId,
    ActivityDateTime,
    DetectedDateTime,
    LastUpdatedDateTime,
    Id,
    AlertSeverity

Explanation

This KQL (Kusto Query Language) query is designed to analyze risk events related to Azure Active Directory (AAD) service principals over a specified period. Here's a simplified breakdown of what the query does:

  1. Define Timeframes:

    • query_frequency is set to 1 hour, and query_period is set to 14 days. These are used to filter the data based on time.

  2. Filter Recent Events:

    • The query retrieves events from the AADServicePrincipalRiskEvents table that have occurred within the last 14 days.

  3. Summarize Events:

    • It summarizes the events by Id, capturing the latest time an event was confirmed as compromised (ConfirmedTimeGenerated) and the earliest occurrence of each event.

  4. Filter Out Certain Events:

    • The query excludes events that meet specific conditions:
      • If the event has a confirmed compromised time, it is excluded.
      • If both ActivityDateTime and DetectedDateTime are empty, but LastUpdatedDateTime is not empty, and the risk state is "dismissed", it is excluded.
      • Other events are included.

  5. Further Summarization:

    • It further summarizes the data by service principal ID and other risk-related attributes, ensuring only the earliest occurrence of each combination is kept.

  6. Filter Recent Summarized Events:

    • It filters these summarized events to include only those that occurred within the last hour.

  7. Add Alert Severity:

    • It creates a new field AlertSeverity by capitalizing the first letter of the RiskLevel and appending the rest of the string.

  8. Select Relevant Columns:

    • Finally, it projects (selects) a set of relevant columns to be displayed in the results, including details like time generated, service principal display name, IP address, location, risk details, and more.

In essence, this query identifies and processes recent risk events related to AAD service principals, focusing on those that are not confirmed as compromised or dismissed under certain conditions, and presents them with relevant details and a calculated alert severity.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: August 21, 2025

Tables

AADServicePrincipalRiskEvents

Keywords

AADServicePrincipalRiskEvents

Operators

letagowheresummarizemaxifarg_minbycaseisnotemptyisemptyextendstrcattouppersubstringproject

Actions

GitHub