Hunting Impossible Travel in Sentinel with KQL

Here's a sign-in pattern that should never happen. An account authenticates from Dublin at nine in the morning, then from São Paulo forty minutes later. Nobody crossed the Atlantic in forty minutes. So either the person is running a VPN that hops continents, or two different people are using one set of credentials, and one of them shouldn't be. That second case is what you're hunting.

Impossible travel is one of the classic identity detections, and Entra already flags a version of it as a risk signal. So why write your own? Because the built-in one is a black box that fires when Microsoft's model decides it should, and you can't see its workings or tune its thresholds. Building your own in KQL means you understand exactly what it checks, you can adjust it to your own tolerance, and you learn a couple of operators that are useful well beyond this one query. Same philosophy as the rest of this series: don't trust a detection you can't read.

The shape of the problem

Every sign-in in SigninLogs carries a location: a latitude and longitude buried in the LocationDetails field. If you take each account's sign-ins in order, look at consecutive pairs, and work out how far apart they are and how much time passed between them, you can calculate an implied travel speed. When that speed climbs past anything a human could actually manage, even by plane, you've found something worth looking at.

So the detection is really three steps: get each sign-in's location and time, line them up per user in order, then measure the distance and the gap between each pair.

Getting the location out

First, look at what you're working with. LocationDetails is a nested field, so pull the pieces you need into plain columns:

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == "0"
| project TimeGenerated, UserPrincipalName, IPAddress,
    City = tostring(LocationDetails.city),
    Country = tostring(LocationDetails.countryOrRegion),
    Lat = toreal(LocationDetails.geoCoordinates.latitude),
    Lon = toreal(LocationDetails.geoCoordinates.longitude)
| take 20

Only successful sign-ins here (ResultType == "0"), because a failed sign-in from another country is a different detection. Run that, read the rows, and confirm your tenant actually populates coordinates. Most do, but some sign-ins come through with blank location, and you want to know that before you build on it.

One more thing about the table. SigninLogs only holds interactive sign-ins, the ones where someone typed a password or approved a prompt. Token refreshes and other background sign-ins go to AADNonInteractiveUserSignInLogs, and there LocationDetails is stored as a string, so wrap it in parse_json() before you read the coordinates.

Lining up consecutive sign-ins

This is the operator worth learning: prev(). When you sort a user's sign-ins by time, prev() lets each row see the row before it. That's how you compare a sign-in to the one that came just before, which is exactly what "consecutive locations" means.

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == "0"
| project TimeGenerated, UserPrincipalName,
    Lat = toreal(LocationDetails.geoCoordinates.latitude),
    Lon = toreal(LocationDetails.geoCoordinates.longitude),
    City = tostring(LocationDetails.city)
| where isnotempty(Lat) and isnotempty(Lon)
| order by UserPrincipalName asc, TimeGenerated asc
| extend PrevLat = prev(Lat), PrevLon = prev(Lon),
    PrevTime = prev(TimeGenerated), PrevUser = prev(UserPrincipalName),
    PrevCity = prev(City)
| where UserPrincipalName == PrevUser

Two things make this correct. Ordering by user and then time means each row's predecessor is genuinely that user's previous sign-in. And the last line, UserPrincipalName == PrevUser, throws away the rows where prev() has reached back into a different person's data, which happens at the boundary between one user and the next. Miss that check and you'll "detect" someone travelling from their location to a stranger's, which is nonsense.

Distance, time, and the speed that gives it away

Now the payoff. geo_distance_2points() returns the distance in metres between two points, doing the curved-earth maths for you. Each point is longitude then latitude, so watch the order. If either point is missing or invalid, it returns null. Divide distance by the time between the two sign-ins and you have an implied speed:

// Impossible travel: consecutive sign-ins too far apart to be real
// ------------------------------------------------------------------
// What it finds: users whose successful sign-ins come from two places
//   so far apart, so close together in time, that nobody could have
//   travelled between them. Often a stolen session or password used
//   from somewhere the real user isn't. VPNs and mobile carriers cause
//   false positives, so check the cities before acting.
// Table: SigninLogs (interactive sign-ins only). Non-interactive
//   sign-ins are in AADNonInteractiveUserSignInLogs, where
//   LocationDetails is a string, so wrap it in parse_json() first.
// MITRE ATT&CK: T1078.004 Valid Accounts: Cloud Accounts
// Written up in: https://alanconlon.com/posts/hunting-impossible-travel-kql/
// ------------------------------------------------------------------
let Lookback = 1d;           // how far back to look
let SpeedThresholdKmh = 900; // roughly airliner speed; anything faster is suspicious
let MinDistanceKm = 500;     // ignore shorter hops: IP geolocation often jumps a few hundred km
                             //   between nearby sign-ins. This misses short in-country hops,
                             //   so lower it if you need them.
