Query VMWare vCenter VCDB database for guest power state = OFF

05 November,2018 by Jack Vamvas

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)

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Query VMWare vCenter VCDB database for guest power state = OFF


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer