SQL script to get VMware tools versions

Brief Description:
SQL Script to get Virtual Machine tools versions and the version of the corresponding parent Host.

We needed to check/verify that all Virtual Machines on our clusters had the correct version of the VMware tools relative to the corresponding Hosts in their parent cluster. Due to the clunkiness of getting across the board information out of virtual center, we looked at pulling the information from the Virtual Center Database and with PowerShell.

The information we needed to get was:

• Virtual Machine Name
• Virtual Machine Operating System
• The Virtual Machine's tools version
• Virtual Machine Tools Status
• Parent Host Name
• Parent Host Build

Solution:

VMWare sees the tools version of the VM as OK if it meets or exceeds the Host product build version.

SQL Script is below, Benj is going to follow up with a PowerShell script to do the same because he thinks it's cooler.

Use [YourVMwareDBName]
select ev.name as VMName,
vm.tools_version, vm.Guest_OS,
case vm.tools_status
when 0 then 'Not Installed' when 1 then 'Not Running' when 2 then 'Out of Date' when 3 then 'OK' when NULL then 'Indeterminate'
end as Tools_Status,
eh.name as Host_Name, ho.Product_Build as Host_Build
from vpx_vm vm join vpx_entity ev on
ev.id = vm.id
join vpx_entity eh on
eh.id = vm.host_id
join vpx_host ho on
ho.id = vm.host_id
where is_template = 0 order by ev.name