→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
fjca
Champion Sweeper II
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...
1 ACCEPTED SOLUTION
MikeMc
Champion Sweeper II
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

View solution in original post

2 REPLIES 2
fjca
Champion Sweeper II
And it worked like a charm, thank you.
MikeMc
Champion Sweeper II
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