CODEWITHSUNDEEP
azurekql
Jason
Jason

Reputation: 821

Whitelist is being ignored in KQL Query

I have the following KQL query built out. I want to only show IPs that are not within the whitelisted subnets at the top.

When I run this I still get the whitelisted subnets in the results. Am I missing something?

let lookup=toscalar(datatable(ip_range:string) ["'X.X.0.0/16', 'X.X.0.0/16" , "X.X.0.0/16" , "X.X.0.0/16"] | summarize l=make_set(ip_range));
CommonSecurityLog
| search *
| extend dIPresult = ipv4_is_private(DestinationIP)
| where DeviceVendor == "Fortinet"
| where AdditionalExtensions !contains "dstcountry=United States"
| where DeviceAction !in~ ("close","server-rst","deny","timeout","client-rst")
| where DestinationPort > 1024 and isnotempty(DestinationPort)
| where dIPresult == false
| where ipv4_is_private(SourceIP)
| mv-apply l=lookup to typeof(string) on (where not(ipv4_is_match(DestinationIP, l)))
| summarize count() by ['DestinationIP'] | sort by count_

Upvotes: 0

Views: 493

Answers (1)

gorzilla
gorzilla

Reputation: 108

So your first line has some mix of double/single quotes that I expect is just a copy/paste error, and also lookup is a KQL keyword so that probably isn't helping.

But I'm pretty sure your issue is your use of the mv-apply function. From the docs:

The mv-apply operator has the following processing steps:

  1. Uses the mv-expand operator to expand each record in the input into subtables (order is preserved).
  2. Applies the subquery for each of the subtables.
  3. Adds zero or more columns to the resulting subtable. These columns contain the values of the source columns that aren't expanded, and are repeated where needed.
  4. Returns the union of the results.

So each IP is compared using where not (ipv4_is_match()) against a subtable containing the 3 whitelisted subnets X,Y,Z. If the IP is in subnet X then the whitelist will be applied for that comparison but then it also compares against subnets Y and Z and in those cases the check fails. The results of all three checks get unioned at the end.

Here's a simpler version that's easier to follow

let lookuptable=datatable(ip_range:string) ["10.0.0.0/8", "192.168.0.0/16", "192.168.1.0/24"]
| summarize l=make_set(ip_range);
datatable(IP:string) ["172.16.0.1","10.0.0.1", "192.168.1.1"]
| mv-apply SubnetWhitelist=toscalar(lookuptable) to typeof(string) on (where not(ipv4_is_match(IP, SubnetWhitelist)))

You can see the first IP 172... isn't in any of the whitelists but since the results get unioned it actually shows up 3 times. The 10... IP is in the 10/8 range so that one check gets filtered out. the 192... matches 2 filters but the one it doesn't match still makes it through.

IP SubnetWhitelist
172.16.0.1 10.0.0.0/8
172.16.0.1 192.168.0.0/16
172.16.0.1 192.168.1.0/24
10.0.0.1 192.168.0.0/16
10.0.0.1 192.168.1.0/24
192.168.1.1 10.0.0.0/8

Upvotes: 1

Related Questions