Select CONCAT(ipstart, ' - ', ipend) as IPRange,
Count(tblassets.AssetID) As [IPs Used],
Cast(ISNULL(sum(a.scantime)/60,0) as decimal(16,2)) as [Scantime (Minutes)],
Cast(ISNULL(avg(a.scantime/60),0) as decimal(16,2)) as [Average Scantime (Minutes)]
From tblassets
Inner Join tblAssetCustom On tblassets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
inner Join tsysIPScanRanges On tsysIPScanRanges.Servername =
tblassets.Scanserver
inner join tblComputersystem on tblComputersystem.AssetID = tblassets.AssetID
left join (select sum(ISNULL(scantime, 0)) as scantime,
assetid
from TsysLastscan
group by assetid) as a on a.AssetID = tblassets.AssetID
Where tblState.Statename = 'Active' and tblassets.IPNumeric Between
ParseName(tsysIPScanRanges.Ipstart, 4) +
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 ParseName(tsysIPScanRanges.Ipend, 4)+ 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,
ipstart, ipend
order by IprangeID
