cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
sukaitsu
Champion Sweeper
The report below lists all of your IP Locations defined in Lansweeper. It automatically formats the network start/end into Network shortname as the first column. It can also sort PCI from Non-PCI IP Locations if you have PCI somewhere in the name of the IP Location. The final column will list how many assets have an IP in that range, and how many IPs should be available in that VLAN/Network.

Select Top 1000000 Case
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 1 Then
tsysIPLocations.Realstart + '/32'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 2 Then
tsysIPLocations.Realstart + '/31'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 3 Then
tsysIPLocations.Realstart + '/30'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 7 Then
tsysIPLocations.Realstart + '/29'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 15 Then
tsysIPLocations.Realstart + '/28'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 31 Then
tsysIPLocations.Realstart + '/27'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 63 Then
tsysIPLocations.Realstart + '/26'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 127 Then
tsysIPLocations.Realstart + '/25'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 255 Then
tsysIPLocations.Realstart + '/24'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 1255 Then
tsysIPLocations.Realstart + '/23'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 3255 Then
tsysIPLocations.Realstart + '/22'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 5255 Then
tsysIPLocations.Realstart + '/21'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 15255 Then
tsysIPLocations.Realstart + '/20'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 31255 Then
tsysIPLocations.Realstart + '/19'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 63255 Then
tsysIPLocations.Realstart + '/18'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 127255 Then
tsysIPLocations.Realstart + '/17'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 255255 Then
tsysIPLocations.Realstart + '/16'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 1255255 Then
tsysIPLocations.Realstart + '/15'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 3255255 Then
tsysIPLocations.Realstart + '/14'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 7255255 Then
tsysIPLocations.Realstart + '/13'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 15255255 Then
tsysIPLocations.Realstart + '/12'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 31255255 Then
tsysIPLocations.Realstart + '/11'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 63255255 Then
tsysIPLocations.Realstart + '/10'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 127255255
Then tsysIPLocations.Realstart + '/9'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 255255255
Then tsysIPLocations.Realstart + '/8'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 1255255255 Then
tsysIPLocations.Realstart + '/7'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 3255255255 Then
tsysIPLocations.Realstart + '/6'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 7255255255 Then
tsysIPLocations.Realstart + '/5'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 15255255255 Then
tsysIPLocations.Realstart + '/4'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 31255255255 Then
tsysIPLocations.Realstart + '/3'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 63255255255 Then
tsysIPLocations.Realstart + '/2'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 127255255255 Then
tsysIPLocations.Realstart + '/1' End As Network,
tsysIPLocations.IPLocation,
tsysIPLocations.Realstart,
tsysIPLocations.Realend,
Case When tsysIPLocations.IPLocation Like '%PCI%' Then 'Yes'
When tsysIPLocations.IPLocation Not Like '%PCI%' Then 'No'
End As [PCI Secured Network],
Case
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 0 Then
CONCAT(Count(tblAssets.IPAddress), '/1')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 1 Then
CONCAT(Count(tblAssets.IPAddress), '/2')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 3 Then
CONCAT(Count(tblAssets.IPAddress), '/2')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 7 Then
CONCAT(Count(tblAssets.IPAddress), '/6')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 15 Then
CONCAT(Count(tblAssets.IPAddress), '/14')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 31 Then
CONCAT(Count(tblAssets.IPAddress), '/30')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 63 Then
CONCAT(Count(tblAssets.IPAddress), '/62')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 127 Then
CONCAT(Count(tblAssets.IPAddress), '/126')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 255 Then
CONCAT(Count(tblAssets.IPAddress), '/254')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 1255 Then
CONCAT(Count(tblAssets.IPAddress), '/512')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 3255 Then
CONCAT(Count(tblAssets.IPAddress), '/1022')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 5255 Then
CONCAT(Count(tblAssets.IPAddress), '/2046')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 15255 Then
CONCAT(Count(tblAssets.IPAddress), '/4094')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 31255 Then
CONCAT(Count(tblAssets.IPAddress), '/8190')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 63255 Then
CONCAT(Count(tblAssets.IPAddress), '/16382')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 127255 Then
CONCAT(Count(tblAssets.IPAddress), '/32766')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 255255 Then
CONCAT(Count(tblAssets.IPAddress), '/65534')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 1255255 Then
CONCAT(Count(tblAssets.IPAddress), '/131072')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 3255255 Then
CONCAT(Count(tblAssets.IPAddress), '/262142')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 7255255 Then
CONCAT(Count(tblAssets.IPAddress), '/524286')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 15255255 Then
CONCAT(Count(tblAssets.IPAddress), '/1048574')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 31255255 Then
CONCAT(Count(tblAssets.IPAddress), '/2097150')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 63255255 Then
CONCAT(Count(tblAssets.IPAddress), '/4194302')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 127255255
Then CONCAT(Count(tblAssets.IPAddress), '/8388606')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 255255255
Then CONCAT(Count(tblAssets.IPAddress), '/16777214')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 1255255255 Then
CONCAT(Count(tblAssets.IPAddress), '/33554430')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 3255255255 Then
CONCAT(Count(tblAssets.IPAddress), '/67108862')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 7255255255 Then
CONCAT(Count(tblAssets.IPAddress), '/134217726')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 15255255255 Then
CONCAT(Count(tblAssets.IPAddress), '/268435454')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 31255255255 Then
CONCAT(Count(tblAssets.IPAddress), '/536870910')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 63255255255 Then
CONCAT(Count(tblAssets.IPAddress), '/1073741822')
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 127255255255 Then
CONCAT(Count(tblAssets.IPAddress), '/2147483646') End As [Used/Total]
From tsysIPLocations
Left Outer Join tblAssets On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State <> '1'
Group By tsysIPLocations.IPLocation,
tsysIPLocations.Realstart,
tsysIPLocations.Realend,
tsysIPLocations.StartIP,
tsysIPLocations.EndIP
Order By tsysIPLocations.StartIP


