FROM logs-azure.signinlogs*

| EVAL
    time_window = DATE_TRUNC(15 minutes, @timestamp),
    user_id = TO_LOWER(azure.signinlogs.properties.user_principal_name),
    ip = source.ip,
    login_error = azure.signinlogs.result_description,
    error_code = azure.signinlogs.properties.status.error_code,
    request_type = TO_LOWER(azure.signinlogs.properties.incoming_token_type),
    app_name = TO_LOWER(azure.signinlogs.properties.app_display_name),
    asn_org = source.`as`.organization.name,
    country = source.geo.country_name,
    user_agent = user_agent.original,
    event_time = @timestamp

| WHERE event.dataset == "azure.signinlogs"
    AND event.category == "authentication"
    AND azure.signinlogs.category IN ("NonInteractiveUserSignInLogs", "SignInLogs")
    AND azure.signinlogs.properties.resource_display_name RLIKE "(.*)365|SharePoint|Exchange|Teams|Office(.*)"
    AND event.outcome == "failure"
    AND error_code != 50053
    AND azure.signinlogs.properties.status.error_code IN (
        50034,  // UserAccountNotFound
        50126,  // InvalidUsernameOrPassword
        50055,  // PasswordExpired
        50056,  // InvalidPassword
        50057,  // UserDisabled
        50064,  // CredentialValidationFailure
        50076,  // MFARequiredButNotPassed
        50079,  // MFARegistrationRequired
        50105,  // EntitlementGrantsNotFound
        70000,  // InvalidGrant
        70008,  // ExpiredOrRevokedRefreshToken
        70043,  // BadTokenDueToSignInFrequency
        80002,  // OnPremisePasswordValidatorRequestTimedOut
        80005,  // OnPremisePasswordValidatorUnpredictableWebException
        50144,  // InvalidPasswordExpiredOnPremPassword
        50135,  // PasswordChangeCompromisedPassword
        50142,  // PasswordChangeRequiredConditionalAccess
        120000, // PasswordChangeIncorrectCurrentPassword
        120002, // PasswordChangeInvalidNewPasswordWeak
        120020  // PasswordChangeFailure
    )
    AND user_id IS NOT NULL AND user_id != ""
    AND user_agent != "Mozilla/5.0 (compatible; MSAL 1.0) PKeyAuth/1.0"

| STATS
    authentication_requirement = VALUES(azure.signinlogs.properties.authentication_requirement),
    client_app_id = VALUES(azure.signinlogs.properties.app_id),
    client_app_display_name = VALUES(azure.signinlogs.properties.app_display_name),
    target_resource_id = VALUES(azure.signinlogs.properties.resource_id),
    target_resource_display_name = VALUES(azure.signinlogs.properties.resource_display_name),
    conditional_access_status = VALUES(azure.signinlogs.properties.conditional_access_status),
    device_detail_browser = VALUES(azure.signinlogs.properties.device_detail.browser),
    device_detail_device_id = VALUES(azure.signinlogs.properties.device_detail.device_id),
    device_detail_operating_system = VALUES(azure.signinlogs.properties.device_detail.operating_system),
    incoming_token_type = VALUES(azure.signinlogs.properties.incoming_token_type),
    risk_state = VALUES(azure.signinlogs.properties.risk_state),
    session_id = VALUES(azure.signinlogs.properties.session_id),
    user_id = VALUES(azure.signinlogs.properties.user_id),
    user_principal_name = VALUES(azure.signinlogs.properties.user_principal_name),
    result_description = VALUES(azure.signinlogs.result_description),
    result_signature = VALUES(azure.signinlogs.result_signature),
    result_type = VALUES(azure.signinlogs.result_type),

    unique_users = COUNT_DISTINCT(user_id),
    user_id_list = VALUES(user_id),
    login_errors = VALUES(login_error),
    unique_login_errors = COUNT_DISTINCT(login_error),
    error_codes = VALUES(error_code),
    unique_error_codes = COUNT_DISTINCT(error_code),
    request_types = VALUES(request_type),
    app_names = VALUES(app_name),
    ip_list = VALUES(ip),
    unique_ips = COUNT_DISTINCT(ip),
    source_orgs = VALUES(asn_org),
    countries = VALUES(country),
    unique_country_count = COUNT_DISTINCT(country),
    unique_asn_orgs = COUNT_DISTINCT(asn_org),
    first_seen = MIN(event_time),
    last_seen = MAX(event_time),
    total_attempts = COUNT()
BY time_window

| EVAL
    duration_seconds = DATE_DIFF("seconds", first_seen, last_seen),
    bf_type = CASE(
        // Many users, relatively few distinct login errors, distributed over multiple IPs (but not too many),
        // and happens quickly. Often bots using leaked credentials.
        unique_users >= 10 AND total_attempts >= 30 AND unique_login_errors <= 3
            AND unique_ips >= 5
            AND duration_seconds <= 600
            AND unique_users > unique_ips,
        "credential_stuffing",

        // One password against many users. Single error (e.g., "InvalidPassword"), not necessarily fast.
        unique_users >= 15 AND unique_login_errors == 1 AND total_attempts >= 15 AND duration_seconds <= 1800,
        "password_spraying",

        // One user targeted repeatedly (same error), OR extremely noisy pattern from many IPs.
        (unique_users == 1 AND unique_login_errors == 1 AND total_attempts >= 30 AND duration_seconds <= 300)
            OR (unique_users <= 3 AND unique_ips > 30 AND total_attempts >= 100),
        "password_guessing",

        // everything else
        "other"
    )

| KEEP
    time_window, bf_type, duration_seconds, total_attempts, first_seen, last_seen,
    unique_users, user_id_list, login_errors, unique_login_errors,
    unique_error_codes, error_codes, request_types, app_names,
    ip_list, unique_ips, source_orgs, countries,
    unique_country_count, unique_asn_orgs,
    authentication_requirement, client_app_id, client_app_display_name,
    target_resource_id, target_resource_display_name, conditional_access_status,
    device_detail_browser, device_detail_device_id, device_detail_operating_system,
    incoming_token_type, risk_state, session_id, user_id,
    user_principal_name, result_description, result_signature, result_type

| WHERE bf_type != "other"