question

SentinelNoob-4281 avatar image
0 Votes"
SentinelNoob-4281 asked SentinelNoob-4281 commented

log query on NetworkCidrBlock match

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!





azure-monitormicrosoft-sentinel
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SentinelNoob-4281 avatar image
0 Votes"
SentinelNoob-4281 answered SentinelNoob-4281 edited

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.












5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

clivewatson-9831 avatar image
0 Votes"
clivewatson-9831 answered SentinelNoob-4281 commented

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)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Clive,

That's an interesting idea, but doesn't the condition "$left.DestinationIP == $right.NetworkIP" implies true for "ipv4_is_match(DestinationIP, NetworkSourceCidrBlock)" if the NetworkIP is within the range of NetworkSourceCidrBlock?

What about cidr value of "20.44.8.3/20" that represent IP range from 20.44.0.1 to 20.44.15.254. What would the NetworkIP value used for the join condition?

Thanks for replying.

0 Votes 0 ·
clivewatson-9831 avatar image
0 Votes"
clivewatson-9831 answered SentinelNoob-4281 commented

https://docs.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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for sharing the links to examples. I got a version of it to work, but there're limitations -- see my post below.

0 Votes 0 ·