KQL Search

Query Details

HomeAI ToolsDevice Query

Multiple Activity From Anonymous IP Addresses

Query

https://learn.microsoft.com/en-us/defender-cloud-apps/anomaly-detection-policy
let query_frequency = 5m;
let query_period = 2d;
AADUserRiskEvents
| where TimeGenerated > ago(query_period)
| where Source == "MicrosoftCloudAppSecurity" and RiskEventType == "riskyIPAddress"
| summarize FirstTimeGenerated = min(TimeGenerated), arg_max(TimeGenerated, *) by Id
| where FirstTimeGenerated > ago(query_frequency)
| mv-apply Auxiliar = AdditionalInfo on (
    summarize AdditionalInfoBag = make_bag(bag_pack(tostring(Auxiliar["Key"]), Auxiliar["Value"]))
    )
| extend VendorOriginalId = tostring(split(tostring(AdditionalInfoBag["alertUrl"]), "/")[-1])
| project
    //TimeGenerated,
    ActivityDateTime,
    DetectedDateTime,
    Source,
    Activity,
    DetectionTimingType,
    UserDisplayName,
    UserPrincipalName,
    UserId,
    IpAddress,
    RequestId,
    CorrelationId,
    TokenIssuerType,
    RiskEventType,
    RiskDetail,
    RiskLevel,
    RiskState,
    AdditionalInfo,
    Id,
    VendorOriginalId
| extend AltAlertLink = strcat("https://entra.microsoft.com/#blade/Microsoft_AAD_IAM/RiskDetectionsBlade/riskState~/[]/userId/", UserId, "/riskLevel/[]/daysBack/90")// Someone wrote "90s" incorrectly in Defender XDR portal
| join kind=leftouter (
    SecurityAlert
    | where ProviderName == "MCAS" and ProductName == "Microsoft Cloud App Security"// and AlertType == "MCAS_ALERT_ANUBIS_???"
    | summarize arg_max(TimeGenerated, *) by VendorOriginalId
    | project
        AlertName,
        AlertSeverity,
        Description,
        AlertStatus = Status,
        Entities,
        ExtendedProperties,
        VendorName,
        ProviderName,
        ProductName,
        ProductComponentName,
        RemediationSteps,
        Tactics,
        Techniques,
        SubTechniques,
        VendorOriginalId,
        SystemAlertId,
        CompromisedEntity,
        AlertLink
    ) on VendorOriginalId
| extend
    AlertLink = coalesce(AlertLink, AltAlertLink),
    ExtendedPropertiesDynamic = todynamic(ExtendedProperties)
| as _Events
| lookup kind=leftouter (
    union SigninLogs, AADNonInteractiveUserSignInLogs
    | where TimeGenerated > ago(query_period)
    //| where RiskEventTypes_V2 has "riskyIPAddress"
    | where OriginalRequestId in (toscalar(_Events | summarize make_list(RequestId)))
    | extend TimeReceived = _TimeReceived
    | summarize arg_max(TimeReceived, *) by OriginalRequestId
    | invoke UnifySignInLogs()
    | project
        TimeGenerated,
        CreatedDateTime,
        Type,
        //UserDisplayName,
        //UserPrincipalName,
        //UserId,
        AlternateSignInName,
        SignInIdentifier,
        UserType,
        IPAddress,
        AutonomousSystemNumber,
        Location,
        NetworkLocationDetails,
        ResultType,
        ResultSignature,
        ResultDescription,
        ClientAppUsed,
        AppDisplayName,
        ResourceDisplayName,
        DeviceDetail,
        UserAgent,
        Status,
        MfaDetail,
        AuthenticationContextClassReferences,
        AuthenticationDetails,
        AuthenticationProcessingDetails,
        AuthenticationProtocol,
        AuthenticationRequirement,
        AuthenticationRequirementPolicies,
        SessionLifetimePolicies,
        //TokenIssuerType,
        IncomingTokenType,
        TokenProtectionStatusDetails,
        ConditionalAccessStatus,
        ConditionalAccessPolicies,
        SignInLogs_RiskDetail = RiskDetail,
        RiskEventTypes,
        RiskEventTypes_V2,
        RiskLevelAggregated,
        RiskLevelDuringSignIn,
        SignInLogs_RiskState = RiskState,
        HomeTenantId,
        ResourceTenantId,
        CrossTenantAccessType,
        AppId,
        ResourceIdentity,
        UniqueTokenIdentifier,
        SessionId,
        OriginalRequestId//,
        //CorrelationId
    ) on $left.RequestId == $right.OriginalRequestId
