cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Saraee
Engaged Sweeper II
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
3 REPLIES 3
Andy_Sismey
Champion Sweeper III
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
Engaged Sweeper II
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
Champion Sweeper III
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