VmWare guests with operating system

[tomecheq]

Hello, I want to create report which will show what versions and types of operating systems has got vmware guests servers.
For now i have report which show what vmware guest is on what physical esxi host but i cannot deal with adding those guest operating systems descrition. All guests are windows byt different versions.

My actual code is:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblVmwareGuest.Name,
tblVmwareGuest.Memory,
tblVmwareGuest.IsRunning As [Guest Status],
Case tblVmwareGuest.ToolsRunningStatus When '3' Then 'Running'
When '2' Then 'Not Running' End As [Tools Running Status],
Case tblVmwareGuest.ToolsStatus When '1' Then 'Not Installed'
When '3' Then 'Current' When '4' Then 'Out Of Date' End As [Tools Status]
From tblAssets
Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
Order By tblAssets.AssetName

Maybe someone have got some idea ?

[tomecheq]

Thank You for report.
I have also create a report few hours later after creating this post.
Sometimes admin have to go for a walk to clear his mind and everything is easier to do 🙂

[Hemoco · Accepted solution]

Please use the report below for this.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblVmwareGuest.Name,
tblVmwareGuest.Memory,
tblVmwareGuest.IsRunning As [Guest Status],
Case tblVmwareGuest.ToolsRunningStatus When '3' Then 'Running'
When '2' Then 'Not Running' End As [Tools Running Status],
Case tblVmwareGuest.ToolsStatus When '1' Then 'Not Installed'
When '3' Then 'Current' When '4' Then 'Out Of Date' End As [Tools Status],
tsysOS.OSname,
tblOperatingsystem.Caption As FullOSname,
tblAssets1.SP
From tblAssets
Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
Inner Join tblVmwareGuestNetwork On tblVmwareGuest.GuestID =
tblVmwareGuestNetwork.GuestID
Inner Join tblAssetMacAddress On tblAssetMacAddress.Mac =
tblVmwareGuestNetwork.MacAddress
Inner Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblAssetMacAddress.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets1.OScode
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets1.AssetID
Order By tblAssets.AssetName