→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
tomecheq
Engaged Sweeper
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 ?
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
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

View solution in original post

2 REPLIES 2
tomecheq
Engaged Sweeper
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
Lansweeper Alumni
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