Part of scanning your network and having a complete overview of your network also includes having an overview of which IP addresses are being used in which subnets. This way you can identify duplicate IP usage and keep an eye on the overall status of IP address usage in all of your subnets.
To use this report, it is highly recommended you follow the setup steps in the IP Address Management Pro tips blog post which covers in detail what you need to do to take full advantage of this report.
The report below will provide an overview of all IP Range scanning targets along with how many IP addresses a range contains, how many assets were scanned in that range and what the usage percentage of the range is.
IP Range Usage Query
Select tsysIPScanRanges.Servername,
tsysIPScanRanges.Description,
tsysIPScanRanges.Enabled,
tsysIPScanRanges.Ipstart,
tsysIPScanRanges.Ipend,
((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],
Case
When (((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) -
b.[IPs Used] Is Null Then ((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
Else (((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) -
b.[IPs Used]
End As [IPs Available],
Case
When b.[IPs Used] Is Null Then 0
Else b.[IPs Used]
End As [IPs Used],
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],
tsysIPScanRanges.LastIPscan As [Last Scan Time]
From tsysIPScanRanges
Left Join (Select Count(tblassets.AssetID) As [IPs Used],
tsysIPScanRanges.IprangeID
From tblassets
Inner Join tblAssetCustom On tblassets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysIPScanRanges On tsysIPScanRanges.Servername =
tblassets.Scanserver
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]
