Select Top 1000000 ha.AssetID,
  tblVmwareGuest.Name As Guest,
  ha.AssetName As Host,
  h.Version,
  tblVmwareGuest.Memory,
  tblVmwareGuest.IsRunning As State,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblVmwareGuest.lastchanged,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename As Type,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tblAssets.Firstseen,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
  Inner Join tblVmwareInfo h On tblVmwareGuest.HostID = h.VmwareID
  Inner Join tblAssets ha On ha.AssetID = h.AssetID
  Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
  Left Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
  Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Order By Host,
  Guest