Report with IP count for each IP Location

[fjca]

Hi all,

We have 9 different locations and about 40 IP Ranges defined on our Asset Group.

What I would like to have is a report that gives us a count for the number of IP's in each IP Range.
Something like this:

10.1.0.0 - 10.1.0.254 : 46
10.2.0.0 - 10.2.0.254 : 23
192.168.254.0 - 192.168.254.254 : 18

I've been testing with tsysLocations and tblAssets.IPAddress tables, but no luck so far...

[fjca]

And it worked like a charm, thank you.

[MikeMc · Accepted solution]

This lists all active asset numbers for each range registered in Lansweeper except for undefined locations.

SELECT IPLocation
	,RealStart
	,RealEnd
	,(
		SELECT DISTINCT COUNT(*)
		FROM tblAssets
		INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
		WHERE tblAssetCustom.STATE = 1
			AND (
				tblAssets.IPNumeric >= tsysIPLocations.StartIP
				AND tblAssets.IPNumeric <= tsysIPLocations.EndIP
				)
		) AS TotalAssets
FROM tsysIPLocations