Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblADComputers.Lastchanged As [Last AD change],
  tblAssets.Username,
  tblADusers.Department As [User Department],
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Case
    When tblPortableBattery.AssetID Is Null And tblBattery.AssetID Is Null Then
      'Desktop'
    Else 'Laptop'
  End As [Desktop/Laptop],
  tblAssetCustom.Serialnumber,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblADComputers.OU
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tblADusers On tblADusers.Username = tblAssets.Username And
      tblADusers.Userdomain = tblAssets.Userdomain
  Inner Join tblPortableBattery On
      tblAssets.AssetID = tblPortableBattery.AssetID
  Inner Join tblBattery On tblAssets.AssetID = tblBattery.AssetID
Where (tblAssetCustom.Model Is Null Or tblAssetCustom.Model = '' Or
    tblAssetCustom.Model Not Like '%Virtual%') And tblAssetCustom.State = 1
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tblADComputers.Lastchanged,
  tblAssets.Username,
  tblADusers.Department,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Case
    When tblPortableBattery.AssetID Is Null And tblBattery.AssetID Is Null Then
      'Desktop'
    Else 'Laptop'
  End,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblADComputers.OU
Order By tblAssets.AssetName