[OCI DWH MySQL] Query to find VMs on NAS (Internal Volume) Datastores of a Storage Array

on

I often get asked to troubleshoot things in NetApp OCI (usually it's problems with downstream systems not being programmed to fully appreciate the wealth of data that is in OCI). Normally, with maximum 10 minutes playing around with MySQL, I can get answers to anything! But I do often think that is 10 minutes wasted because I probably get asked the same questions again and again. Really, I should create a page on this blog of useful OCI DWH MySQL queries ... one day perhaps!

Anyway, here is the titular query:

select vm.name from dwh_inventory.storage AS s
join dwh_inventory.hv_data_store_to_internal_volume AS ds ON ds.storageId = s.id
join dwh_inventory.hv_virtual_machine AS vm ON ds.dataStoreId = vm.dataStoreId
where s.name = 'STORAGENAME';

A useful little query to find all the VMs that we can see in the OCI DWH that are contained in internal volumes (VM{ware} NFS datastores) on a named storage array/cluster.

We can expand it slightly to include VMs on specific NetApp Storage Virtual Machines (virtualStorage):

select i.virtualStorage,vm.name from dwh_inventory.storage AS s
join dwh_inventory.hv_data_store_to_internal_volume AS ds ON ds.storageId = s.id
join dwh_inventory.hv_virtual_machine AS vm ON ds.dataStoreId = vm.dataStoreId
join dwh_inventory.internal_volume AS i ON i.id = ds.internalVolumeId
where i.virtualStorage IN ('SVM2','SVM1');

For SAN:

Very similar for SAN, all we're doing is changing from internal_volume to volume:

select vm.name from dwh_inventory.storage AS s
join dwh_inventory.hv_data_store_to_volume AS ds ON ds.storageId = s.id
join dwh_inventory.hv_virtual_machine AS vm ON ds.dataStoreId = vm.dataStoreId
where s.name = 'STORAGENAME';

And if we're looking for hosts on an iSCSI SAN, and want to check the vCenter information (or not if the vCenter has not been added to OCI):

SELECT DISTINCT s.name,h.name,h.os,hv.virtualCenterIp,hv.clusterName
FROM dwh_inventory.host AS h
JOIN dwh_inventory.iscsi_logical AS p ON p.hostId = h.id
JOIN dwh_inventory.storage AS s ON s.id = p.storageId
LEFT JOIN dwh_inventory.hv_server AS hv ON hv.hostId = h.id
WHERE s.name = 'STORAGENAME'
ORDER BY 1,2,3 ASC


Comments

Post a Comment