✄𐘗
text code block:
✄𐘗FROM logs-okta.system-*

// Filter to relevant event types for AiTM detection
| WHERE
    okta.event_type IN ("user.session.start", "policy.evaluate_sign_on", "user.authentication.sso") AND
    okta.authentication_context.root_session_id IS NOT NULL AND
    okta.actor.alternate_id != "system@okta.com"

// Create event type flags
| EVAL Esql.is_session_start = okta.event_type == "user.session.start"
| EVAL Esql.is_policy_eval = okta.event_type == "policy.evaluate_sign_on"
| EVAL Esql.is_sso = okta.event_type == "user.authentication.sso"
| EVAL Esql.is_replay_event = Esql.is_policy_eval OR Esql.is_sso

// Flag suspicious non-browser user agents
| EVAL Esql.is_suspicious_ua =
    user_agent.original LIKE "python-requests*" OR
    user_agent.original LIKE "curl/*" OR
    user_agent.original LIKE "httpx*" OR
    user_agent.original LIKE "aiohttp*" OR
    user_agent.original LIKE "Go-http-client*" OR
    user_agent.original LIKE "*Headless*" OR
    user_agent.original LIKE "Java/*" OR
    user_agent.original LIKE "okhttp*"

// Aggregate by session
| STATS
    Esql.session_start_count = SUM(CASE(Esql.is_session_start, 1, 0)),
    Esql.replay_event_count = SUM(CASE(Esql.is_replay_event, 1, 0)),
    Esql.session_start_time = MIN(CASE(Esql.is_session_start, @timestamp, null)),
    Esql.first_replay_time = MIN(CASE(Esql.is_replay_event, @timestamp, null)),
    Esql.last_replay_time = MAX(CASE(Esql.is_replay_event, @timestamp, null)),
    Esql.session_start_ip = MAX(CASE(Esql.is_session_start, okta.client.ip, null)),
    Esql.session_start_ua = MAX(CASE(Esql.is_session_start, user_agent.original, null)),
    Esql.suspicious_ua_count = SUM(CASE(Esql.is_suspicious_ua, 1, 0)),
    Esql.okta_client_ip_count_distinct = COUNT_DISTINCT(okta.client.ip),
    Esql.user_agent_count_distinct = COUNT_DISTINCT(user_agent.original),
    Esql.okta_client_ip_values = VALUES(okta.client.ip),
    Esql.user_agent_values = VALUES(user_agent.original),
    Esql.okta_event_type_values = VALUES(okta.event_type),
    Esql.okta_outcome_result_values = VALUES(okta.outcome.result),
    Esql.source_geo_country_name_values = VALUES(source.geo.country_name),
    Esql.source_geo_city_name_values = VALUES(source.geo.city_name),
    Esql.okta_debug_context_debug_data_risk_level_values = VALUES(okta.debug_context.debug_data.risk_level),
    Esql.okta_debug_context_debug_data_risk_reasons_values = VALUES(okta.debug_context.debug_data.risk_reasons)
  BY okta.authentication_context.root_session_id, okta.actor.alternate_id

// Detection conditions
| WHERE
    Esql.session_start_count >= 1
    AND Esql.replay_event_count >= 1
    AND Esql.first_replay_time > Esql.session_start_time
    AND (
            (
                Esql.okta_client_ip_count_distinct > 1 OR Esql.user_agent_count_distinct > 1
            ) AND Esql.suspicious_ua_count > 0
        )

| SORT Esql.session_start_time DESC
| KEEP Esql.*, okta.authentication_context.root_session_id, okta.actor.alternate_id