Lansweeper: IP Locations (All Defined)

[sukaitsu]

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