Adding OS Caption to Vmware Virtual Guest Report

[DaveR]

Hey,

I would like to use that report as it pretty much contain everything i need for a request i just received. However, i miss the OS caption in the report. Would you please lend me a hand as i am pretty new to SQL.

Here is the report:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.IPAddress,
  tblVmwareInfo.Version,
  tblVmwareGuest.Name As Guest,
  tblVmwareGuest.Memory,
  tblVmwareGuest.IsRunning As State,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblVmwareGuest.lastchanged
From tblAssets
  Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
  Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
  Inner Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
Order By tblAssets.AssetName,
  Guest

Thank you for your help, this report is almost perfect, only adding a colum with the OScaption and this would be awsome! i dont want to filter by OS, only showing it.

[DaveR]

Thanks!

Work Perfectly.

[Hemoco · Accepted solution]

Please use the following report:

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblVmwareInfo.Version,
tblVmwareGuest.Name As Guest,
tblOperatingsystem.Caption As [os guest],
tblVmwareGuest.Memory,
tblVmwareGuest.IsRunning As State,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblVmwareGuest.lastchanged
From tblAssets
Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
Left Join tblVmwareGuestNetwork On tblVmwareGuest.GuestID =
tblVmwareGuestNetwork.GuestID
Left Join tblAssets tblAssets1
On tblAssets1.Mac = tblVmwareGuestNetwork.MacAddress
Left Join tblOperatingsystem
On tblAssets1.AssetID = tblOperatingsystem.AssetID
Order By tblAssets.AssetName,
Guest