SQL counts individual subnets in IP column

Question

I need to get a count of all the individual subnets in the IP column and group on the subnet in MS SQL. those. 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 the addresses are stored in a varchar column with a name 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 has not been tested, so I can disconnect somewhere or the bracket is missing.

, , , , , CHARINDEX . " " " " , .

( , , .)

Answer 2

3 , CIDR. -

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

Answer 3

, IP- varchar char, :

SELECT 
  "Subnet" = SUBSTRING(IPAddress, 1, LEN(IPAddress) - CHARINDEX('.', REVERSE(IPAddress))),
  "IP Count" = COUNT(*) 
  FROM [tblIPAddress]
  GROUP BY SUBSTRING(IPAddress, 1, LEN(IPAddress) - CHARINDEX('.', REVERSE(IPAddress)))

:

IPAddress
-----------------
10.10.10.1
192.168.0.1
192.168.1.2
192.168.1.4
192.168.1.5
192.168.0.2
192.168.0.3
10.10.10.3
127.0.0.1

:

Subnet                                             IP Count
-------------------------------------------------- -----------
10.10.10                                           2
127.0.0                                            1
192.168.0                                          3
192.168.1                                          3