// | where case(
//     // AlertStatus == "Resolved" and tostring(todynamic(ExtendedProperties)["State"]) == "Closed", false,
//     SignInLogs_RiskState == "dismissed" and SignInLogs_RiskDetail == "aiConfirmedSigninSafe", false,
//     SignInLogs_RiskState == "remediated" and SignInLogs_RiskDetail == "userPassedMFADrivenByRiskBasedPolicy", false,
//     true
//     )
| project
    //TimeGenerated,
    ActivityDateTime,
    DetectedDateTime,
    Source,
    Activity,
    DetectionTimingType,
    UserDisplayName,
    UserPrincipalName,
    UserId,
    IpAddress,
    RequestId,
    CorrelationId,
    TokenIssuerType,
    RiskEventType,
    RiskDetail,
    RiskLevel,
    RiskState,
    AdditionalInfo,
    Id,
    AlertName,
    AlertSeverity,
    Description,
    AlertStatus,
    Entities,
    ExtendedProperties,
    VendorName,
    ProviderName,
    ProductName,
    ProductComponentName,
    RemediationSteps,
    Tactics,
    Techniques,
    SubTechniques,
    VendorOriginalId,
    SystemAlertId,
    CompromisedEntity,
    AlertLink,
    TimeGenerated,
    CreatedDateTime,
    Type,
    //UserDisplayName,
    //UserPrincipalName,
    //UserId,
    AlternateSignInName,
    SignInIdentifier,
    UserType,
    IPAddress,
    AutonomousSystemNumber,
    Location,
    NetworkLocationDetails,
    ResultType,
    ResultSignature,
    ResultDescription,
    ClientAppUsed,
    AppDisplayName,
    ResourceDisplayName,
    DeviceDetail,
    UserAgent,
    Status,
    MfaDetail,
    AuthenticationContextClassReferences,
    AuthenticationDetails,
    AuthenticationProcessingDetails,
    AuthenticationProtocol,
    AuthenticationRequirement,
    AuthenticationRequirementPolicies,
    SessionLifetimePolicies,
    //TokenIssuerType,
    IncomingTokenType,
    TokenProtectionStatusDetails,
    ConditionalAccessStatus,
    ConditionalAccessPolicies,
    SignInLogs_RiskDetail,
    RiskEventTypes,
    RiskEventTypes_V2,
    RiskLevelAggregated,
    RiskLevelDuringSignIn,
    SignInLogs_RiskState,
    HomeTenantId,
    ResourceTenantId,
    CrossTenantAccessType,
    AppId,
    ResourceIdentity,
    UniqueTokenIdentifier,
    SessionId//,
    //OriginalRequestId,
    //CorrelationId

Explanation

This KQL (Kusto Query Language) query is designed to analyze and correlate security events related to risky IP addresses from Azure Active Directory (AAD) and Microsoft Cloud App Security (MCAS). Here's a simplified breakdown of what the query does:

  1. Define Timeframes:

    • query_frequency is set to 5 minutes.
    • query_period is set to 2 days.

  2. Filter AAD User Risk Events:

    • It retrieves events from the AADUserRiskEvents table where the events are generated within the last 2 days.
    • It specifically looks for events sourced from "MicrosoftCloudAppSecurity" with a risk event type of "riskyIPAddress".

  3. Summarize Events:

    • For each event, it identifies the first occurrence and the most recent occurrence, grouping by the event ID.
    • Filters out events that first occurred more than 5 minutes ago.

  4. Extract Additional Information:

    • It processes additional information from the events to create a structured data bag.
    • Extracts a unique identifier (VendorOriginalId) from the alert URL.

  5. Project Relevant Fields:

    • Selects specific fields to include in the output, such as user details, risk information, and additional info.

  6. Create Alternative Alert Link:

    • Constructs an alternative alert link for each event.

  7. Join with Security Alerts:

    • Performs a left outer join with the SecurityAlert table to enrich the data with alert details from MCAS.
    • Projects additional alert-related fields.

  8. Join with Sign-in Logs:

    • Looks up sign-in logs from SigninLogs and AADNonInteractiveUserSignInLogs tables to correlate sign-in activities with the risk events.
    • Filters and projects relevant sign-in details.

  9. Final Projection:

    • The final output includes a comprehensive set of fields from both the risk events and the correlated sign-in logs, providing a detailed view of each incident.

Overall, this query is used to identify and analyze risky IP address activities, correlate them with security alerts and sign-in logs, and provide detailed insights into potential security threats.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: September 26, 2025

Tables

AADUserRiskEventsSecurityAlertSigninLogsAADNonInteractiveUserSignInLogs

Keywords

AADUserRiskEvents SecurityAlert SigninLogs AADNonInteractiveUserSignInLogs MicrosoftCloudAppSecurity RiskyIPAddress MCAS MicrosoftCloudAppSecurity User IpAddress Device App Resource Location Network Authentication Session Tenant Token Alert Risk SignIn Request Correlation Vendor Provider Product CompromisedEntity ExtendedProperties RemediationSteps Tactics Techniques SubTechniques ConditionalAccess Mfa UserAgent ClientApp AppDisplayName ResourceDisplayName DeviceDetail AutonomousSystemNumber NetworkLocationDetails ResultType ResultSignature ResultDescription AuthenticationContextClassReferences AuthenticationDetails AuthenticationProcessingDetails AuthenticationProtocol AuthenticationRequirement AuthenticationRequirementPolicies SessionLifetimePolicies TokenProtectionStatusDetails ConditionalAccessStatus ConditionalAccessPolicies RiskEventTypes RiskEventTypesV2 RiskLevelAggregated RiskLevelDuringSignIn HomeTenantId ResourceTenantId CrossTenantAccessType AppId ResourceIdentity UniqueTokenIdentifier SessionId

Operators

letagowheresummarizeminarg_maxmv-applymake_bagbag_packtostringsplitprojectextendstrcatjoincoalescetodynamicaslookupunionintoscalarsummarizeinvokeproject

Actions