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:

vm.moref as VMMoref, AS vApp, AS OrgVDC, 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 =
INNER JOIN vm ON vapp_vm.svm_id =
INNER JOIN vdc_logical_resource ON vapp_vm.storage_class_lr_id =
INNER JOIN datastore_inv ON vm.dstore_moref = datastore_inv.moref
INNER JOIN storage_class ON vdc_logical_resource.fo_id =
LEFT JOIN datastore_profile_inv ON datastore_inv.moref = datastore_profile_inv.datastore_or_pod_moref
SQL query for getting storage info

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

Results of Query in pgAdmin:

Results of SQL query
Alexey Koznov

Published 3 months ago


Leave us your opinion.