1E 8.1 (on-premises)

Determines if an IP Address is within a specified range or ranges.

(String, mandatory): IpAddress. The IPv4 address in print notation, for example, "192.168.0.1".

(String, mandatory): IpRange. This is the IPv4 range that provides the boundaries for the range checking. Multiple ranges can be supplied, with a comma as a separator. An IpRange is two IP Addresses separated by a hyphen. For example: "192.168.0.0-192.168.255.255,1.2.3.4-2.3.4.5"

(Integer, optional): AllowNullAddresses. This can be 0 or 1. Set to 1 if a NULL is supplied as parameter 1, then it will cause the function to return 1 (true). This acts as a passthrough for sets of data where only some rows will have an IpAddress, but the other data is still valuable. In effect, only IPAddresses which exist will be range checked, the rest will be allowed through. Possible values are 1, (allow null IPs) or 0 (do not allow null IPs). The default is 0.

(Integer, optional): AllowIpv6Addresses. This can be 0 or 1. Set to 1 if an IPv6 address is supplied as IpAddress and/or IpRange, then it will cause the function to return 1 (true). Similar to parameter 3, this will allow through IPv6 Addresses, but will continue filtering IPv4 addresses. Possible values are 1, (allow IPv6 addresss) or 0 (do not allow IPv6 addresses). The default is 0.

(Integer) True (1) or false (0).

// Input is "192.168.1.1", which is checked against the ranges "1.2.3.4-2.3.4.5" and "192.168.0.0-192.168.2.2"
// Returns 1 (true)
SELECT IPINRANGE("192.168.1.1","1.2.3.4-2.3.4.5,192.168.0.0-192.168.2.2");
 
// Input is "192.168.1.1", which is checked against the ranges "1.2.3.4-2.3.4.5" and "192.168.0.0-192.168.2.2". 
// The optional parameters are set to 0, their defaults. This is functionally the same as the above example.
// Returns 1 (true)
SELECT IPINRANGE("192.168.1.1","1.2.3.4-2.3.4.5,192.168.0.0-192.168.2.2",0,0);
 
// This will return 0 (false) - the IP is not in the range
// Returns 0 (false)
SELECT IPINRANGE("192.168.66.77,"192.168.0.0-192.168.66.76");
 
// This allows the following IP through even though it is an IPv6 address and thus not range checked
// Returns 1 (true)
SELECT IPINRANGE("::1","1.2.3.4-2.3.4.5",0,1);
 
// This allows the following IP though even though it is NULL and thus cannot be range checked
// Returns 1 (true)
SELECT IPINRANGE(NULL,"1.2.3.4-2.3.4.5",1,0);
 
// This will still return 1 (true) - IPv6 ranges are discarded (though the agent will log a warning)
// Returns 1 (true)
SELECT IPINRANGE("192.168.1.1","::1-::55",192.168.0.0-192.168.2.2");
 
// This will return 0 (false) - the ranges cannot be correctly parsed. Though ::1-::55 would be discarded and a 
// warning will be logged, jaffa-cake will cause a delicious error. This won't stop the operation, it'll just 
// return 0 instead.
// Returns 0 (false)
SELECT IPINRANGE("192.168.1.1","::1-::55",jaffa-cake");
 
// More meaningful usage: select everything from the $TCP_Live historic table with an IPv4 address within a range
@raw = SELECT * from $TCP_Live;
SELECT * from @raw where IPINRANGE(@raw.IpAddress,"192.168.0.0-192.168.255.255");