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