6.5 IP Range Functions and CIDR Matching

IP address analysis is fundamental to security investigation. KQL provides built-in functions for CIDR range matching, private/public classification, and IP comparison, eliminating the need for regex-based IP parsing.

ipv4_is_in_range. CIDR subnet matching

SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == "0"
| extend IsInternal = ipv4_is_in_range(IPAddress, "10.0.0.0/8")
    or ipv4_is_in_range(IPAddress, "172.16.0.0/12")
    or ipv4_is_in_range(IPAddress, "192.168.0.0/16")
| extend IPType = iff(IsInternal, "Internal", "External")
| summarize count() by IPType

ipv4_is_in_range(ip, cidr) returns true if the IP address falls within the CIDR range. This is the correct way to classify IPs, regex patterns like startswith "10." fail on edge cases (10.x in a /16 that does not include all of 10.0.0.0/8).

ipv4_is_in_any_range, matching against multiple ranges

let corporateRanges = dynamic(["10.0.0.0/8", "172.16.0.0/12", "192.168.0.0/16", 
    "100.64.0.0/10"]);  // Include CGNAT
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == "0"
| where not(ipv4_is_in_any_range(IPAddress, corporateRanges))
// All external sign-ins
| summarize count() by IPAddress, tostring(LocationDetails.countryOrRegion)
| sort by count_ desc

ipv4_is_in_any_range checks against a dynamic array of CIDR ranges in one call, cleaner and faster than chaining multiple ipv4_is_in_range calls with or.

ipv4_is_private, built-in RFC 1918 check

| extend IsPrivate = ipv4_is_private(IPAddress)

Equivalent to checking against 10.0.0.0/8, 172.16.0.0/12, and 192.168.0.0/16, but as a single function call. Use this when you only need the RFC 1918 classification. Use ipv4_is_in_any_range when you have additional ranges (CGNAT, documentation ranges, your specific corporate ranges).

ipv4_compare, sorting and comparing IPs numerically

// Sort IPs numerically (not alphabetically)
| sort by ipv4_compare(IPAddress, "0.0.0.0") asc

// Find IPs in a specific range
| where ipv4_compare(IPAddress, "198.51.100.0") >= 0 
    and ipv4_compare(IPAddress, "198.51.100.255") <= 0

String-based IP sorting produces "10.0.0.1, 10.0.0.10, 10.0.0.100, 10.0.0.2" (alphabetical). ipv4_compare produces correct numeric ordering: "10.0.0.1, 10.0.0.2, 10.0.0.10, 10.0.0.100".

Geolocation-based filtering

SigninLogs includes geolocation in the LocationDetails dynamic column. Combine with IP functions for location-aware detection:

SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == "0"
| extend Country = tostring(LocationDetails.countryOrRegion)
| extend City = tostring(LocationDetails.city)
| extend IsPrivate = ipv4_is_private(IPAddress)
| where not(IsPrivate) and isnotempty(Country)
// Detect sign-ins from unexpected countries
| where Country !in ("GB", "US", "IE", "DE", "FR")
| project TimeGenerated, UserPrincipalName, IPAddress, Country, City, AppDisplayName

IP enrichment pipeline

let corporateRanges = dynamic(["10.0.0.0/8", "172.16.0.0/12", "192.168.0.0/16"]);
let vpnRanges = dynamic(["100.64.0.0/10"]);
let cloudRanges = dynamic(["20.0.0.0/8", "40.0.0.0/8", "52.0.0.0/8", "104.0.0.0/8"]);
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == "0"
| extend IPClassification = case(
    ipv4_is_in_any_range(IPAddress, corporateRanges), "Corporate",
    ipv4_is_in_any_range(IPAddress, vpnRanges), "VPN/CGNAT",
    ipv4_is_in_any_range(IPAddress, cloudRanges), "Azure/Cloud",
    ipv4_is_private(IPAddress), "Other Private",
    "External"
)
| extend Country = tostring(LocationDetails.countryOrRegion)
| summarize 
    SignInCount = count(),
    UniqueUsers = dcount(UserPrincipalName),
    UserSample = make_set(UserPrincipalName, 5)
    by IPAddress, IPClassification, Country
| where IPClassification == "External"
| sort by UniqueUsers desc

This pipeline classifies every IP into a meaningful category before analysis. External IPs with many unique users are shared infrastructure (NAT gateways, VPN endpoints, or adversary infrastructure targeting multiple accounts).

Impossible travel detection with IP geolocation

SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == "0"
| extend Country = tostring(LocationDetails.countryOrRegion)
| extend Lat = toreal(LocationDetails.geoCoordinates.latitude)
| extend Lon = toreal(LocationDetails.geoCoordinates.longitude)
| where isnotempty(Lat) and isnotempty(Lon)
| sort by UserPrincipalName, TimeGenerated asc
| extend PrevLat = prev(Lat), PrevLon = prev(Lon), PrevTime = prev(TimeGenerated), PrevUser = prev(UserPrincipalName)
| where UserPrincipalName == PrevUser
| extend TimeDiffMinutes = datetime_diff("minute", TimeGenerated, PrevTime)
// Haversine distance approximation (simplified)
| extend DistanceKm = round(111.0 * sqrt(pow(Lat - PrevLat, 2) + pow(cos(Lat * 3.14159 / 180) * (Lon - PrevLon), 2)), 0)
| extend RequiredSpeedKmh = iff(TimeDiffMinutes > 0, round(DistanceKm / (TimeDiffMinutes / 60.0), 0), 0)
| where RequiredSpeedKmh > 1000 and TimeDiffMinutes < 120  // Faster than commercial flight in <2 hours
| project TimeGenerated, UserPrincipalName, IPAddress, Country, 
    DistanceKm, TimeDiffMinutes, RequiredSpeedKmh
| sort by RequiredSpeedKmh desc

If a user authenticates from London at 14:00 and from Tokyo at 14:30, the required travel speed exceeds 19,000 km/h, physically impossible. The Haversine approximation calculates great-circle distance from latitude/longitude. Any required speed above 1,000 km/h within 2 hours is impossible travel.

False positives: VPN users appear to be in the VPN endpoint's location, not their physical location. Corporate VPN IPs should be excluded from impossible travel detection using your CorporateEgressIPs watchlist.

IPv6 support

KQL has limited IPv6 support. ipv6_is_in_range and ipv6_compare exist but are less commonly used in security analysis because most security log tables (SigninLogs, OfficeActivity) currently record IPv4 addresses:

| where ipv6_is_in_range(IPv6Address, "2001:db8::/32")

As IPv6 adoption increases in enterprise environments, IPv6 functions will become more relevant. Monitor your data sources for IPv6 addresses, if SigninLogs starts recording IPv6, your IPv4-only detection rules will need updating.

Building an IP investigation function

let IPInvestigation = (targetIP: string, lookback: timespan) {
    let signins = SigninLogs
        | where TimeGenerated > ago(lookback)
        | where IPAddress == targetIP;
    let summary = signins
        | summarize 
            TotalSignIns = count(),
            SuccessCount = countif(ResultType == "0"),
            FailCount = countif(ResultType != "0"),
            UniqueUsers = dcount(UserPrincipalName),
            Users = make_set(UserPrincipalName, 20),
            UniqueApps = dcount(AppDisplayName),
            Apps = make_set(AppDisplayName, 10),
            Countries = make_set(tostring(LocationDetails.countryOrRegion)),
            FirstSeen = min(TimeGenerated),
            LastSeen = max(TimeGenerated);
    summary
    | extend IsPrivate = ipv4_is_private(targetIP)
    | extend FailRate = round(100.0 * FailCount / TotalSignIns, 1)
};
IPInvestigation("198.51.100.44", 7d)

Save this as a Sentinel function. Every analyst investigating an IP alert runs IPInvestigation(ip, 7d) and gets a complete profile in 2 seconds: no manual query construction needed.

Subnet enumeration for internal network analysis

// Count sign-ins per /24 subnet
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == "0"
| where ipv4_is_private(IPAddress)
| extend Subnet = strcat(extract(@"(\d+\.\d+\.\d+)\.", 1, IPAddress), ".0/24")
| summarize 
    SignInCount = count(),
    UniqueUsers = dcount(UserPrincipalName),
    UniqueDevices = dcount(tostring(DeviceDetail.displayName))
    by Subnet
| sort by SignInCount desc

This identifies which internal subnets generate the most authentication traffic. Subnets with unexpectedly high authentication volume may host compromised devices performing credential spraying against internal services, or may indicate misconfigured applications causing authentication loops.

Cloud provider IP identification

// Azure IP ranges (simplified — production use the published JSON)
let azureRanges = dynamic(["20.0.0.0/8", "40.64.0.0/10", "52.0.0.0/8", "104.40.0.0/13"]);
let awsRanges = dynamic(["3.0.0.0/8", "18.0.0.0/8", "35.0.0.0/8", "54.0.0.0/8"]);
let gcpRanges = dynamic(["34.0.0.0/8", "35.184.0.0/13"]);

SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == "0"
| where not(ipv4_is_private(IPAddress))
| extend CloudProvider = case(
    ipv4_is_in_any_range(IPAddress, azureRanges), "Azure",
    ipv4_is_in_any_range(IPAddress, awsRanges), "AWS",
    ipv4_is_in_any_range(IPAddress, gcpRanges), "GCP",
    "Non-cloud"
)
| summarize count() by CloudProvider

Sign-ins from cloud provider IPs may indicate: legitimate cloud-hosted applications, adversary infrastructure hosted on cloud providers (common for AiTM proxies), or automated tooling running in cloud environments. Classify them separately from residential and corporate IPs for accurate investigation.

IP reputation scoring pipeline

Combine IP functions with enrichment for a complete reputation assessment:

