KQL: Check table of IPs against table of subnets

Question

From a table with a column of IPs i would like to know which IPs are in a subnet from a table of which contains a column of subnets.

Attempt:

let IPs = SecurityAlert
| mv-expand parse_json(Entities)
| evaluate bag_unpack(Entities, columnsConflict='keep_source')
| distinct Address;
AzureIPTable
| extend ipv4_is_in_range(IPs, addressPrefixes) 

Pretend addressPrefixes is a table of subnets in AzureIPTable and Address is a column of IPs in a table called SecurityAlert

Answer 1

I have reproduced in my environment and below are expected results:

Firstly IP's Table:

Subnet Table:

Now use below KQL Query (to Check if IP is in Subnet IP's, which are in another table ) like below:

let subnetTable = datatable(Subnet:string)
[
    "172.168.1.0/24",
    "10.0.0.0/24",
    "172.16.0.0/16"
]
|  summarize mylist = make_list(Subnet)
| extend new_column2 = 0
| sort by new_column2 desc;
let ipTable = datatable(IPAddress:string)
[
    "192.168.1.10",
    "10.0.0.5",
    "172.16.0.20"
];
let x=ipTable
| extend new_column2 = 0
| sort by new_column2 desc
| extend  rn=row_number()
|join kind=fullouter subnetTable on new_column2
|project-away rn,new_column2,new_column21;
let y = x
|mv-apply id= mylist to typeof(string) on (where ipv4_is_in_range(IPAddress,id))
|project-away mylist;
y
|join kind = fullouter x on IPAddress
|project-away IPAddress
|extend TESTCOL =iff(isempty(id),False,True)
|project-away id

Output:

If present in range then gives true else gives false.

Fiddle.