log query on NetworkCidrBlock match

Question

Hi Community,

My "ThreatIntelligenceIndicator" table stores rows of NetworkCidrBlock as indicators.

I need to query "CommonSecurityLog" table against the indicator table on any DestinationIP that matches any value in NetworkCidrBlock column.

I understand the function ipv4_is_match() can check if an IP is within a Cidr block. So I tried to use it for ip to cidr correlation between the two tables... something like the following but it gave me error: join can only work on column entity or equality expression like $left.DestinationIP == $right.NetworkIP.

CommonSecurityLog
| where TimeGenerated >= ago(2h) and not(ipv4_is_private(DestinationIP))
| join (ThreatIntelligenceIndicator
| where ExpirationDateTime > now() and Active == true and NetworkIP != ''
  ) on  ipv4_is_match($left.DestinationIP, $right.NetworkCidrBlock)

If there's any work around that allows me to correlate ips in the log with CidrBlock, please share!

Thanks in advance!

Answer 1

After some test and trial, I got this work around to work for returning result if IP from one table is in CIDR of the other. However, there's a couple of caveats I will mention at the bottom.

let TICacheS = toscalar(
ThreatIntelligenceIndicator
| where ExpirationDateTime > now() 
   and TimeGenerated > ago(1d)
   and Active == true 
   and isnotempty(NetworkCidrBlock)
| distinct NetworkCidrBlock
| limit 10000
| summarize cidr_set = make_set(NetworkCidrBlock)
);

let LogCache = materialize(
CommonSecurityLog
| where not(ipv4_is_private(DestinationIP)) 
 and TimeGenerated >= ago(1h)
| distinct DestinationIP
| extend ip=DestinationIP
);

let t= LogCache
| mv-apply cidr=TICacheS to typeof(string) on 
(
   where ipv4_is_match(ip, cidr)
);

CommonSecurityLog
| where TimeGenerated >= ago(2d)
| join t on $left.DestinationIP == $right.ip

The caveats are:

1. only 10K cidr allowed in this query otherwise it will error out with complain of too much data.
2. It's very slow in comparison to the join query of networkIPs. (perhaps it has to do the cidr calculation instead of string comparison in just network ips)

This might be a work around for someone that is looking to do the same. Unfortunately the size limitation of CIDRs is a blocker for me to actually use it.

Answer 2

Try to match like this

CommonSecurityLog
 | where TimeGenerated >= ago(2h)
 | where not(ipv4_is_private(DestinationIP))
 | join 
 (
    ThreatIntelligenceIndicator
   // | where TimeGenerated > ago(7d)
    | where  Active == true and isnotempty(NetworkIP)
    //| extend NetworkIP = "20.44.8.3", NetworkSourceCidrBlock = "20.44.8.3/32"
    | project NetworkIP , NetworkSourceCidrBlock, NetworkSourceIP
 ) on  $left.DestinationIP == $right.NetworkIP
| where ipv4_is_match(DestinationIP, NetworkSourceCidrBlock)

Answer 3

https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/ipv4-is-matchfunction allows you to match an IP address --> IP address or IP Address --> CIDR

i.e.

ipv4_is_match('192.168.1.1', '192.168.1.255') == false
ipv4_is_match('192.168.1.1/24', '192.168.1.255/24') == true

However a JOIN need equality (which was the error you got originally), so if both tables share a common Column like "name" or "id" then you can join on that, if not to get equality you have to join on $left.nnnn == $right.yyyy. As this uses "==" they have to match, so a IP address on the left wouldn't match a CIDR block on the right (even if the IP was "contained" within the block)

other examples:

https://github.com/Azure/Azure-Sentinel/issues/910
https://github.com/Azure/Azure-Sentinel/search?q=ipv4_is_match