How to find a common element in two columns with Kusto (KQL)? Objective: find matching IPs to CIDRs

Question

this is the dataset (image below):

Objective:

1. find in both the SrcIP_s and DestIP_s columns elements that match a dynamic list of CIDRs (i.e. probably using ipv4_is_in_range())

In my silly attempts I managed two find matching CIDRs in both columns using mv-apply, but the end result only shows results when they match simultaneously both columns, which is not the expected result.

let customer_cidrs = dynamic(['172.16.1.128/27']);
AzureNetworkAnalytics_CL
| mv-apply SrcCIDR = customer_cidrs to typeof(string) on (where ipv4_is_in_range(SrcIP_s, SrcCIDR))
| mv-apply DestCIDR = customer_cidrs to typeof(string) on (where ipv4_is_in_range(DestIP_s, DestCIDR))
| project FlowDirection_s, SrcIP_s, DestIP_s, InMiBytes=round((InboundBytes_d/1048576),2), OutMiBytes=round((OutboundBytes_d/1048576),2)

Restriction:

• the *MiBytes data must be preserved (later I intend to sum it all, thus obtaining a comprehensive figure of how many Bytes each matching CIDR exchanged)

Expected result:

show all lines that match EITHER SrcIP_s or the DestIP_s columns without changing any other column value (i.e. exclude rows that don't match a customer_cidrs element).

Any ideas?

Answer 1

You may want to see if using the ipv4_lookup is going to work better for you rather than ipv4_is_in_range. It sounds like it could be a good fit for your use case.

let customer_cidrs = datatable (CIDR:string, Customer1:string) [
'172.16.1.128/27', 'Contoso',
'10.10.1.0/24', 'Wingtip',
'192.168.1.0/24', 'Northwind'
];
let AzureNetworkAnalytics_CL_T = datatable (FlowDirection_s:string, SrcIP_s:string, DestIP_s:string, InMiBytes:real, OutMiBytes:real)[
'I', '172.16.1.69', '172.16.1.132', 0.06, 0.04,
'I', '172.16.1.101', '172.16.1.132', 0.0, 0.0,
'I', '192.168.0.10', '192.168.1.50', 10.2, 15.7,
'O', '172.16.1.132', '1.1.1.1', 0.04, 0.0,
'O', '192.168.1.100', '10.10.1.14', 0.1, 1.6
];
AzureNetworkAnalytics_CL_T
| evaluate ipv4_lookup(customer_cidrs, SrcIP_s, CIDR, return_unmatched=true)
| evaluate ipv4_lookup(customer_cidrs, DestIP_s, CIDR, return_unmatched=true)
| where isnotempty(coalesce(Customer1, Customer11))