VMware cluster/Virtual Center Statistics

This post provides a set of SQL commands to extract data from a VMware Virtual Center database. The statistics provided include VMs and their config LUNs, VM disk total and free space (includes RDM), VMFS volumes used and free space, DRS migrations that have occurred, manual migrations that have occurred, Virtual Machine information, and the current distribution of VMs across cluster nodes

Set the date suffix:

• for /f "tokens=1-8 delims=/:. " %i in ('echo %date%') do set DateFlat=%l%k%j
  

VMs and their config LUNs

1. sqlcmd -S %server% -d %database% -W -s "," -Q "select VMS.Name as 'VM Name', TDS.Name as 'Config Datastore', SubString(ConfigFileName, CharIndex('/', ConfigFileName, Len('sanfs://vmfs'))+1, 255) as 'Config Path' from vpxv_vms VMS inner join vpx_datastore TDS on TDS.Storage_URL = SubString(VMS.ConfigFileName, 1, CharIndex('/', VMS.ConfigFileName, Len('sanfs://vmfs'))) Order By 'Config Datastore'" > VMConfigLuns_%DateFlat%.csv
   

VM disk total and free space (includes RDM)

1. sqlcmd -S %server% -d %database% -W -s "," -Q "select VM.Name, Cast(Round(Sum(Cast(VDISK.CAPACITY as numeric))/1024/1024/1024, 1) as int) as 'Total Disk', Cast(Round(Sum(Cast(VDISK.FREE_SPACE as numeric))/1024/1024/1024, 1) as int) as 'Free Disk' from dbo.VPX_GUEST_DISK VDISK inner join VPXV_VMS VM on VDISK.VM_ID = VM.VMID group by VM.Name compute sum(Cast(Round(Sum(Cast(VDISK.CAPACITY as numeric))/1024/1024/1024, 1) as int))" > VMDiskInfo_%DateFlat%.csv
   

VMFS volumes used and free space

1. sqlcmd -S %server% -d %database% -W -s "," -Q "select Name, Cast(Round(Cast(Capacity as numeric)/1024/1024/1024, -1) as int) as 'Total Space', Cast(Round(Cast(Free_Space as numeric)/1024/1024/1024, -1) as int) as 'Free Space', Type from vpx_Datastore compute sum(Cast(Round(Cast(Capacity as numeric)/1024/1024/1024, -1) as int))" > VMFS_volumes_%DateFlat%.csv
   

DRS migrations that have occurred

1. sqlcmd -S %server% -d %database% -W -s "," -Q "select DateAdd(Hour, 10, Create_Time) as 'Relocation Finished', VM_Name, Host_Name as 'Host Destination', (select Host_Name from dbo.VPX_EVENT Where Chain_ID = EVTDEST.Chain_ID and event_type = 'vim.event.VMBeingHotMigratedEvent') as 'Host Source', ComputeResource_Name, DataCenter_Name from dbo.VPX_EVENT EVTDEST where event_type = 'vim.event.DrsVmMigratedEvent'" > VMDRSMigrations_%DateFlat%.csv
   

Manual migrations that have occurred

1. sqlcmd -S %server% -d %database% -W -s "," -Q "select DateAdd(Hour, 10, Create_Time) as 'Relocation Start', UserName, VM_Name, Host_Name as 'Host Source', (select Host_Name from dbo.VPX_EVENT Where Chain_ID = EVTDEST.Chain_ID and event_type = 'vim.event.VmRelocatedEvent') as 'Host Destination', ComputeResource_Name, DataCenter_Name from dbo.VPX_EVENT EVTDEST where event_type = 'vim.event.VmBeingRelocatedEvent'" > VMManualMigrations_%DateFlat%.csv
   

Virtual Machine information

1. sqlcmd -S %server% -d %database% -W -s "," -Q "select ENT.Name as 'Name', Lower(VM.DNS_Name) as 'DNS Name', VH.DNS_NAME as 'Host', Guest_OS as 'OS', Mem_Size_MB as 'Mem', Num_VCPU as 'CPU', Num_NIC as 'NIC', VM.IP_Address as 'IP', NET.MAC_Address as 'MAC Address', VM.FILE_Name as 'VMX location' from vpx_vm VM inner join VPX_GUEST_NET_ADAPTER NET on VM.ID = NET.VM_ID inner join VPX_ENTITY ENT on VM.ID = ENT.ID inner join vpx_host VH on VM.HOST_ID = VH.ID order by ENT.Name" > VMInfo_%DateFlat%.csv
   

Current distribution of VMs across cluster nodes

1. sqlcmd -S %server% -d %database% -W -s "," -Q "Select VH.DNS_NAME as 'Host', count(VM.HOST_ID) from vpx_vm VM inner join vpx_host VH on VM.HOST_ID = VH.ID group by VM.HOST_ID, VH.DNS_NAME order by 'Host'" > VMDistribution_%DateFlat%.csv

Notes:

1. These commands were created for use with a SQL 2005 database and the sqlcmd.exe utility. You can also just manually run the query string in SQL enterprise manager/management studio if you prefer.
   
2. The first command below will set a variable used to provide a date suffix appended to the output of each command, ie YYYYMMDD - 20080404
   
3. SQL queries using the DateAdd() function are adding GMT+10 (my local timezone), change the value to your GMT offset to modify the UTC times recorded in the database.
   

Wayne's World of IT (WWoIT), Copyright 2008 Wayne Martin.