If you query data that contains IP addresses this blog is something for you! It does not matter if you are a SOC Analyst, Detection Engineer, Network Engineer or a Developer all the logs that you use on a daily basis will contain IP addresses. This can be in Sentinel, Defender For Endpoint, Application Insights, Azure Firewall and many other sources.

This blog will discuss some basic network related operations, before diving into useful network related KQL functions. The blog is divided into the following sections:

• Troubleshooting Network Data
  • Filter specific IP
  • List all unique IPs
  • Count all IPs
• ipv4_is_private
• ipv4_range_to_cidr_list
• ipv4_is_in_range
• ipv4_is_in_any_range
• IP Regex

Troubleshooting Network Data

Before we dive into the specific network related functions, first some general KQL queries that can help you gather the logs that you need. Those are particularly useful when troubleshooting incidents. Based on those basic queries you can filter the exact events that you need.

Filter specific IP

If you want to filter on one specific IP address the where equals clause can be used, as seen below. In the case of this datatable, one row will be returned that matches ip_address equals 10.0.0.1.

datatable(ip_address:string)
[
    "192.168.1.1",       // Private
    "10.0.0.1",          // Private
    "172.16.0.1",        // Private
]    
| where ip_address == "10.0.0.1"

Executing Example Queries You can execute the example queries in Azure Data Explorer, Sentinel, Log Analytics or Advanced Hunting.

List all unique IPs

To list all unique values of a column, the distinct operator is used. The input is 5 rows, and the distinct operator returns 3 rows containing the unique IPs that are found.

Query:

datatable(ip_address:string)
[
    "192.168.1.1",       // Private
    "10.0.0.1",          // Private
    "172.16.0.1",        // Private
    "172.16.0.1",        // Private
    "172.16.0.1",        // Private
]    
| distinct ip_address

Count all IPs

If you want to get insight into how often certain IP addresses have been used in your dataset the summarize operator can be leveraged.

Query:

datatable(ip_address:string)
[
    "192.168.1.1",       // Private
    "10.0.0.1",          // Private
    "10.0.0.1",          // Private
    "10.0.0.1",          // Private
    "10.0.0.1",          // Private
    "10.0.0.1",          // Private
    "10.0.5.1",          // Private                    
    "172.16.0.1",        // Private
    "172.16.0.1",        // Private
    "172.16.0.1",        // Private
]    
| summarize Total = count() by ip_address
| sort by Total

ipv4_is_private()

Scoping KQL queries to only public or private addresses can be very useful. The function ipv4_is_private() is specifically designed to take any IPv4 address as input and return whether the IP is private (return true) or public (return false). The input can be a single IP string, but also a column that contains IP addresses to directly return the results for your whole dataset. This function can be leveraged by extending the current KQL query to include a boolean field that contains the return value of the function, or it can be used in a condition clause.

In the example below an additional column isprivate is created, which is filled based on the ipv4_is_private() function. You can also directly filter on only public or private IPs by using:

// Return only public IPs
| where not(ipv4_is_private(ip_address))
// Return only private IPs
| where ipv4_is_private(ip_address)

Query:

datatable(ip_address:string)
[
    "192.168.1.1",       // Private
    "10.0.0.1",          // Private
    "172.16.0.1",        // Private
    "192.168.0.1",       // Private
    "203.0.113.1",       // Public
    "104.16.249.249",    // Public
    "8.8.8.8",           // Public
    "151.101.129.69",    // Public
    "216.58.194.142",     // Public
]    
| extend isprivate = ipv4_is_private(ip_address)

DeviceNetworkEvents // Your table name
| where ipv4_is_private(LocalIP) // Condition on the column that contains an IP
| where not(ipv4_is_private(RemoteIP)) // Condition on the column that contains an IP

ipv4_range_to_cidr_list()

Calculating what ranges you need to put in the firewall can be a tedious job, but KQL is here to help. The ipv4_range_to_cidr_list() can return all subnets that you need to configure by only needing a start and end IP address.

The image below shows an example where all subnets that need to be configured where the first IP is 10.5.64.0 and the last of your range is 10.5.96.254. Really easy right?

Query:

print start_range="10.5.64.0", end_range="10.5.96.254"
 | project ipv4_range_list = ipv4_range_to_cidr_list(start_range, end_range)

