This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject
Community FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Lansweeper: IP Locations (All Active)

sukaitsu
Champion Sweeper

‎12-10-2014 02:51 PM

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

‎12-21-2014 08:31 AM

[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]
0 Kudos
Michael_V
Champion Sweeper III

‎12-17-2014 09:04 AM

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.
0 Kudos
Michael_V
Champion Sweeper III

‎12-16-2014 03:04 PM

Note:
The report does not work for custom start/end ranges.
Example:
Start: 192.168.1.1
End: 192.168.1.254
0 Kudos
sukaitsu
Champion Sweeper
In response to Michael_V

‎12-16-2014 03:11 PM

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

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now