→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

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
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
0 REPLIES 0