Select Top 1000000 a.AssetID,
  Min(a.AssetName) As AssetName,
  Min(a.Domain) As Domain,
  Min(Coalesce(os.Image, at.AssetTypeIcon10)) As icon,
  Min(a.IPAddress) As IPAddress,
  Min(ipl.IPLocation) As IPLocation,
  Min(ac.Manufacturer) As Manufacturer,
  Min(ac.Model) As Model,
  Min(os.OSname) As OsName,
  Min(a.Lastseen) As Lastseen,
  Min(a.Lasttried) As Lasttried,
  Min(pcm.Name) As MetricName,
  Min(pcm.Unit) As Unit,
  Round(Cast(Avg(pcsm.Value) As float), 0) As Average,
  Min(pcsm.Value) As MinValue,
  Max(pcsm.Value) As MaxValue,
  Min(pcsmi.Name) As Identifier
From tblAssets a
  Inner Join tblAssetCustom ac On a.AssetID = ac.AssetID
  Inner Join tsysAssetTypes at On at.AssetType = a.Assettype
  Inner Join tsysIPLocations ipl On ipl.LocationID = a.LocationID
  Inner Join tblState On tblState.State = ac.State
  Left Join tsysOS os On os.OScode = a.OScode
  Inner Join tblPerformanceCountersScan pcs On pcs.AssetId = a.AssetID
  Inner Join tblPerformanceCountersScanMetric pcsm On
    pcsm.PerformanceCountersScanId = pcs.Id
  Inner Join tsysPerformanceCounterMetric pcm On pcm.Id = pcsm.Metric
  Left Join tblPerformanceCountersScanMetricIdentifier pcsmi On
    pcsmi.Id = pcsm.PerformanceCountersScanMetricIdentifierId
Where tblState.Statename = 'Active' And pcs.ScanDateTime > GetDate() - 30 And
  a.Assettype = -1
Group By a.AssetID,
  pcm.Id,
  pcsmi.Id
Order By AssetName,
  MetricName