Apr 082016
 

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!