// Step 1: successful sign-ins that carry both coordinates
SigninLogs
| where TimeGenerated > ago(Lookback)
| where ResultType == "0"                   // 0 = success
| project TimeGenerated, UserPrincipalName,
    Lat = toreal(LocationDetails.geoCoordinates.latitude),
    Lon = toreal(LocationDetails.geoCoordinates.longitude),
    City = tostring(LocationDetails.city)
| where isnotempty(Lat) and isnotempty(Lon)
// Step 2: line up each user's sign-ins in time order and pair each with the one before.
//   prev() only works on serialized rows; order by serializes them, so keep it right above.
| order by UserPrincipalName asc, TimeGenerated asc
| extend PrevLat = prev(Lat), PrevLon = prev(Lon),
    PrevTime = prev(TimeGenerated), PrevUser = prev(UserPrincipalName),
    PrevCity = prev(City)
| where UserPrincipalName == PrevUser       // don't compare across different users
// Step 3: distance and time between the pair, then the speed that implies
| extend DistanceKm = geo_distance_2points(Lon, Lat, PrevLon, PrevLat) / 1000
| where DistanceKm >= MinDistanceKm
//   Milliseconds, so sign-ins less than a second apart still count.
| extend HoursApart = datetime_diff('millisecond', TimeGenerated, PrevTime) / 3600000.0
//   Two far-apart places at the same instant is impossible, so a zero gap gets infinite speed.
| extend SpeedKmh = iff(HoursApart > 0, DistanceKm / HoursApart, real(+inf))
// Step 4: keep only speeds no traveller could manage
| where SpeedKmh > SpeedThresholdKmh
| project UserPrincipalName, PrevCity, City, DistanceKm, HoursApart, SpeedKmh, TimeGenerated
| sort by SpeedKmh desc

The threshold is the judgement call. A commercial airliner cruises around 900 km/h, so anything faster than that implies travel no passenger flight could achieve, which is a reasonable line to draw. Set it lower and you start catching fast but legitimate journeys; set it higher and you only see the truly impossible. Nine hundred is a sensible place to begin, and your own results will tell you whether to move it. The query also skips any pair less than 500 km apart, because IP location often jumps a few hundred kilometres between sign-ins that are really from the same place. That means it misses short in-country hops, so lower MinDistanceKm if you need them.

A commented copy of that query is in the free queries repo if you'd rather start from a file.

What it flags, and what it flags falsely

Be honest about the false positives, because this detection has a specific and very common one: VPNs. A user who connects to a corporate VPN endpoint in another country, or flips a personal VPN on and off, will look exactly like impossible travel, because as far as the sign-in logs are concerned they teleported. Mobile networks that route through a gateway in another city do the same thing on a smaller scale. So a hit here is a question, not a verdict. The useful follow-up is to look at whether the two sign-ins came from wildly different device or app fingerprints, or whether one of the locations is somewhere the person has genuinely never been.

And what it misses: an attacker signing in from the same city as the victim, or anywhere within 500 km, won't register at all, and one patient enough to wait out the speed threshold slips under it. This catches the careless and the automated, not the careful. That is still most of them.

Promote it the same way as always

Run it against a week of real data first. You will find VPN noise, and tuning it, whether that's the speed threshold, an exclusion for your known VPN egress ranges, or a device-mismatch condition, is the actual work. Only once it returns signal you trust should it become a scheduled analytics rule. The built-in Entra risk detection is worth leaving on alongside it; the point of writing your own isn't to replace Microsoft's, it's to have one whose reasoning you can see and defend when it fires at 2am.

It's the same lesson as the earlier hunts. A count and a threshold caught MFA fatigue. A prev() and a distance function catch impossible travel. Neither is complicated. Both catch real attacks that have cost real organisations, and both were sitting in the logs the whole time, waiting for someone to line the rows up and look.