Friday, April 4, 2008

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

  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.

1 comment:

Anonymous said...

Hello everyone,

First, I just want to apologize if I make some mistakes in my English, actually I'm french.
I know this post as been posted a long time ago but it is the only one I could find on the internet about SQL query and Virtual Center, so I give a try.

I'm looking for a way to get, by using a SQL query, the virtual machines that use RDM. After a lot of research, I couldn't find the table in the database that could give me this information.

If you have any idea about this, or any link that could help me, I take it.
Thank you very much and have a nice day.

All Posts

printQueue AD objects for 2003 ClusterVirtualCenter Physical to VirtualVirtual 2003 MSCS Cluster in ESX VI3
Finding duplicate DNS recordsCommand-line automation – Echo and macrosCommand-line automation – set
Command-line automation - errorlevels and ifCommand-line automation - find and findstrBuilding blocks of command-line automation - FOR
Useful PowerShell command-line operationsMSCS 2003 Cluster Virtual Server ComponentsServer-side process for simple file access
OpsMgr 2007 performance script - VMware datastores...Enumerating URLs in Internet ExplorerNTLM Trusts between 2003 and NT4
2003 Servers with Hibernation enabledReading Shortcuts with PowerShell and VBSModifying DLL Resources
Automatically mapping printersSimple string encryption with PowerShellUseful NTFS and security command-line operations
Useful Windows Printer command-line operationsUseful Windows MSCS Cluster command-line operation...Useful VMware ESX and VC command-line operations
Useful general command-line operationsUseful DNS, DHCP and WINS command-line operationsUseful Active Directory command-line operations
Useful command-linesCreating secedit templates with PowerShellFixing Permissions with NTFS intra-volume moves
Converting filetime with vbs and PowerShellDifference between bat and cmdReplica Domain for Authentication
Troubleshooting Windows PrintingRenaming a user account in ADOpsMgr 2007 Reports - Sorting, Filtering, Charting...
WMIC XSL CSV output formattingEnumerating File Server ResourcesWMIC Custom Alias and Format
AD site discoveryPassing Parameters between OpsMgr and SSRSAnalyzing Windows Kernel Dumps
Process list with command-line argumentsOpsMgr 2007 Customized Reporting - SQL QueriesPreventing accidental NTFS data moves
FSRM and NTFS Quotas in 2003 R2PowerShell Deleting NTFS Alternate Data StreamsNTFS links - reparse, symbolic, hard, junction
IE Warnings when files are executedPowerShell Low-level keyboard hookCross-forest authentication and GP processing
Deleting Invalid SMS 2003 Distribution PointsCross-forest authentication and site synchronizati...Determining AD attribute replication
AD Security vs Distribution GroupsTroubleshooting cross-forest trust secure channels...RIS cross-domain access
Large SMS Web Reports return Error 500Troubleshooting SMS 2003 MP and SLPRemotely determine physical memory
VMware SDK with PowershellSpinning Excel Pie ChartPoke-Info PowerShell script
Reading web content with PowerShellAutomated Cluster File Security and PurgingManaging printers at the command-line
File System Filters and minifiltersOpsMgr 2007 SSRS Reports using SQL 2005 XMLAccess Based Enumeration in 2003 and MSCS
Find VM snapshots in ESX/VCComparing MSCS/VMware/DFS File & PrintModifying Exchange mailbox permissions
Nested 'for /f' catch-allPowerShell FindFirstFileW bypassing MAX_PATHRunning PowerSell Scripts from ASP.Net
Binary <-> Hex String files with PowershellOpsMgr 2007 Current Performance InstancesImpersonating a user without passwords
Running a process in the secure winlogon desktopShadow an XP Terminal Services sessionFind where a user is logged on from
Active Directory _msdcs DNS zonesUnlocking XP/2003 without passwords2003 Cluster-enabled scheduled tasks
Purging aged files from the filesystemFinding customised ADM templates in ADDomain local security groups for cross-forest secu...
Account Management eventlog auditingVMware cluster/Virtual Center StatisticsRunning scheduled tasks as a non-administrator
Audit Windows 2003 print server usageActive Directory DiagnosticsViewing NTFS information with nfi and diskedit
Performance Tuning for 2003 File ServersChecking ESX/VC VMs for snapshotsShowing non-persistent devices in device manager
Implementing an MSCS 2003 server clusterFinding users on a subnetWMI filter for subnet filtered Group Policy
Testing DNS records for scavengingRefreshing Computer Account AD Group MembershipTesting Network Ports from Windows
Using Recovery Console with RISPAE Boot.ini Switch for DEP or 4GB+ memoryUsing 32-bit COM objects on x64 platforms
Active Directory Organizational Unit (OU) DesignTroubleshooting computer accounts in an Active Dir...260+ character MAX_PATH limitations in filenames
Create or modify a security template for NTFS perm...Find where a user is connecting from through WMISDDL syntax in secedit security templates

About Me

I’ve worked in IT for over 20 years, and I know just about enough to realise that I don’t know very much.