Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblVmwareGuest.Ipv4Address As [Guest IP],
  tsysAssetTypes.AssetTypename,
  tblAssets1.AssetName As Host,
  tblAssets1.IPAddress As [Host IP],
  tblAssets.Lastseen,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssetCustom.Location,
  tblAssetCustom.Department,
  tblAssets.Domain,
  tblAssets.Description
From tblAssets
  Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tblVmwareInfo On tblVmwareGuest.HostID = tblVmwareInfo.VmwareID
  Left Join tblAssets tblAssets1 On tblAssets1.AssetID = tblVmwareInfo.AssetID
Where tsysAssetTypes.AssetTypename Like '%Windows%' And tblAssets.Lastseen >
  GetDate() - 120
Order By tsysAssetTypes.AssetTypename,
  tblAssets.AssetName