KQL - How to enrich an event by matching an IP address to an IP range from a Sentinel Watchlist?

Question

I am trying to enrich events from an Analytic Rule with a Watchlist as I did in Splunk. My event contains a field named SourceIP that obviously contains an IP address.

I have a Watchlist that contains all subnets of different locations:

Office	Range
New York	10.10.1.0/16
Paris	10.20.1.0/24

I need to match the IP address from the event with the IP range from the Watchlist to output the field Office and its value.

I made this in splunk with a lookup, but I don't know how to do this in KQL.

|lookup office-ranges.csv Range AS SourceIP OUTPUT Office

I tried to do this in KQL with join operator but it doesn't allow me to match the IP

|join kind=fullouter (_GetWatchlist(office-ranges) on Ranges

And also tried with lookup:

| lookup kind = leftouter _GetWatchlist(office-ranges) on $left.SourceIP  $right.SearchKey

Answer 1

You'll probably want to use the inbuilt ipv4_lookup like this:

let Watchlist = datatable(Office:string, Range:string) [
'New York', '10.10.1.0/16',
'Paris', '10.20.1.0/24'
];
let Events = datatable(Description:string, SoureIP:string) [
'Event 1', '192.168.0.50',
'Event 2', '10.10.6.50',
'Event 3', '10.20.1.50',
'Event 4', '10.20.2.50'
];
Events
| evaluate ipv4_lookup(Watchlist, SoureIP, Range, return_unmatched = true)

Description	SoureIP	Office	Range
Event 1	192.168.0.50
Event 2	10.10.6.50	New York	10.10.1.0/16
Event 3	10.20.1.50	Paris	10.20.1.0/24
Event 4	10.20.2.50