✄𐘗
text code block:
✄𐘗// successful interactive sign-ins with country + region populated.
from logs-azure.signinlogs-*
| where data_stream.dataset == "azure.signinlogs"
    and event.outcome == "success"
    and azure.signinlogs.category == "SignInLogs"
    and azure.signinlogs.properties.user_principal_name is not null
    and source.geo.location 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),
    Esql.region_ua_values     = values(user_agent.original),
    Esql.region_app_id_values = values(azure.signinlogs.properties.app_id),
    Esql.region_app_display_name_values = values(azure.signinlogs.properties.app_display_name),
    Esql.region_client_app_used_values  = values(azure.signinlogs.properties.client_app_used),
    Esql.region_resource_id_values      = values(azure.signinlogs.properties.resource_id),
    Esql.region_resource_display_name_values = values(azure.signinlogs.properties.resource_display_name),
    Esql.region_browser_values = values(azure.signinlogs.properties.device_detail.browser),
    Esql.region_os_values      = values(azure.signinlogs.properties.device_detail.operating_system)
  by azure.signinlogs.properties.user_principal_name,
     source.geo.country_name,
     source.geo.region_name

// roll up to the user. two parallel measurements:
// bbox: corners over region centroids. catches A->B->A because B is still
// a centroid in the set even when first/last events are in A.
| 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),
    Esql.user_agent_original_values         = values(Esql.region_ua_values),
    Esql.app_id_values                      = values(Esql.region_app_id_values),
    Esql.app_display_name_values            = values(Esql.region_app_display_name_values),
    Esql.client_app_used_values             = values(Esql.region_client_app_used_values),
    Esql.resource_id_values                 = values(Esql.region_resource_id_values),
    Esql.resource_display_name_values       = values(Esql.region_resource_display_name_values),
    Esql.device_detail_browser_values       = values(Esql.region_browser_values),
    Esql.device_detail_operating_system_values = values(Esql.region_os_values)
  by azure.signinlogs.properties.user_principal_name

// need at least 2 regions to have anything to compare. cap at 5 because regions
// are finer-grained than countries (a traveling employee 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
// purely as triage signal.
| where Esql.distance_km >= 500 and Esql.travel_kmh >= 800

| keep azure.signinlogs.properties.user_principal_name,
    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.user_agent_original_values,
    Esql.app_id_values,
    Esql.app_display_name_values,
    Esql.client_app_used_values,
    Esql.resource_id_values,
    Esql.resource_display_name_values,
    Esql.device_detail_browser_values,
    Esql.device_detail_operating_system_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