[NetApp OCI DWH] Simple vCPU Oversubscription Query

I've learnt (mostly from customers) that for best user experience (and application experience) in a virtual infrastructure environment, you never really want to go much above 1 vCPU to 1 pCPU, otherwise you will get bad user experience (this is especially so in Virtual Desktop Infrastructures).

If you have the NetApp OnCommand Insight Data Warehouse, it is very easy to see how vCPU oversubscribed your hypervisors are (globally) with this simple MySQL query:

SELECT

    SUM(v.processors) AS 'vCPUs'

  , h.cpuCount AS 'pCPUs'

  , SUM(v.processors)/h.cpuCount AS 'vCPU Subscription'

  , h.name AS 'Hypervisor'

  , h2.clusterName AS 'HV Cluster'

  , h2.virtualCenterIp AS 'HV VC IP'

FROM dwh_inventory.hv_virtual_machine AS v

JOIN dwh_inventory.host AS h ON h.id = v.hostId

JOIN dwh_inventory.hv_server AS h2 ON h2.hostId = h.id

WHERE v.powerState = 'poweredOn'

GROUP BY v.hostId

ORDER BY 3 DESC;