KUSTO never ceases to amaze me! Whenever I tackle a new use case, I always find a specific function to get the job done. Recently, I needed to compare the previous event in an ordered list to calculate impossible travel. And let me tell you, KUSTO delivered flawlessly ❤️❤️❤️
key features used
- Geospatial – geo_distance_2points()
- Window functions – prev()
Bare in mind, the example is fully educational, I used values, that are there only for educational purposes, there might be many things to fix / adjust logically. The aim was to learn some use for these functions
- Depending on travel options the travel speed needs to be dynamic. I used something of an middle ground, where I estimate static speed of 500km / hour (Travelling to airport, security etc, overheads…) – If you have your own private jet, you might have better travel speed, or you do long travel with max speed (lets say 950km/h) with really little time spent at airfields
- VPN switching between locations
Results
https://github.com/jsa2/kql/blob/main/impossibleTravel.kql4
let roughTravelSpeedPerHour = 500;
union AADNonInteractiveUserSignInLogs, SigninLogs
| where ResultType == 0
| where TimeGenerated > ago(30d)
| extend unifiedLocation = coalesce(tostring(LocationDetails_dynamic), LocationDetails_string)
| extend TimeGenerated = bin(TimeGenerated, 1m)
| extend unifiedLocation = parse_json(unifiedLocation).geoCoordinates
| evaluate bag_unpack(unifiedLocation)
| project UserPrincipalName, Location, TimeGenerated, latitude, longitude
| distinct UserPrincipalName, TimeGenerated, latitude, longitude, Location
| sort by UserPrincipalName asc, TimeGenerated asc
| summarize events = make_list(pack_all(true)) by UserPrincipalName
| extend forcedSorting = array_sort_asc(events)
| mv-apply forcedSorting on (
serialize
| extend TimeGenerated = todatetime(forcedSorting.TimeGenerated), latitude = todouble(forcedSorting.latitude), longitude = todouble(forcedSorting.longitude), Location = tostring(forcedSorting.Location)
| extend previousTimeGenerated = prev(TimeGenerated, 1)
| extend differenceInHours = datetime_diff('hour', TimeGenerated, previousTimeGenerated)
| extend previousLatitude = prev(latitude, 1), previousLongitude = prev(longitude, 1)
| extend previousLocation = prev(Location)
// account jumps back
| extend secondPreviousLocation = prev(Location, 2)
| where Location != secondPreviousLocation
// don't account for events that have the same LAT/LONG and for the first event in the loop (it cannot have anything in prev() to compare)
| where previousLatitude != latitude and previousLongitude != longitude and isnotempty(previousTimeGenerated)
| extend geoDiffInKm = round(geo_distance_2points(previousLatitude, previousLongitude, latitude, longitude) / 1000, 2)
| extend allowedDiffInHours = round(geoDiffInKm / roughTravelSpeedPerHour, 2)
)
| distinct UserPrincipalName, differenceInHours, previousLocation, Location, allowedDiffInHours, TimeGenerated, previousTimeGenerated
Ending words
Further optimization could be probably be done with max() and min() to record first and last timestamps per location
Check further KUSTO documentation here under ADX https://learn.microsoft.com/en-us/azure/data-explorer/
SecureCloudBlog 
0 comments on “KUSTO education – Detecting Impossible Travel with Built-in Capabilities”