from logs-o365.audit-*
| mv_expand event.category
| eval
    Esql.time_window_date_trunc = date_trunc(5 minutes, @timestamp),
    Esql_priv.o365_audit_UserId_lower = to_lower(o365.audit.UserId),
    Esql.o365_audit_LogonError = o365.audit.LogonError,
    Esql.o365_audit_ExtendedProperties_RequestType_lower = to_lower(o365.audit.ExtendedProperties.RequestType)
| where
    event.dataset == "o365.audit" and
    event.category == "authentication" and
    event.provider in ("AzureActiveDirectory", "Exchange") and
    event.action in ("UserLoginFailed", "PasswordLogonInitialAuthUsingPassword") and
    Esql.o365_audit_ExtendedProperties_RequestType_lower rlike "(oauth.*||.*login.*)" and
    Esql.o365_audit_LogonError != "IdsLocked" and
    Esql.o365_audit_LogonError not in (
        "EntitlementGrantsNotFound",
        "UserStrongAuthEnrollmentRequired",
        "UserStrongAuthClientAuthNRequired",
        "InvalidReplyTo",
        "SsoArtifactExpiredDueToConditionalAccess",
        "PasswordResetRegistrationRequiredInterrupt",
        "SsoUserAccountNotFoundInResourceTenant",
        "UserStrongAuthExpired",
        "CmsiInterrupt"
    ) and
    Esql_priv.o365_audit_UserId_lower != "not available" and
    o365.audit.Target.Type in ("0", "2", "6", "10")
| stats
    Esql.o365_audit_UserId_lower_count_distinct = count_distinct(Esql_priv.o365_audit_UserId_lower),
    Esql_priv.o365_audit_UserId_lower_values = values(Esql_priv.o365_audit_UserId_lower),
    Esql.o365_audit_LogonError_values = values(Esql.o365_audit_LogonError),
    Esql.o365_audit_LogonError_count_distinct = count_distinct(Esql.o365_audit_LogonError),
    Esql.o365_audit_ExtendedProperties_RequestType_values = values(Esql.o365_audit_ExtendedProperties_RequestType_lower),
    Esql.source_ip_values = values(source.ip),
    Esql.source_ip_count_distinct = count_distinct(source.ip),
    Esql.source_as_organization_name_values = values(source.`as`.organization.name),
    Esql.source_geo_country_name_values = values(source.geo.country_name),
    Esql.source_geo_country_name_count_distinct = count_distinct(source.geo.country_name),
    Esql.source_as_organization_name_count_distinct = count_distinct(source.`as`.organization.name),
    Esql.timestamp_first_seen = min(@timestamp),
    Esql.timestamp_last_seen = max(@timestamp),
    Esql.event_count = count(*)
  by Esql.time_window_date_trunc
| eval
    Esql.event_duration_seconds = date_diff("seconds", Esql.timestamp_first_seen, Esql.timestamp_last_seen),
    Esql.brute_force_type = case(
        Esql.o365_audit_UserId_lower_count_distinct >= 15 and Esql.o365_audit_LogonError_count_distinct == 1 and Esql.event_count >= 10 and Esql.event_duration_seconds <= 1800, "password_spraying",
        Esql.o365_audit_UserId_lower_count_distinct >= 8 and Esql.event_count >= 15 and Esql.o365_audit_LogonError_count_distinct <= 3 and Esql.source_ip_count_distinct <= 5 and Esql.event_duration_seconds <= 600, "credential_stuffing",
        Esql.o365_audit_UserId_lower_count_distinct == 1 and Esql.o365_audit_LogonError_count_distinct == 1 and Esql.event_count >= 20 and Esql.event_duration_seconds <= 300, "password_guessing",
        "other"
    )
| keep
    Esql.time_window_date_trunc,
    Esql.o365_audit_UserId_lower_count_distinct,
    Esql_priv.o365_audit_UserId_lower_values,
    Esql.o365_audit_LogonError_values,
    Esql.o365_audit_LogonError_count_distinct,
    Esql.o365_audit_ExtendedProperties_RequestType_values,
    Esql.source_ip_values,
    Esql.source_ip_count_distinct,
    Esql.source_as_organization_name_values,
    Esql.source_geo_country_name_values,
    Esql.source_geo_country_name_count_distinct,
    Esql.source_as_organization_name_count_distinct,
    Esql.timestamp_first_seen,
    Esql.timestamp_last_seen,
    Esql.event_duration_seconds,
    Esql.event_count,
    Esql.brute_force_type
| where Esql.brute_force_type != "other"