let corporateRanges = dynamic(["10.0.0.0/8", "172.16.0.0/12", "192.168.0.0/16"]);
let vpnRanges = dynamic(["100.64.0.0/10"]);
let threatIPs = _GetWatchlist('ThreatIntelIPs') | project ThreatIP = SearchKey, ThreatType;
let torExits = _GetWatchlist('TorExitNodes') | project TorIP = SearchKey;

SigninLogs
| where TimeGenerated > ago(4h)
| where ResultType == "0"
| where not(ipv4_is_in_any_range(IPAddress, corporateRanges))
| where not(ipv4_is_in_any_range(IPAddress, vpnRanges))
| extend Country = tostring(LocationDetails.countryOrRegion)
| extend IsHostingIP = ipv4_is_in_any_range(IPAddress, dynamic(["20.0.0.0/8", "3.0.0.0/8", "34.0.0.0/8", "54.0.0.0/8"]))
| join kind=leftouter (threatIPs) on $left.IPAddress == $right.ThreatIP
| join kind=leftouter (torExits) on $left.IPAddress == $right.TorIP
| extend IPScore = 
    iff(isnotempty(ThreatType), 5, 0) +
    iff(isnotempty(TorIP), 4, 0) +
    iff(IsHostingIP, 2, 0) +
    iff(Country !in ("GB", "US", "IE", "DE", "FR"), 1, 0)
| where IPScore >= 3
| project TimeGenerated, UserPrincipalName, IPAddress, Country, IPScore,
    IsThreatIntel = isnotempty(ThreatType), IsTor = isnotempty(TorIP), IsHosting = IsHostingIP
| sort by IPScore desc

This pipeline classifies every external IP across four dimensions: threat intelligence match (5 points), Tor exit node (4 points), hosting provider (2 points), and unexpected country (1 point). IPs scoring 3+ are flagged. The multi-dimensional scoring catches IPs that any single check might miss: a hosting IP from an unexpected country scores 3 even without a threat intelligence match.

Building a network investigation function

let NetworkInvestigation = (targetIP: string, lookback: timespan) {
    union
        (SigninLogs | where TimeGenerated > ago(lookback)
            | where IPAddress == targetIP
            | summarize SignIns = count(), Users = dcount(UserPrincipalName),
                SuccessRate = round(100.0 * countif(ResultType == "0") / count(), 1),
                Apps = make_set(AppDisplayName, 10)
            | extend Source = "SigninLogs"),
        (DeviceNetworkEvents | where TimeGenerated > ago(lookback)
            | where RemoteIP == targetIP
            | summarize Connections = count(), Devices = dcount(DeviceName),
                TotalBytes = sum(SentBytes + ReceivedBytes),
                Ports = make_set(RemotePort, 10)
            | extend Source = "DeviceNetworkEvents"),
        (CommonSecurityLog | where TimeGenerated > ago(lookback)
            | where DestinationIP == targetIP or SourceIP == targetIP
            | summarize FirewallEvents = count(), Actions = make_set(Activity, 5)
            | extend Source = "Firewall")
};
NetworkInvestigation("198.51.100.44", 7d)

This function queries every network-relevant data source for a single IP, sign-in logs, endpoint network events, and firewall logs. The analyst gets a complete picture of the IP's footprint in one call.

Build an IP anomaly detector: for each external IP that appeared in sign-ins today, check whether it appeared in the previous 30 days. IPs that are new today (never seen before in 30 days) and authenticated more than 3 users are suspicious, they may be adversary infrastructure.

NE environmental considerations

NE's detection environment includes specific factors that influence this rule's operation:

Device diversity: 768 P2 corporate workstations with full Defender for Endpoint telemetry, 58 P1 manufacturing workstations with basic cloud-delivered protection, and 3 RHEL rendering servers with Syslog-only coverage. Rules targeting DeviceProcessEvents operate with full fidelity on P2 devices but may have reduced visibility on P1 devices. Manufacturing workstations in Sheffield and Sunderland represent a detection gap for endpoint-level detections.

Network topology: 11 offices connected via Palo Alto SD-WAN with full-mesh connectivity. The SD-WAN firewall logs feed CommonSecurityLog in Sentinel. Cross-site lateral movement generates firewall allow events that correlate with DeviceLogonEvents, enabling multi-source detection that single-table rules cannot achieve.

User population: 810 users with distinct behavioral profiles, office workers (predictable hours, consistent applications), field engineers (variable hours, travel patterns), IT administrators (elevated privilege, broad access patterns), and manufacturing operators (fixed shifts, limited application access). Each user population has different detection baselines.

Troubleshooting

"The query returns an error I do not understand." KQL error messages reference the specific line and operator that failed. Read the error message from left to right: it names the operator, the expected input type, and the actual input type. Most errors are type mismatches (passing a string where a datetime is expected) or field name typos. The getschema operator shows every field name and type for any table: TableName | getschema.

"The query runs but returns unexpected results." Add | take 10 after each operator in the pipeline and examine the intermediate output. This reveals WHERE the data transforms in a way you did not expect. Debug the pipeline stage by stage, not the entire query at once.