Get an Overview of All Devices Scanned in an IP Range
When keeping an eye on your IP Range usage and other statistics, it is useful to also have a complete list of all devices in the IP Ranges created. The report below provides that overview of all active assets that are scanned and are part of an IP Range scanning target you have created. 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.
Assets Scanned by IP Range Query
select
a.AssetID,
a.AssetName,
a.IPAddress,
AssetTypename,
Domain,
Username,
Userdomain,
icon,
IPLocation,
Manufacturer,
Model,
OS,
Servername,
ipstart + ' - ' + ipend as 'IP Range',
Description,
ScanningErrors,
Lastseen,
Lasttried
from (select tblassets.assetid,
AssetName,
Domain,
Username,
Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblassets.IPAddress,
IPLocation,
Manufacturer,
Model,
OSname as OS,
Case
When tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '' Then
'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
Else ''
End As ScanningErrors,
Lastseen,
Lasttried,
tsysAssetTypes.AssetTypename,
tsysIPScanRanges.Description,
tsysIPScanRanges.Servername,
ipstart,
IprangeID,
ipend,
RIGHT('000'+ISNULL(PARSENAME(Ipstart, 4),''),3) +
RIGHT('000'+ISNULL(PARSENAME(Ipstart, 3),''),3) +
RIGHT('000'+ISNULL(PARSENAME(Ipstart, 2),''),3) +
RIGHT('000'+ISNULL(PARSENAME(Ipstart, 1),''),3) as ipstarting,
RIGHT('000'+ISNULL(PARSENAME(ipend, 4),''),3) +
RIGHT('000'+ISNULL(PARSENAME(ipend, 3),''),3) +
RIGHT('000'+ISNULL(PARSENAME(ipend, 2),''),3) +
RIGHT('000'+ISNULL(PARSENAME(ipend, 1),''),3) as ipending,
IPNumeric
from tblassets
inner join tsysIPScanRanges on tsysIPScanRanges.Servername = tblassets.Scanserver
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select Distinct Top 1000000 tblErrors.AssetID As ID,
Max(tblErrors.Teller) As ErrorID
From tblErrors
Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID =
ScanningError.ID
Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
tblErrors.ErrorType
Where tblState.Statename = 'Active' ) as a
where IPNumeric between a.ipstarting and a.ipending
order by IprangeID,
IPAddress
