Query VMWare vCenter VCDB database for guest power state = OFF

Question: I want to identify virtual machines which are powered off. What query can I run which will return the full list of virtual machines within a VMWare vCenter database where the power state = OFF.

Answer: It is possible to return the recordset by querying the  database. Find a sample query executed on a vcDB database. The query includes some extra information , which you may find useful .

The key column is the POWER_STATE column in the vpxv_vms table. There are different potential  power states  for  a virtual machine . Possible values are off, on, and suspend

SELECT 
VMS.name AS [Server Name], VCR.name AS [Cluster Name], HOS.name as [Host Name], 
VMS.DNS_name, VMS.IP_ADDRESS, HOS.hostid, VMS.power_state FROM vpxv_vms VMS 
 INNER JOIN VPXV_HOSTS HOS on VMS.hostid = HOS.hostid
 INNER JOIN VPXV_COMPUTE_RESOURCE VCR on HOS.farmid = VCR.RESOURCEPOOLID
 WHERE VMS.power_state =  'off' 
 ORDER BY VCR.name, VMS.name

Read more on virtual machines and SQL Server

Query VMWare vCenter VCDB database for guest and host details

VM Memory balloon performance counters (SQL Server DBA)

VM Snapshot Backup Review for databases (SQL Server DBA)