Useful vCenter Database Queries

Author: NetworkAdminKB.com

Created: 2011-09-02

Modified: 2011-11-06

Information:

Below are some examples of vCenter Database queries that you can run to generate quick reports for management, find problem VMs, or do capacity planning for your environment.  Once you get a good idea about where things are stored in the vCenter Database you will be able to write your own queries to find out the information you need very quickly and without third party tools.

Assumptions:

• You are using the full version or Windows 2005 and not the embedded version
  • You know how to run SQL queries using SQL Management studio
• You know the name of the vCenter Database, the SQL Sever and have the rights to run the query.
• If using the embedded version of SQL you know how to connect to it and run queries.
  • How to connect to the embedded SQL Server (SQLEXP_VIM) used by VirtualCenter
  • How to move VirtualCenter Database from SQL Express to SQL Server 2005

The Queries:

--List DNS names and IP Addresses that of VMs in vCenter

select dns_name, ip_address from vpx_vm

  order by dns_name

--Find VMs by IP Addresses in vCenter

select dns_name, ip_address from vpx_vm

  where ip_address like '10.10.%' --or ip_address like '10.20.%'

  order by dns_name

--Find VMs by DNS name in vCenter

select dns_name, ip_address from vpx_vm

  where dns_name like 'myname%' --or dns_name like '%paritial%'

  order by dns_name

--Search for Secondary IP Addresses of VMs in vCenter

--the guest_ip_stack holds all ip addresses of the vm

select dns_name, ip_address, Guest_ip_stack from vpx_vm

  where Guest_ip_stack like '%10.1.2.3%'

  order by dns_name

--Find Drive Capacity of VMs in vCenter

Select e.name, vm.path, vm.capacity, vm.free_space from

  VPX_ENTITY e JOIN

  vpx_guest_disk vm ON e.id = vm.vm_id

--Sort Drive Capacity of VMs in vCenter

Select e.name, vm.path, CAST(vm.capacity AS money) AS Capacity, vm.free_space from

  VPX_ENTITY e JOIN

  vpx_guest_disk vm ON e.id = vm.vm_id

  Order by Capacity desc

--Sort by VM Name with Capacity in GB of VMs in vCenter

Select e.name,

       vm.path,

       CAST(vm.capacity AS money) / (1024 * 1024 * 1024) AS Capacity_GB,

       CAST(vm.free_space AS money) / (1024 * 1024 * 1024) AS Free_GB  from

  VPX_ENTITY e JOIN

  vpx_guest_disk vm ON e.id = vm.vm_id

  Order by e.name

Article ID: 441, Created On: 11/6/2011, Modified: 11/6/2011