[OCI-DWH][MySQL] Count of VM's By Country

A nice little query where we get the count of virtual machines per country from dwh_capacity. This assumes you have an annotation 'country' applied to your storage assets (which I think is fairly typical for large NetApp OnCommand Insight deployments.)

SELECT country,count(*) AS 'VM Count'
FROM
(
SELECT s.country,
FROM dwh_capacity.vm_capacity_fact AS f
JOIN dwh_capacity.vm_dimension AS vm ON vm.tk = f.vmTk
JOIN dwh_capacity.storage_dimension AS s ON s.tk = f.storageTk
WHERE f.dateTk = (SELECT max(dateTk) FROM dwh_capacity.vm_capacity_fact)
GROUP BY f.vmtk
) AS t
GROUP BY country
ORDER BY 2 DESC