View IP Usage and Capacity Data
Getting an overview of your subnets, the number of IP addresses in that subnet, how many IP addresses have been used and what the capacity and usage percentage are is part of the basics for IP address management. Using the charts below, you can get the basics in a chart so you can add the to your dashboard to get a simple overview of your subnet capacity status. To use these chart reports, it is highly recommended you follow the setup steps in the IP Address Management Pro tips blog post which covers in detail what minimal setup is required.
The first chart shows the percentage of IP addresses used in an IP Range.
Chart: IP Usage Query
Select tsysIPScanRanges.Ipstart + ' - ' + tsysIPScanRanges.Ipend As 'IP Range',
Case
When Cast(Convert(DECIMAL(10,2),b.[IPs Used]) As float) /
Convert(DECIMAL(10,2),(((Cast(ParseName(tsysIPScanRanges.Ipend,
4) As bigint) - Cast(ParseName(tsysIPScanRanges.Ipstart, 4) As bigint)) *
16777216 + (Cast(ParseName(tsysIPScanRanges.Ipend, 3) As bigint) -
Cast(ParseName(tsysIPScanRanges.Ipstart, 3) As bigint)) * 65536 +
(Cast(ParseName(tsysIPScanRanges.Ipend, 2) As bigint) -
Cast(ParseName(tsysIPScanRanges.Ipstart, 2) As bigint)) * 256 +
(Cast(ParseName(tsysIPScanRanges.Ipend, 1) As bigint) -
Cast(ParseName(tsysIPScanRanges.Ipstart, 1) As bigint)) * 1) + 1)) *
100 Is Null Then 0
Else Cast(Convert(DECIMAL(10,2),b.[IPs Used]) As float) /
Convert(DECIMAL(10,2),(((Cast(ParseName(tsysIPScanRanges.Ipend,
4) As bigint) - Cast(ParseName(tsysIPScanRanges.Ipstart, 4) As bigint)) *
16777216 + (Cast(ParseName(tsysIPScanRanges.Ipend, 3) As bigint) -
Cast(ParseName(tsysIPScanRanges.Ipstart, 3) As bigint)) * 65536 +
(Cast(ParseName(tsysIPScanRanges.Ipend, 2) As bigint) -
Cast(ParseName(tsysIPScanRanges.Ipstart, 2) As bigint)) * 256 +
(Cast(ParseName(tsysIPScanRanges.Ipend, 1) As bigint) -
Cast(ParseName(tsysIPScanRanges.Ipstart, 1) As bigint)) * 1) + 1)) * 100
End As [% IP Space Used]
From tsysIPScanRanges
Left Join (Select Count(tblassets.AssetID) As [IPs Used],
tsysIPScanRanges.IprangeID
From tblassets
Left Join tsysIPScanRanges On tsysIPScanRanges.Servername =
tblassets.Scanserver
Inner Join tblAssetCustom On tblassets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblState.Statename = 'Active' And tblassets.IPNumeric Between
Right('000' + IsNull(ParseName(tsysIPScanRanges.Ipstart, 4), ''), 3) +
Right('000' + IsNull(ParseName(tsysIPScanRanges.Ipstart, 3), ''), 3) +
Right('000' + IsNull(ParseName(tsysIPScanRanges.Ipstart, 2), ''), 3) +
Right('000' + IsNull(ParseName(tsysIPScanRanges.Ipstart, 1), ''),
3) And Right('000' + IsNull(ParseName(tsysIPScanRanges.Ipend, 4), ''),
3) + Right('000' + IsNull(ParseName(tsysIPScanRanges.Ipend, 3), ''), 3) +
Right('000' + IsNull(ParseName(tsysIPScanRanges.Ipend, 2), ''), 3) +
Right('000' + IsNull(ParseName(tsysIPScanRanges.Ipend, 1), ''), 3)
Group By tsysIPScanRanges.IprangeID) As b On
b.IprangeID = tsysIPScanRanges.IprangeID
Order By [% IP Space Used]The following chart shows the size of the IP Ranges you've created in Lansweeper in order to show your potential capacity.
Chart: IP Range Size Query
Select tsysIPScanRanges.Ipstart + ' - ' + tsysIPScanRanges.Ipend As 'IP Range',
((Cast(ParseName(tsysIPScanRanges.Ipend, 4) As bigint) -
Cast(ParseName(tsysIPScanRanges.Ipstart, 4) As bigint)) * 16777216 +
(Cast(ParseName(tsysIPScanRanges.Ipend, 3) As bigint) -
Cast(ParseName(tsysIPScanRanges.Ipstart, 3) As bigint)) * 65536 +
(Cast(ParseName(tsysIPScanRanges.Ipend, 2) As bigint) -
Cast(ParseName(tsysIPScanRanges.Ipstart, 2) As bigint)) * 256 +
(Cast(ParseName(tsysIPScanRanges.Ipend, 1) As bigint) -
Cast(ParseName(tsysIPScanRanges.Ipstart, 1) As bigint)) * 1) +
1 As [Total IP Addresses]
From tsysIPScanRanges
Left Join (Select Count(tblassets.AssetID) As [IPs Used],
tsysIPScanRanges.IprangeID
From tblassets
Left Join tsysIPScanRanges On tsysIPScanRanges.Servername =
tblassets.Scanserver
Inner Join tblAssetCustom On tblassets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblState.Statename = 'Active' And tblassets.IPNumeric Between
Right('000' + IsNull(ParseName(tsysIPScanRanges.Ipstart, 4), ''), 3) +
Right('000' + IsNull(ParseName(tsysIPScanRanges.Ipstart, 3), ''), 3) +
Right('000' + IsNull(ParseName(tsysIPScanRanges.Ipstart, 2), ''), 3) +
Right('000' + IsNull(ParseName(tsysIPScanRanges.Ipstart, 1), ''),
3) And Right('000' + IsNull(ParseName(tsysIPScanRanges.Ipend, 4), ''),
3) + Right('000' + IsNull(ParseName(tsysIPScanRanges.Ipend, 3), ''), 3) +
Right('000' + IsNull(ParseName(tsysIPScanRanges.Ipend, 2), ''), 3) +
Right('000' + IsNull(ParseName(tsysIPScanRanges.Ipend, 1), ''), 3)
Group By tsysIPScanRanges.IprangeID) As b On
b.IprangeID = tsysIPScanRanges.IprangeID
