Skip to content
Microsoft Industry Blogs - United Kingdom

KQL has some IPV4 features.  A new one last month is IPV4_is_match : https://docs.microsoft.com/en-us/azure/kusto/query/ipv4-is-matchfunction

IPv4 info

Two examples (more here https://github.com/CliveW-MSFT/KQLpublic/blob/master/Queries/CIDRexamples ):

1.Using the SigninLogs Table as data.

This example takes an IP Address from the log and sees if it is in an allowed range or not.  You define whats allowed or not in the CASE statement.
SigninLogs
| where TimeGenerated > ago(24h)
| extend local = case(parse_ipv4(IPAddress) between ( parse_ipv4(“67.0.0.0”).. parse_ipv4(“67.255.255.255″)),”Allowed”,
parse_ipv4(IPAddress) between ( parse_ipv4(“74.0.0.0”).. parse_ipv4(“74.255.255.255″)),”Allowed”,
parse_ipv4(IPAddress) between ( parse_ipv4(“100.0.0.0”) .. parse_ipv4(“109.255.255.255″) ),”Aloowed”,
//else
“Not Allowed”)
| summarize count(), make_set(IPAddress) by local
| order by local asc

 

     Results:

local count_ set_IPAddress
Allowed 133 [“108.4.232.173″,”67.166.178.142″,”109.88.218.99″,”108.54.119.134″,”67.162.100.59”]
Not Allowed 332 [“52.179.171.240″,”77.125.9.89″,”84.109.188.120″,”192.176.203.10″,”80.230.49.35″,”37.142.175.61″,”185.27.105.142″,”213.57.167.77″,”79.176.91.243″,”51.143.4.240″,”77.139.246.1″,”73.225.151.175″,”50.35.73.176″,”40.121.91.41″,”89.151.37.15″,”143.159.241.199″,”47.185.20.237″,”173.169.57.117″,”136.55.145.135″,”173.68.101.186″,”52.191.195.160″,”167.220.2.190″,”98.232.109.219″,”166.67.66.245″,”77.138.103.125″,”184.170.166.31″,”83.130.91.77″,”137.135.26.148″,”66.108.20.213″,”90.222.83.39″,”93.173.27.72”]

 

2. Using IPV4_is_match

The example in the help is this:

datatable(ip1_string:string, ip2_string:string)
[
‘1.168.1.0’,’192.168.1.0′,
‘192.168.1.1/24′,’192.168.1.255’,
‘192.168.1.1’,’192.168.1.10/24′,
‘239.168.1.1/30′,’192.168.1.255/24’,
]
| extend CIDRresult= ipv4_is_match(ip1_string, ip2_string) // In CIDR range?

 

 

ip1_string ip2_string result
192.168.1.0 192.168.1.0 true
192.168.1.1/24 192.168.1.255 true
192.168.1.1 192.168.1.255/29 false
192.168.1.1/30 192.168.1.255/24 true

 

We can add HostCount and IP Class information

datatable(ip1_string:string, ip2_string:string)
[
‘1.168.1.0’,’192.168.1.0′,
‘192.168.1.1/24′,’192.168.1.255’,
‘192.168.1.1’,’192.168.1.10/24′,
‘239.168.1.1/30′,’192.168.1.255/24’,
]
| extend CIDRresult= ipv4_is_match(ip1_string, ip2_string) // In CIDR range?
| extend hostcount = pow(2,(32 – split(ip1_string,”/”).[1])) // How many hosts supported?
| extend IPAddress = tostring(split(ip1_string,”/”).[0]) // Get just IP part of CIDR
| extend ipClass = case(parse_ipv4(IPAddress) between ( parse_ipv4(‘1.0.0.0’).. parse_ipv4(‘126.0.0.0’)),”A”,
parse_ipv4(IPAddress) between ( parse_ipv4(‘128.0.0.0’).. parse_ipv4(‘191.255.0.0’)),”B”,
parse_ipv4(IPAddress) between ( parse_ipv4(‘192.0.0.0’).. parse_ipv4(‘223.255.255.0’) ),”C”,
parse_ipv4(IPAddress) between ( parse_ipv4(‘224.0.0.0’).. parse_ipv4(‘239.255.255.255’) ),”D”,
parse_ipv4(IPAddress) between ( parse_ipv4(‘240.0.0.0’).. parse_ipv4(‘255.255.255.254’) ),”E”,
//else
strcat(“Unknown class”, parse_ipv4(IPAddress))
)
ip1_string ip2_string CIDRresult hostcount IPAddress ipClass
1.168.1.0 192.168.1.0 false null 1.168.1.0 A
192.168.1.1/24 192.168.1.255 true 256 192.168.1.1 C
192.168.1.1 192.168.1.10/24 true null 192.168.1.1 C
239.168.1.1/30 192.168.1.255/24 false 4 239.168.1.1 D

 

Join the conversation

Leave a reply

Your email address will not be published. Required fields are marked *

Loading comments...