Logins far from HQ with ST_DISTANCE
ST_DISTANCE computes the distance in meters between a login's geolocation and a reference point — here the Paris headquarters — to surface geographically improbable accesses.
Prerequisites
Elasticsearch 8.15+, GeoIP sur source.ip
SQL
FROM "logs-auth-*"
| WHERE event.outcome == "success"
AND source.geo.location IS NOT NULL
AND @timestamp >= NOW() - 24 hours
| EVAL siege = TO_GEOPOINT("POINT(2.3522 48.8566)")
| EVAL distance_km = ROUND(ST_DISTANCE(source.geo.location, siege) / 1000)
| WHERE distance_km > 500
| STATS
connexions = COUNT(*),
distance_max_km = MAX(distance_km),
pays = VALUES(source.geo.country_iso_code)
BY user.name
| SORT distance_max_km DESC
| LIMIT 20Result
user.name | connexions | distance_max_km | pays -----------+------------+-----------------+-------------- m.duval | 12 | 9 714 | ["JP", "FR"] k.bennani | 3 | 7 802 | ["SG"] a.lefort | 8 | 6 051 | ["US", "FR"] s.gomez | 1 | 1 940 | ["GR"] p.martin | 5 | 812 | ["ES"]
ST_DISTANCEGeoIPSOCCompromission