Azure Sentinel: CIDR matching

Wed, 18 Mar 2020 15:08:09 +0000

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

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

The post Azure Sentinel: CIDR matching appeared first on Microsoft Industry Blogs - United Kingdom.

KQL Archives - Microsoft Industry Blogs - United Kingdom

Azure Sentinel: CIDR matching

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

1.Using the SigninLogs Table as data.

Results:

2. Using IPV4_is_match