VMware Tools status report

[KevinA-REJIS]

There was a report I found here several months ago (can't seem to find it now so I can't credit the original author) that displayed the state of VMware Tools on all the guest VMs (Current, Out of date, Not installed, & Unmanaged). It worked in Lansweeper 6.x but doesn't appear to work in 7.x, it now returns no results. Here's the code, any help is appreciated!

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblVmwareGuest.Name,
  tblVmwareGuest.ToolsVersion,
  Case tblVmwareGuest.ToolsVersionStatus
    When '1' Then 'Current'
    When '2' Then 'Out of date'
    When '3' Then 'Not installed'
    When '4' Then 'Unmanaged'
  End As ToolsVersionStatus,
  Case tblVmwareGuest.ToolsRunningStatus
    When '1' Then 'Executing scripts'
    When '2' Then 'Not running'
    When '3' Then 'Running'
  End As ToolsRunningStatus
From tblAssetCustom
  Inner Join (tblVmwareGuest
  Inner Join tblAssets On tblVmwareGuest.AssetID = tblAssets.AssetID) On
    tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblVmwareInfo On tblVmwareInfo.AssetID = tblAssets.AssetID
Where tblState.Statename = 'Active'
Order By tblAssets.IPNumeric,
  tblAssets.AssetName,
  tblVmwareGuest.Name

[KevinA-REJIS]

Lansweeper support helped me out, here's the code for the updated report:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblVmwareGuest.Name,
  tblVmwareGuest.ToolsVersion,
  Case tblVmwareGuest.ToolsVersionStatus
    When '1' Then 'Current'
    When '2' Then 'Out of date'
    When '3' Then 'Not installed'
    When '4' Then 'Unmanaged'
  End As ToolsVersionStatus,
  Case tblVmwareGuest.ToolsRunningStatus
    When '1' Then 'Executing scripts'
    When '2' Then 'Not running'
    When '3' Then 'Running'
  End As ToolsRunningStatus
From tsysAssetTypes
  Right Outer Join (tblVmwareGuest
  Inner Join tblAssets On tblVmwareGuest.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State) On
    tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where tblState.Statename = 'Active'
Order By tblAssets.IPNumeric,
  tblAssets.AssetName,
  tblVmwareGuest.Name