KQL Search

let query_frequency = 1h;
let query_period = 7d;
let disallowed_risks = dynamic(["high"]);
let legacy_auth_protocols = dynamic(["Authenticated SMTP", "AutoDiscover", "Exchange ActiveSync", "Exchange Online PowerShell", "Exchange Web Services", "IMAP4", "MAPI Over HTTP", "Outlook Anywhere (RPC over HTTP)", "Outlook Service", "POP3", "Reporting Web Services", "Other clients"]);
let legacy_user_agents = dynamic(["BAV2ROPC", "CBAinPROD", "CBAinTAR", "MSRPC"]);
let _ExpectedLocations = toscalar(
    _GetWatchlist("Activity-ExpectedSignificantActivity")
    | where Activity == "CorporateGeolocation"
    | summarize make_list(Auxiliar)
);
let _UntrustedResultTypes = toscalar(
    _GetWatchlist("ResultType-SignInLogsErrorCodes")
    | where Notes has "[MFA]" and Notes has_any ("[Unconfigured]", "[NotCompliant]")
    | summarize make_list(ResultType)
);
let _ExpectedIPRanges = dynamic([]);
let _AtypicaltravelAlerts = materialize(
    SecurityAlert
    | where TimeGenerated > ago(query_period)
    | where ProductName has "Azure Active Directory Identity Protection" and ProviderName != "ASI Scheduled Alerts" and AlertName has "Atypical travel"
    | extend ExtendedProperties = todynamic(ExtendedProperties)
    | extend OriginalRequestId = tostring(ExtendedProperties["Request Id"])
    | 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"]))
    | extend
        UserId = tostring(todynamic(Entities)[0]["AadUserId"]),
        ["Alert_Previous IP Address"] = column_ifexists("Alert_Previous IP Address", ""),
        ["Alert_Current IP Address"] = column_ifexists("Alert_Current IP Address", "")
    | summarize minTimeGenerated = min(Alert_TimeGenerated), arg_max(Alert_TimeGenerated, *) by UserId, AlertName, AlertSeverity, ["Alert_Previous IP Address"], ["Alert_Current IP Address"]
    | where minTimeGenerated > ago(query_frequency)
    | project-away minTimeGenerated
    | extend
        ["Alert_Previous Location"] = column_ifexists("Alert_Previous Location", ""),
        ["Alert_Current Location"] = column_ifexists("Alert_Current Location", "")
    | extend ["Alert_Current Country"] = extract(@"([A-Z]{2})$", 1, ["Alert_Current Location"])
    | lookup kind=leftouter (
        AADUserRiskEvents
        | where TimeGenerated > ago(query_period)
        | where RiskEventType == "unlikelyTravel"
        | distinct RequestId, UserId, IpAddress, CorrelationId
        | project-rename OriginalRequestId = RequestId
    ) on OriginalRequestId, UserId, $left.["Alert_Current IP Address"] == $right.IpAddress
    | as _Alerts
    | lookup kind=leftouter (
        union
            (SigninLogs
            | where TimeGenerated > ago(query_period)
            | where CorrelationId in (toscalar(_Alerts | summarize make_list(CorrelationId))) and RiskState == "atRisk" and RiskEventTypes has "unlikelyTravel"
            | extend
                DeviceDetail = tostring(DeviceDetail),
                TimeReceived = _TimeReceived
            ),
            (AADNonInteractiveUserSignInLogs
            | where TimeGenerated > ago(query_period)
            | where CorrelationId in (toscalar(_Alerts | summarize make_list(CorrelationId))) and RiskState == "atRisk" and RiskEventTypes has "unlikelyTravel"
            | extend TimeReceived = _TimeReceived
            )
        | summarize
            arg_max(TimeReceived, *),
            MFASuccess_TimeGenerated = minif(TimeGenerated, ConditionalAccessStatus == "success" and AuthenticationRequirement == "multiFactorAuthentication")
            by CorrelationId
        | project
            MFASuccess_TimeGenerated,
            TimeGenerated,
            Type,
            UserPrincipalName,
            UserDisplayName,
            IPAddress,
            Location,
            ResultType,
            ResultDescription,
            ClientAppUsed,
            AppDisplayName,
            ResourceDisplayName,
            DeviceDetail,
            UserAgent,
            AuthenticationDetails,
            RiskEventTypes,
            RiskLevelDuringSignIn,
            RiskLevelAggregated,
            //UserId,
            //OriginalRequestId,
            CorrelationId
    ) on CorrelationId
);
let _UntrustedUserIds = toscalar(
    union
        (_AtypicaltravelAlerts
        | join kind=innerunique (
            SigninLogs
            | where TimeGenerated > ago(query_period)
            | where ResultType in (_UntrustedResultTypes)
            | project UserId
            )
            on UserId
        ),
        (AuthenticationMethodChanges(query_period, toscalar(_AtypicaltravelAlerts | summarize make_set(UserId)))
        )
    | summarize make_set(UserId)
);
_AtypicaltravelAlerts
| extend
    Alert_State = column_ifexists("Alert_State", ""),
    ["Alert_Detection Subcategory"] = column_ifexists("Alert_Detection Subcategory", ""),
    RecentlyConfiguredMFA = UserId in (_UntrustedUserIds)
| extend
    BenignAlert = case(
        // Remove cases where Identity Protection considers the alert solved and the account had MFA configured long ago
        (Status == "Resolved" or Alert_State == "Closed") and not(RecentlyConfiguredMFA), true,
        // // Remove cases where MFA was used successfully and the account had MFA configured long ago (this condition should not be needed with the above one)
        // isnotempty(MFASuccess_TimeGenerated) and not(RecentlyConfiguredMFA), true,
        // Remove cases from expected locations, depending on severity and legacy protocols
        ["Alert_Current Country"] == ["Alert_Previous Location"] and ["Alert_Current Country"] in (_ExpectedLocations) and ["Alert_Previous Location"] in (_ExpectedLocations) and AlertSeverity in ("Medium", "Low") and not(ClientAppUsed in (legacy_auth_protocols) or UserAgent in (legacy_user_agents)), true,
        // Remove cases from expected IP addresses, depending on severity and legacy protocols
        (isnotempty(parse_ipv4(["Alert_Previous IP Address"])) and ipv4_is_in_any_range(["Alert_Previous IP Address"], _ExpectedIPRanges)) and ["Alert_Current Country"] in (_ExpectedLocations) and AlertSeverity in ("Medium", "Low") and not(ClientAppUsed in (legacy_auth_protocols) or UserAgent in (legacy_user_agents)), true,
        (isnotempty(parse_ipv4(["Alert_Current IP Address"])) and ipv4_is_in_any_range(["Alert_Current IP Address"], _ExpectedIPRanges)) and ["Alert_Previous Location"] in (_ExpectedLocations) and AlertSeverity in ("Medium", "Low"), 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 AlertSeverity != "High")
| project-rename
    Alert_PreviousIPAddress = ['Alert_Previous IP Address'],
    Alert_CurrentIPAddress = ['Alert_Current IP Address']
| 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