Virtual Guest machines report- Add guest OS

[Saraee]

I am using the Virtual Guest Machines report copied from https://www.lansweeper.com/report/vmware-virtual-guest-machines/ I would like to add a field for the guest operating system if it is known but everything I've tried results in duplicate entries. Can anyone provide guidance? Thanks!

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

[Andy_Sismey]

Hi,

Is this what your after :

Select Top 1000000 ha.AssetID,
  tblVmwareGuest.Name As Guest,
  tsysOS.OSname,
  tblAssetCustom.Model,
  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.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
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Order By Host,
  Guest

[Saraee]

Sorta, only this removes all of the machines with no known OS. I would like to keep all of the guest machines shown in the original, just fill in the OS if it's known.

[Andy_Sismey]

OK if you change the Inner Join To a Left Join that should fix it as below

Select Top 1000000 ha.AssetID,
  tblVmwareGuest.Name As Guest,
  tsysOS.OSname,
  tblAssetCustom.Model,
  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.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
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Order By Host,
  Guest