As co-author Bryan described in great detail a situation can occur where the dstore_moref value of a VM in the cloud director database is empty. Preventing you from making any changes to that VM. Read all about it in his blog post: How to fix VMware Cloud Director ‘Index 0 out of bounds for length 0 error’ Recently I encountered that same issue but upon inspection I learned that the cloud director database had not one but quite a lot of VMs that had this symptom. This lead me to create a more extensive SQL query to solve this issue for all VMs.

Now first of all you need to keep in mind that within cloud director you can link multiple vCenters. And you should be aware that morefs are only unique with a vCenter as our community champion William Lam explained a long time ago. So especially when attempting to update a lot of objects using morefs you need to make sure that you use a combination of moref as well was a vCenter ID.

So what I came up with is this query that extracts the datastore moref from the datastore_inv table with a regular expression based on the location_path in the vm table. If you want an overview of what will be changed you can use this report query as a dry-run.

WITH vm_extracted AS (
SELECT
vm.moref,
vm.dstore_moref,
vm.vc_id AS vm_vc_id, -- Include vc_id from vm table
(regexp_matches(vm.location_path, '\[([a-z0-9\-]+)\]', 'g'))[1] AS extracted_value
FROM vm
WHERE vm.location_path ~ '\[([a-z0-9\-]+)\]'
)
SELECT
vm_extracted.moref AS vm_moref,
vm_extracted.dstore_moref AS current_dstore_moref,
datastore_inv.moref AS new_dstore_moref,
vm_extracted.vm_vc_id, -- Move vm_vc_id up
datastore_inv.vc_id AS datastore_vc_id -- Move datastore_vc_id down
FROM vm_extracted
JOIN datastore_inv
ON vm_extracted.vm_vc_id = datastore_inv.vc_id -- Match vc_id first
AND vm_extracted.extracted_value = datastore_inv.vc_display_name -- Then match vc_display_name
WHERE vm_extracted.dstore_moref IS NULL;

The output of this query should look something like this

vm_moref | current_dstore_moref | new_dstore_moref | vm_vc_id | datastore_vc_id
--------------------------------------------------------------------------------
vm-001 | | datastore-123 | vc1 uuid | vc1 uuid
vm-002 | | datastore-456 | vc2 uuid | vc2 uuid

Now if you are satisfied with the output of this command you can consider running the update for real now. But not before you created backups of your environment. If the update causes any unintended results you always want to be able to roll back to an earlier point in time. Also consider doing this update with the help of VMware (by Broadcom) technical support.

Having performed a successful backup of your vCloud servers you can then proceed to execute the actual update query.

WITH vm_extracted AS (
SELECT
vm.moref,
(regexp_matches(vm.location_path, '\[([a-z0-9\-]+)\]', 'g'))[1] AS extracted_value,
datastore_inv.moref AS datastore_moref
FROM vm
JOIN datastore_inv
ON (regexp_matches(vm.location_path, '\[([a-z0-9\-]+)\]', 'g'))[1] = datastore_inv.vc_display_name
WHERE vm.location_path ~ '\[([a-z0-9\-]+)\]'
)
UPDATE vm
SET dstore_moref = vm_extracted.datastore_moref
FROM vm_extracted
WHERE vm.moref = vm_extracted.moref
AND vm.dstore_moref IS NULL;

This query will update the dstore_moref field in the vm table with the corresponding datastore_moref from the datastore_inv table, but only where dstore_moref is currently NULL.

If the query is succesful this means you can run the report query again and it should no longer return any results. Which means you not only fixed the issue for a single VM, but all VMs in your database.

Happy updating!


Rudolf Kleijwegt

I am an experienced IT professional with over 20 years of hands-on experience designing, deploying, and maintaining IT infrastructure in both enterprise and service provider environments. My skills span across Linux and Windows and a multitude of server applications, allowing me to excel in a wide range of IT roles. Currently, my primary focus is on Software Defined DataCenter and DevOps. I am passionate about staying up to date with the latest trends in the industry to achieve superior outcomes.

0 Comments

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *