SQL count distinct subnets in an IP column

Question

I need to get a get a count of all the distinct subnets in an IP column and group by the subnet on MS SQL. ie.. count all ips that have a subnet of 192.168.0,192.168.1,10.10.10 and so on.

Any help is appreciated. Thanks

Answer 1

This is not super-efficient, but assuming that the addresses are stored in a varchar column named IPAddress, you can do:

SELECT
    SUBSTRING(IPAddress, 1, LEN(IPAddress) - CHARINDEX('.',REVERSE(IPAddress))),
    COUNT(*)
FROM
    ...
GROUP BY
    SUBSTRING(IPAddress, 1, LEN(IPAddress) - CHARINDEX('.',REVERSE(IPAddress)))

This hasn't been tested, so I may be off by one somewhere or missing a parenthesis.

The basic idea is that you want to chop off the end by finding the last dot, and to find the last dot you instead reverse the string and find the first dot, which CHARINDEX will do quite easily. To transform the "first dot" position back to the "last dot" position in the original string, you subtract the position from the original length.

(If my assumption is wrong and it's not stored as text, you're unlikely to get a meaningful answer unless you also give the data type.)

Answer 2

Simply taking the first 3 octets will not work if you are using CIDR. You need to do something like this

DECLARE @Subnet varchar(15)
DECLARE @bits int
DECLARE @VLSMSuffix int
DECLARE @IP TABLE (IPAddr varchar(15), Running binary(8))

INSERT @IP
SELECT '10.10.19.2', NULL UNION  -- 00001010 00001010 00010011 00000010
SELECT '10.10.10.5', NULL UNION  -- 00001010 00001010 00001010 00000101
SELECT '10.10.11.2', NULL        -- 00001010 00001010 00001011 00000010
SET @Subnet = '10.10.10.0'       -- 00001010 00001010 00001010 00000000
SET @VLSMSuffix = 24             -- # of bits in subnet mask
                                 -- 10.10.11.2 is part of the 10.10.10.0/23 CIDR block
DECLARE @Fun bigint
SET @Fun = CAST(CAST(16777216 as bigint) * PARSENAME(@Subnet, 4) 
                                 + 65536 * PARSENAME(@Subnet, 3) 
                                   + 256 * PARSENAME(@Subnet, 2) 
                                         + PARSENAME(@Subnet, 1) as binary(8))

UPDATE @IP
SET Running = CAST(CAST(16777216 as bigint) * PARSENAME(IPAddr, 4) 
                                    + 65536 * PARSENAME(IPAddr, 3) 
                                      + 256 * PARSENAME(IPAddr, 2) 
                                            + PARSENAME(IPAddr, 1) as binary(8))

-- determine subnet mask
DECLARE @Scissors bigint
SELECT @Scissors = 4294967296 - POWER(CAST(2 AS bigint), CAST(32 AS bigint) - @VLSMSuffix)

SELECT @Subnet [Subnet], COUNT(IPAddr) [Count] 
FROM @IP 
WHERE  @Scissors & Running = @Fun