Class C Subnet device count

cscoengineer

This is a quick SQL code to count the number of devices within a /24 subnet.  I'm sure it can be expanded to accomindate any cidr.

SELECT

  SUBSTRING(IP_Address, 1, LEN(IP_Address) - CHARINDEX('.', REVERSE(IP_Address)))+'.0/24' as [Subnet],

  COUNT(*) as [Count]

  FROM nodes

  GROUP BY SUBSTRING(IP_Address, 1, LEN(IP_Address) - CHARINDEX('.', REVERSE(IP_Address)))

  order by [count] desc

Thanks

Amit