✄𐘗
text code block:
✄𐘗// successful Google Workspace logins with country + region populated.
from logs-google_workspace.login-*
| where event.dataset == "google_workspace.login"
    and event.action == "login_success"
    and event.outcome == "success"
    and user.email is not null
    and source.geo.location is not null
    and source.geo.country_name is not null
    and source.geo.region_name is not null
| eval Esql.source_geo_lat = st_y(source.geo.location),
       Esql.source_geo_lon = st_x(source.geo.location)

// collapse each (user, country, region) into one centroid + the actual lat/lon
// of the first and last event in that region. FIRST/LAST lock coords to the
// timestamp ordering so we can later build the honest event pair.
| stats
    Esql.region_centroid_lat = avg(Esql.source_geo_lat),
    Esql.region_centroid_lon = avg(Esql.source_geo_lon),
    Esql.region_first_lat    = first(Esql.source_geo_lat, @timestamp),
    Esql.region_first_lon    = first(Esql.source_geo_lon, @timestamp),
    Esql.region_last_lat     = last(Esql.source_geo_lat,  @timestamp),
    Esql.region_last_lon     = last(Esql.source_geo_lon,  @timestamp),
    Esql.region_first_seen   = min(@timestamp),
    Esql.region_last_seen    = max(@timestamp),
    Esql.region_event_count  = count(*),
    Esql.region_city_values   = values(source.geo.city_name),
    Esql.region_asn_values    = values(source.`as`.organization.name),
    Esql.region_ip_values     = values(source.ip)
  by user.email,
     source.geo.country_name,
     source.geo.region_name

// roll up to the user. two parallel measurements:
// bbox: corners over region centroids.
// honest: real coords at the user's actual first and last events (nested FIRST/LAST).
| stats
    Esql.min_lat = min(Esql.region_centroid_lat),
    Esql.max_lat = max(Esql.region_centroid_lat),
    Esql.min_lon = min(Esql.region_centroid_lon),
    Esql.max_lon = max(Esql.region_centroid_lon),
    Esql.honest_first_lat = first(Esql.region_first_lat, Esql.region_first_seen),
    Esql.honest_first_lon = first(Esql.region_first_lon, Esql.region_first_seen),
    Esql.honest_last_lat  = last(Esql.region_last_lat,   Esql.region_last_seen),
    Esql.honest_last_lon  = last(Esql.region_last_lon,   Esql.region_last_seen),
    Esql.timestamp_first_seen = min(Esql.region_first_seen),
    Esql.timestamp_last_seen  = max(Esql.region_first_seen), // first arrival in last region > tighter bbox window
    Esql.honest_last_time     = max(Esql.region_last_seen),  // user's actual last event > honest window
    Esql.region_count  = count_distinct(source.geo.region_name),
    Esql.country_count = count_distinct(source.geo.country_name),
    Esql.event_count   = sum(Esql.region_event_count),
    Esql.source_geo_country_name_values     = values(source.geo.country_name),
    Esql.source_geo_region_name_values      = values(source.geo.region_name),
    Esql.source_geo_city_name_values        = values(Esql.region_city_values),
    Esql.source_as_organization_name_values = values(Esql.region_asn_values),
    Esql.source_ip_values                   = values(Esql.region_ip_values)
  by user.email

// need at least 2 regions to have anything to compare. cap at 5 because regions
// are finer-grained than countries (a traveling user can hit 3-4 in 90m via
// carrier hub bouncing) > bbox drift stays bounded below this.
| where Esql.region_count >= 2 and Esql.region_count <= 5

// bbox path (primary trigger): corners over region centroids.
| eval Esql.p1 = to_geopoint(concat("POINT(", to_string(Esql.min_lon), " ", to_string(Esql.min_lat), ")")),
       Esql.p2 = to_geopoint(concat("POINT(", to_string(Esql.max_lon), " ", to_string(Esql.max_lat), ")"))
| eval Esql.distance_km    = round(st_distance(Esql.p1, Esql.p2) / 1000.0, 0),
       Esql.window_minutes = date_diff("minute", Esql.timestamp_first_seen, Esql.timestamp_last_seen),
       Esql.travel_kmh     = case(Esql.window_minutes > 0,
                                  round(Esql.distance_km * 60.0 / Esql.window_minutes, 0), null)

// honest pair (triage signal): real coords at the user's actual first and last
// events, time locked to those same two events.
| eval Esql.honest_p1 = to_geopoint(concat("POINT(", to_string(Esql.honest_first_lon), " ", to_string(Esql.honest_first_lat), ")")),
       Esql.honest_p2 = to_geopoint(concat("POINT(", to_string(Esql.honest_last_lon),  " ", to_string(Esql.honest_last_lat),  ")"))
| eval Esql.honest_distance_km     = round(st_distance(Esql.honest_p1, Esql.honest_p2) / 1000.0, 0),
       Esql.honest_window_minutes  = date_diff("minute", Esql.timestamp_first_seen, Esql.honest_last_time),
       Esql.honest_travel_kmh      = case(Esql.honest_window_minutes > 0,
                                          round(Esql.honest_distance_km * 60.0 / Esql.honest_window_minutes, 0), null)

// 500 km separation + faster than a commercial airliner. bbox is the trigger
// honest fields are kept purely as triage signal.
| where Esql.distance_km >= 500 and Esql.travel_kmh >= 800

| keep user.email,
    Esql.source_geo_country_name_values,
    Esql.source_geo_region_name_values,
    Esql.source_geo_city_name_values,
    Esql.source_as_organization_name_values,
    Esql.source_ip_values,
    Esql.country_count,
    Esql.region_count,
    Esql.event_count,
    Esql.timestamp_first_seen,
    Esql.timestamp_last_seen,
    Esql.window_minutes,
    Esql.distance_km,
    Esql.travel_kmh,
    Esql.honest_distance_km,
    Esql.honest_travel_kmh,
    Esql.honest_window_minutes