public

Find all VMs and vApps on a specific storage policy

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

3 months ago

Latest Post vCenter error: Unable to get signed certificate forhost name rpc_s_no_memory 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 3 months ago

Comments?

Leave us your opinion.