| extend GeoIPInfo = geo_info_from_ip_address(RemoteIP)
| extend country = tostring(parse_json(GeoIPInfo).country), 
    state = tostring(parse_json(GeoIPInfo).state), 
    city = tostring(parse_json(GeoIPInfo).city), 
    latitude = tostring(parse_json(GeoIPInfo).latitude), 
    longitude = tostring(parse_json(GeoIPInfo).longitude)

ipv4_is_in_range()

What if I do need to get results for a certain subnet? Let’s assume I want to return all rows that match on the subnet 10.0.0.0/8. You could use hasprefix or startswith as shown below.

| where ip_address hasprefix "10."
| where ip_address startswith "10."

But is it really effective? I do not think so, for your colleagues, it will also be unclear why this will be used, luckily there is a function that can simply take the subnet you want to filter on and match it against your data. This can be seen in the example below, the function ipv4_is_in_range(), will take an IP (or column with IPs) as first variable, the second is simply the subnet range you want your data to be in.

But what if you have to search in multiple ranges at once? Well, that is also possible using the ipv4_is_in_any_range() function.

Query:

datatable(ip_address:string)
[
    "192.168.1.1",       // Private
    "10.0.0.1",          // Private
    "10.3.0.1",          // Private
    "10.55.234.1",          // Private
    "172.16.0.1",        // Private
    "192.168.0.1",       // Private
    "203.0.113.1",       // Public
    "216.58.194.142",     // Public
]    
| extend isprivate = ipv4_is_private(ip_address)
| where ipv4_is_in_range(ip_address, "10.0.0.0/8")

Example queries If you are looking for more example queries have a look at the Hunting-Queries-Detection-Rules Repo

ipv4_is_in_any_range()

The ipv4_is_in_any_range() is very similar to the ipv4_is_in_range() function, the only difference being that you can instead of matching on 1 range match on n ranges.

In case you want to filter on a large number of ranges, it is recommended to use a let variable for the ranges as seen below. This will ensure that your query will still be organised. Especially if you are going to write longer queries, the variables will make it easier for others to update certain ranges when needed.

let DynamicList = dynamic(["10.3.0.0/16", "10.55.234.0/24", "216.58.194.144/30"]);
datatable(ip_address:string)
[
    "192.168.1.1",       // Private
    "10.0.0.1",          // Private
    "10.3.0.1",          // Private
    "10.55.234.1",          // Private
]    
| where ipv4_is_in_any_range(ip_address, DynamicList)

Query:

datatable(ip_address:string)
[
    "192.168.1.1",       // Private
    "10.0.0.1",          // Private
    "10.3.0.1",          // Private
    "10.55.234.1",          // Private
    "172.16.0.1",        // Private
    "192.168.0.1",       // Private
    "203.0.113.1",       // Public
    "216.58.194.144",     // Public
]    
| extend isprivate = ipv4_is_private(ip_address)
| extend isin10subnet = ipv4_is_in_range(ip_address, "10.0.0.0/8")
| where ipv4_is_in_any_range(ip_address, "10.3.0.0/16", "10.55.234.0/24", "216.58.194.144/30")

IP Regex

In some occasions the IP address is located inside a larger string, an example of this is commandlines. If you log commandline executions the following examples can appear in your dataset:

ping 8.8.8.8
wget --ftp-user=FTP_USERNAME --ftp-password=FTP_PASSWORD ftp://10.10.10.10/filename.tar.gz
Set-NetIPAddress -InterfaceIndex 12 -IPAddress 192.168.0.1 -PrefixLength 24 

But how can we extract the mentioned IP addresses and perform analysis on them? This can be done using the extract() function. An example of this is shown below. First, an IPv4 regex is defined, this is later used to match the content of the commandline and extract the matched content. The results are saved in a new column named IPAddress. You can now start applying all the functions that you have learned in this blog!

Query:

let IPv4Regex = '[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}';
datatable(commandline:string)
[
    "ping 8.8.8.8",   
    "wget --ftp-user=FTP_USERNAME --ftp-password=FTP_PASSWORD ftp://10.10.10.10/filename.tar.gz",
    "Set-NetIPAddress -InterfaceIndex 12 -IPAddress 192.168.0.1 -PrefixLength 24",  
]    
| extend IPAddress = tostring(xtract(IPv4Regex, 0, commandline))

Questions? Feel free to reach out to me on any of my socials.