OCI DWH SQL Query: Count Number of VMs Per ONTAP Node in a ONTAP Cluster

It's relatively straightforward to stitch together tables in the OCI DWH to answer questions that are asked. For instance, if someone asks:

Q: Can you tell me the number of VMs per ONTAP node in an ONTAP cluster called NACLU01?
A: Yes, we can provide a table output of this information by running a simple SQL query using MySQL Workbench connected to the OCI DWH database (often it's easier to simply run an SQL query for adhoc requests, rather than using Cognos to develop a report.)

A screenshot of output from MySQL Workbench and the SQL query used is below. The SQL query uses that the storage_pool.name is of the format {NODENAME}:{AGGREGATE} for ONTAP clusters. Replace NACLU01 with whatever your cluster name is called.

Image: Output in MySQL Workbench

SELECT
count(*) AS 'VM Count',
SUBSTRING_INDEX(sp.name,':',1) AS 'ONTAP Node Name'
FROM hv_virtual_machine AS vm
JOIN hv_data_store_to_internal_volume AS ds ON vm.dataStoreId = ds.dataStoreId
JOIN internal_volume AS iv ON ds.internalVolumeId = iv.id
JOIN storage_pool AS sp ON iv.storagePoolId = sp.id
WHERE sp.type = 'AGGREGATE'
AND sp.name LIKE '%NACLU01%'
GROUP BY SUBSTRING_INDEX(sp.name,':',1)