KQL Search

// This rule checks other security alerts related to password spray activity, and gathers the IP addresses in the Entities of these alerts, that are presumably malicious.
// This rule detects if these IP addresses have successful (or partially successful) authentication activity, before and after the password spray occurred.
// This rule excludes the accounts that were already identified as compromised in the last day in the password spray alerts, to reduce noise.
let query_frequency = 1h;
let query_period = 14d;
let exclude_accounts_period = 1d;
let _MonitoredRuleIds = toscalar(
    _GetWatchlist('AlertName-MonitoredDetections')
    | where Notes has "[PasswordSpray]"
    | summarize make_list(AnalyticsId)
);
let _EmailAddressRegex = toscalar(
    _GetWatchlist('RegEx-SingleRegularExpressions')
    | where UseCase == "EmailAddress"
    | project RegEx
);
let _ExternalEmailAddressRegex = toscalar(
    _GetWatchlist('RegEx-SingleRegularExpressions')
    | where UseCase == "ExternalEmailAddress"
    | project RegEx
);
let _ExcludedResultTypes = array_concat(toscalar(
    _GetWatchlist('ResultType-SignInLogsErrorCodes')
    | where Notes has "[Failure]" and not(Notes has_any ("[Expired]", "[Success]")) and isnotempty(ResultDescription)
    | summarize make_list(ResultType)
    ),
    pack_array("700082")
);
let _AlertsSignInLogsMatch = (alerts_start: datetime, alerts_end: datetime, signinlogs_start: datetime, signinlogs_end: datetime) {
    let _Alerts =
        SecurityAlert
        // Take all the available alerts, we will filter them by "alerts_start" and "alerts_end" later
        | where TimeGenerated > ago(query_period)
        | where AlertType has_any (_MonitoredRuleIds)
        | mv-expand Entity = todynamic(Entities)
        | project TimeGenerated, Entity
    ;
    let _AlertIPAddresses =
        _Alerts
        | where Entity["Type"] == "ip"
        | extend IPAddress = strcat(tostring(Entity["Address"]), iff(bag_has_key(Entity, "AddressScope"), strcat("/", tostring(Entity["AddressScope"])), ""))
        | summarize minTimeGenerated = min(TimeGenerated) by IPAddress
        // For the specified alert time period, IP addresses should be checked only if the first time they were observed was during this period (when the alert time period is 14 days, all IP addresses will be checked)
        | where minTimeGenerated between(alerts_start .. alerts_end)
    ;
    let _AlertIPv4Addresses = toscalar(
        _AlertIPAddresses
        | where not(isempty(parse_ipv4(IPAddress)) or ipv4_is_private(IPAddress))
        | summarize make_list(IPAddress)
    );
    let _AlertIPv6Addresses = toscalar(
        _AlertIPAddresses
        | where not(isnotempty(parse_ipv4(IPAddress)) or isempty(parse_ipv6(IPAddress)))
        | summarize make_list(IPAddress)
    );
    // Monitored password spray alerts should contain account entities, but only if these were compromised
    // This function should return the recently compromised accounts (in the last day or a longer period) so they can be excluded
    let _AlertAccounts = toscalar(
        _Alerts
        | where TimeGenerated between(alerts_start .. alerts_end) and TimeGenerated > ago(exclude_accounts_period)
        | extend
            EntityName = tostring(Entity.Name),
            EntityUPNSuffix = tostring(Entity.UPNSuffix)
        | extend EntityUPN = tolower(iff(isnotempty(EntityUPNSuffix), strcat(EntityName, "@", EntityUPNSuffix), ""))
        | as _Info
        | extend EntityEmail = todynamic(dynamic_to_json(extract_all(_EmailAddressRegex, dynamic([1]), tolower(strcat(tostring(toscalar(_Info | summarize make_set_if(EntityUPN, isnotempty(EntityUPN)))), Entity)))))
        | mv-expand EntityEmail
        | extend EntityEmail = tostring(EntityEmail[0])
        | where isnotempty(EntityEmail)
        | extend EntityEmail = case(
            EntityEmail has "#EXT#", replace_regex(EntityEmail, _ExternalEmailAddressRegex, @"\2@\3"),
            EntityEmail startswith "live.com#" or EntityEmail startswith "guest#", replace_regex(EntityEmail, strcat(@"(?:live\.com#|guest#)", _EmailAddressRegex), @"\2@\3"),
            EntityEmail
            )
        | summarize make_list(EntityEmail)
    );
    union
        (ADFSSignInLogs
        | where ingestion_time() between (signinlogs_start .. signinlogs_end)
        | where
            (
                (array_length(_AlertIPv4Addresses) > 0
                and not(isempty(parse_ipv4(IPAddress)) or ipv4_is_private(IPAddress))
                and ipv4_is_in_any_range(IPAddress, _AlertIPv4Addresses))
                or
                (array_length(_AlertIPv6Addresses) > 0
                and not(isnotempty(parse_ipv4(IPAddress)) or isempty(parse_ipv6(IPAddress)))
                and ipv6_is_in_any_range(IPAddress, _AlertIPv6Addresses))
            )
            and not(ResultType in (_ExcludedResultTypes))
            //and not(todynamic(AuthenticationDetails)[0].authenticationMethod == "Integrated Windows Authentication")
        ),
        (SigninLogs
        | where TimeGenerated between (signinlogs_start .. signinlogs_end)
        | where
            (
                (array_length(_AlertIPv4Addresses) > 0
                and not(isempty(parse_ipv4(IPAddress)) or ipv4_is_private(IPAddress))
                and ipv4_is_in_any_range(IPAddress, _AlertIPv4Addresses))
                or
                (array_length(_AlertIPv6Addresses) > 0
                and not(isnotempty(parse_ipv4(IPAddress)) or isempty(parse_ipv6(IPAddress)))
                and ipv6_is_in_any_range(IPAddress, _AlertIPv6Addresses))
            )
            and not(ResultType in (_ExcludedResultTypes))
        | extend
            DeviceDetail = tostring(DeviceDetail),
            ConditionalAccessPolicies = tostring(ConditionalAccessPolicies)
        ),
        (AADNonInteractiveUserSignInLogs
        | where TimeGenerated between (signinlogs_start .. signinlogs_end)
        | where
            (
                (array_length(_AlertIPv4Addresses) > 0
                and not(isempty(parse_ipv4(IPAddress)) or ipv4_is_private(IPAddress))
                and ipv4_is_in_any_range(IPAddress, _AlertIPv4Addresses))
                or
                (array_length(_AlertIPv6Addresses) > 0
                and not(isnotempty(parse_ipv4(IPAddress)) or isempty(parse_ipv6(IPAddress)))
                and ipv6_is_in_any_range(IPAddress, _AlertIPv6Addresses))
            )
            and not(ResultType in (_ExcludedResultTypes))
        )
    // Exclude accounts compromised in the last day
    | where not(UserPrincipalName in (_AlertAccounts))
    | summarize arg_min(TimeGenerated, *) by IPAddress, UserId, ResultType
};
union
    // Past alerts (from 14 days ago until 1 hour ago)
    _AlertsSignInLogsMatch(ago(query_period), ago(query_frequency), ago(query_frequency), now()),
    // Current alerts (from 1 hour ago until now)
    _AlertsSignInLogsMatch(ago(query_frequency), now(), ago(query_period), now())
| project
    TimeGenerated,
    Type,
    UserPrincipalName,
    UserDisplayName,
    IPAddress,
    Location,
    ResultType,
    ResultDescription,
    ClientAppUsed,
    AppDisplayName,
    ResourceDisplayName,
    DeviceDetail,
    UserAgent,
    AuthenticationDetails,
    ConditionalAccessPolicies,
    RiskState,
    RiskEventTypes,
    RiskLevelDuringSignIn,
    RiskLevelAggregated,
    UserId,
    OriginalRequestId,
    CorrelationId