KQL Search

let query_frequency = 5m;
let query_period = 2d;
let disallowed_risks = dynamic(["high"]);
SecurityAlert
| where TimeGenerated > ago(query_period)
| where ProductName has "Azure Active Directory Identity Protection" and ProviderName != "ASI Scheduled Alerts" and AlertName has "Malicious IP address"
| extend ExtendedProperties = todynamic(ExtendedProperties)
| extend OriginalRequestId = tostring(ExtendedProperties["Request Id"])
| summarize minTimeGenerated = min(TimeGenerated), arg_max(TimeGenerated, *) by OriginalRequestId, AlertName, AlertSeverity
| where minTimeGenerated > ago(query_frequency)
| project
    Alert_TimeGenerated = TimeGenerated,
    ProductName,
    AlertName,
    Description,
    AlertSeverity,
    Status,
    Tactics,
    Techniques,
    Entities,
    ExtendedProperties,
    OriginalRequestId
| evaluate bag_unpack(ExtendedProperties, OutputColumnPrefix="Alert_", ignoredProperties=dynamic(["Alert generation status", "ProcessedBySentinel", "Request Id", "Tenant Login Source", "User Account", "User Name"]))
| as _Alerts
| lookup kind=leftouter (
    union
        (SigninLogs
        | where TimeGenerated > ago(query_period)
        | where OriginalRequestId in (toscalar(_Alerts | summarize make_list(OriginalRequestId))) and RiskState != "none"
        | extend
            DeviceDetail = tostring(DeviceDetail),
            TimeReceived = _TimeReceived
        ),
        (AADNonInteractiveUserSignInLogs
        | where TimeGenerated > ago(query_period)
        | where OriginalRequestId in (toscalar(_Alerts | summarize make_list(OriginalRequestId))) and RiskState != "none"
        | extend TimeReceived = _TimeReceived
        )
    | summarize arg_max(TimeReceived, *) by OriginalRequestId
    | project
        TimeGenerated,
        Type,
        UserPrincipalName,
        UserDisplayName,
        IPAddress,
        Location,
        ResultType,
        ResultDescription,
        ClientAppUsed,
        AppDisplayName,
        ResourceDisplayName,
        DeviceDetail,
        UserAgent,
        AuthenticationDetails,
        RiskEventTypes,
        RiskLevelDuringSignIn,
        RiskLevelAggregated,
        UserId,
        OriginalRequestId,
        CorrelationId
    ) on OriginalRequestId
| extend
    Alert_State = column_ifexists("Alert_State", ""),
    ["Alert_Detection Subcategory"] = column_ifexists("Alert_Detection Subcategory", "")
| extend
    BenignAlert = case(
        // Remove cases where Identity Protection considers the alert solved
        Status == "Resolved" or Alert_State == "Closed", true,
        false
    ),
    // If a user is put at high risk, the alert severity should be High and the incident name should have the string "User at risk"
    AlertSeverity = case(
        RiskLevelAggregated in (disallowed_risks) or RiskLevelDuringSignIn in (disallowed_risks), "High",
        AlertSeverity
    ),
    IncidentName = case(
        RiskLevelAggregated in (disallowed_risks), strcat(AlertName, " - User at risk"),
        AlertName
    )
// Remove benign cases where alert severity is not High
// | where not(BenignAlert and not(AlertSeverity in ("High")))
| project-reorder
    TimeGenerated,
    ProductName,
    AlertName,
    Description,
    Alert_*,
    Type,
    UserPrincipalName,
    UserDisplayName,
    IPAddress,
    Location,
    ResultType,
    ResultDescription,
    ClientAppUsed,
    AppDisplayName,
    ResourceDisplayName,
    DeviceDetail,
    UserAgent,
    AuthenticationDetails,
    AlertSeverity,
    RiskEventTypes,
    RiskLevelDuringSignIn,
    RiskLevelAggregated,
    Entities,
    UserId,
    OriginalRequestId,
    CorrelationId