Thank you,

Jeffrey
Thank you, Jeffrey Smith Enterprise Applications Security (319) 499-6310 JefSmith@geico.com
4 REPLIES 4
dkramer
Engaged Sweeper II
[size=4]Modified to work on SQL Server 2008r2 and added a percentage used column

Edited: Forgot that I removed the Where clause of the query during debugging


Select Top 1000000 Case
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 1 Then
tsysIPLocations.Realstart + '/32'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 2 Then
tsysIPLocations.Realstart + '/31'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 3 Then
tsysIPLocations.Realstart + '/30'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 7 Then
tsysIPLocations.Realstart + '/29'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 15 Then
tsysIPLocations.Realstart + '/28'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 31 Then
tsysIPLocations.Realstart + '/27'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 63 Then
tsysIPLocations.Realstart + '/26'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 127 Then
tsysIPLocations.Realstart + '/25'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 255 Then
tsysIPLocations.Realstart + '/24'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 1255 Then
tsysIPLocations.Realstart + '/23'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 3255 Then
tsysIPLocations.Realstart + '/22'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 5255 Then
tsysIPLocations.Realstart + '/21'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 15255 Then
tsysIPLocations.Realstart + '/20'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 31255 Then
tsysIPLocations.Realstart + '/19'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 63255 Then
tsysIPLocations.Realstart + '/18'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 127255 Then
tsysIPLocations.Realstart + '/17'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 255255 Then
tsysIPLocations.Realstart + '/16'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 1255255 Then
tsysIPLocations.Realstart + '/15'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 3255255 Then
tsysIPLocations.Realstart + '/14'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 7255255 Then
tsysIPLocations.Realstart + '/13'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 15255255 Then
tsysIPLocations.Realstart + '/12'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 31255255 Then
tsysIPLocations.Realstart + '/11'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 63255255 Then
tsysIPLocations.Realstart + '/10'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 127255255
Then tsysIPLocations.Realstart + '/9'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 255255255
Then tsysIPLocations.Realstart + '/8'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 1255255255 Then
tsysIPLocations.Realstart + '/7'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 3255255255 Then
tsysIPLocations.Realstart + '/6'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 7255255255 Then
tsysIPLocations.Realstart + '/5'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 15255255255 Then
tsysIPLocations.Realstart + '/4'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 31255255255 Then
tsysIPLocations.Realstart + '/3'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 63255255255 Then
tsysIPLocations.Realstart + '/2'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 127255255255 Then
tsysIPLocations.Realstart + '/1' End As Network,
tsysIPLocations.IPLocation,
tsysIPLocations.Realstart,
tsysIPLocations.Realend,
Case When tsysIPLocations.IPLocation Like '%PCI%' Then 'Yes'
When tsysIPLocations.IPLocation Not Like '%PCI%' Then 'No'
End As [PCI Secured Network],
Case
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 0 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 1'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 1 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 2'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 3 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 2'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 7 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 6'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 15 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 14'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 31 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 30'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 63 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 62'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 127 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 126'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 255 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 254'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 1255 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 512'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 3255 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 1022'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 5255 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 2046'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 15255 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 4094'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 31255 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 8190'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 63255 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 16382'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 127255 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 32766'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 255255 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 65534'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 1255255 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 131072'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 3255255 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 262142'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 7255255 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 524286'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 15255255 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 1048574'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 31255255 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 2097150'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 63255255 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 4194302'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 127255255
Then CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 8388606'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 255255255
Then CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 16777214'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 1255255255 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 33554430'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 3255255255 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 67108862'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 7255255255 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 134217726'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 15255255255 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 268435454'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 31255255255 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 536870910'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 63255255255 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 1073741822'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 127255255255 Then
CAST(Count(tblAssets.IPAddress) AS VARCHAR) + ' / 2147483646' End As [Used/Total],
Case
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 0 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 1)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 1 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 2)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 3 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 2)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 7 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 6)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 15 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 14)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 31 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 30)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 63 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 62)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 127 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 126)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 254)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 1255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 512)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 3255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 1022)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 5255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 2046)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 15255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 4094)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 31255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 8190)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 63255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 16382)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 127255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 32766)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 255255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 65534)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 1255255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 131072)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 3255255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 262142)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 7255255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 524286)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 15255255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 1048574)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 31255255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 2097150)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 63255255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 4194302)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 127255255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 8388606)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 255255255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 16777214)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 1255255255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 33554430)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 3255255255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 67108862)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 7255255255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 134217726)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 15255255255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 2268435454)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 31255255255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 536870910)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 63255255255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 1073741822)) + '%'
When (tsysIPLocations.EndIP - tsysIPLocations.StartIP) = 127255255255 Then
CONVERT(varchar(10), convert(decimal,100.00 * Count(tblAssets.IPAddress) / 2147483646)) + '%' End As [Used %]
From tsysIPLocations
Left Outer Join tblAssets On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1
Group By tsysIPLocations.IPLocation,
tsysIPLocations.Realstart,
tsysIPLocations.Realend,
tsysIPLocations.StartIP,
tsysIPLocations.EndIP
Order By tsysIPLocations.StartIP


[/size]
Michael_V
Champion Sweeper III
Some people (including myself) sometimes use the ip locations to split up ranges.

Example:
Subnet: 192.168.1.0/24

Iplocation:
Static IP: 192.168.1.1 - 192.168.1.50
DHCP: 192.168.1.51 - 192.168.1.255

PS
This is just for information, your report is useful if you only use subnets.
Michael_V
Champion Sweeper III
Note:
The report does not work for custom start/end ranges.
Example:
Start: 192.168.1.1
End: 192.168.1.254
Michael.V wrote:
Note:
The report does not work for custom start/end ranges.
Example:
Start: 192.168.1.1
End: 192.168.1.254


The report will still display all active IP ranges, but it is correct in saying; if you do not have the IP ranges defined correct, some columns will be blank. The following I'll list the incorrect and then correct way to list put them in:

Incorrect

Start: 192.168.1.1
End: 192.168.1.254

Correct

Start: 192.168.1.0
End: 192.168.1.255

You can use this tool here, to find out what the start and end should be for a network segment.

Thank you,

Jeffrey
Thank you, Jeffrey Smith Enterprise Applications Security (319) 499-6310 JefSmith@geico.com