public

Find all VMs and vApps on a specific storage policy

Recipe for finding all VMs and vApps on VMware vCloud Director Storage Policy

8 months ago

Latest Post VMworld 2021 - 10 sessions from my watchlist by Alexey Koznov public

If you need to find all Virtual Machines, vApps and additional information about Datastores that stores this objects you can use this SQL query:

SELECT
vapp_vm.name AS VM,
vm.moref as VMMoref,
vm_container.name AS vApp,
org_prov_vdc.name AS OrgVDC,
vdc_logical_resource.name AS StoragePolicy,
datastore_inv.vc_display_name AS Datastore,
datastore_profile_inv.profile_moref AS ds_sp_moref
FROM vapp_vm
INNER JOIN vm_container ON vapp_vm.vapp_id = vm_container.sg_id
INNER JOIN org_prov_vdc ON vm_container.org_vdc_id = org_prov_vdc.id
INNER JOIN vm ON vapp_vm.svm_id = vm.id
INNER JOIN vdc_logical_resource ON vapp_vm.storage_class_lr_id = vdc_logical_resource.id
INNER JOIN datastore_inv ON vm.dstore_moref = datastore_inv.moref
INNER JOIN storage_class ON vdc_logical_resource.fo_id = storage_class.id
LEFT JOIN datastore_profile_inv ON datastore_inv.moref = datastore_profile_inv.datastore_or_pod_moref
WHERE storage_class.name = 'DPLabCompSSD'
ORDER BY org_prov_vdc.name,vapp_vm.name;
SQL query for getting storage info

You should change "storage_class.name" to your Storage Policy name (in example DPLabCompSSD.

Results of Query in pgAdmin:

Results of SQL query
Alexey Koznov

Published 8 months ago

Comments?

Leave us your opinion.