Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssetCustom.PurchaseDate As [Purchase Date],
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.Username,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  ProcCapacity.CPU,
  tblAssets.Memory,
  tsysIPLocations.IPLocation,
  tblAssets.Lastseen
From tblAssets
  Inner Join (Select tblAssets.AssetID,
        Max(Replace(Replace(tblProcessor.Name, '(R)', ''), '(TM)', '')) As CPU
      From tblAssets
        Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
      Group By tblAssets.AssetID) As ProcCapacity On ProcCapacity.AssetID =
    tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
    And tsysIPLocations.EndIP >= tblAssets.IPNumeric
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName