IP Range Usage Audit

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]