A number of you sent me some excellent questions about this topic, and my favorite asked me for the query that you can use to get this information DIRECTLY from the VMware vCenter database.
SO… here you go! Just plug in your VM name (which might differ from your FQDN name). Feel free to modify this as needed for your own purposes.
This query will fetch the Co-Stop time from within the vCenter database server. This value is the summation of Co-Stop values over a five minute interval for the last day, if the vCenter statistics collection process is set at the default (and it usually is).
| SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT vv.[name] AS VMName, vh.NAME AS HostName, cl.name AS VMwareClusterName, vhsd.sample_time AS SampledTime, vhsd.SAMPLE_INTERVAL AS SampleInterval, vhsd.stat_value AS CPUCoStopTime5mSumInMS, vv.num_vcpu AS VMvCPUCount, vhsd.DEVICE_NAME AS vCPUNumber FROM dbo.VPXV_HIST_STAT_DAILY vhsd JOIN dbo.vpxv_vms AS vv ON vhsd.entity = N’vm-‘+cast(vv.vmid AS NVARCHAR) JOIN dbo.vpxv_hosts AS vh ON vv.hostid = vh.hostid JOIN dbo.VPXV_COMPUTE_RESOURCE AS cl ON cl.RESOURCEPOOLID = vh.farmid WHERE vv.[name] = ‘DB01’ AND stat_group = ‘cpu’ AND stat_name = ‘costop’ AND vv.[name] IS NOT NULL AND ltrim(rtrim(vhsd.DEVICE_NAME)) != ” ORDER BY VMwareClusterName ASC, VMName ASC, SampledTime DESC, vCPUNumber ASC |
Enjoy!
