So this next post came about after someone asked me how to remove / roll back the custom properties / attributes which I had posted about.

After reaching out to VMware and confirming that no option exists in the GUI or via the suite-api to delete custom properties… I set about trying to find a solution… and this is what i came up with!


DISCLAIMER

This method is most likely unsupported and could void your support from VMWARE should you cause any damage to the vRops Postgres DB…

Perform it at your own risk!


Before you do anything I recommend that you take a snapshot of vRops VM that you are about to perform this on.

1). Make sure to delete / modify any notifications, views, Metric / Property Symptom Definitions, etc… that depend on the custom properties that you are about to delete…

2). Download pgAdmin or use psql on your vRops server to execute the SQL statements that I provide below.

3). Enable access to the Postgres DB

a). Follow the KB from VMWare on how to allow access to vFabric Postgres…below I will demonstrate on my vRops Windows deployment.

b). Edit the file %VCOPS_DATA%\db\vcops\vpostgres\data\pg_hba.conf and change the line from md5 to trust ….just be aware that by using trust means that the user / password will not be required to login to the database…. so this should be a temporarily action only...

host    all             all             127.0.0.1/32         trust

pg_hba-conf

4). Restart the service

vpostgres-restart

 

5). Connect to the Postgres DB

pgadmin-addserver

pgadmin-configserver

pgadmin-dbexplore

6). Search for the Custom Properties with the select below

SELECT property_id, property_name
	FROM public.hd_property where property_name like 'VMAN%';

pgadmin-select-hd_property

7). From the list above select the property_id for the custom property you wish to delete and lets list all the resources with that custom property before we issue the delete..

SELECT resource_id, internal_id, resource_name, resource_kind, adapter_kind, creation_time, period_start_time, period_end_time, creation_time_dt, period_start_time_dt, period_end_time_dt, property_instance_name, property_name, property_value_string, property_category, property_type, property_instance_id, property_id, property_category_id, property_type_id
	FROM public.hd_resource_property_history_v where property_instance_id = '84e3a4a5-19ae-61ea-17f7-8baa6d45854f';

pgadmin-select-hd_resource_property_history_v

8). Now that we have confirmed that the custom property is attached to multiple resources we can issue the delete statement to remove them.

DELETE FROM public.hd_resource_property_history
	WHERE property_instance_id = '84e3a4a5-19ae-61ea-17f7-8baa6d45854f';

pgadmin-delete-hd_resource_property_history

9). Now that we have deleted all records for the custom property from the resources, we will now delete the property instance from the hd_property_instance table.

DELETE FROM public.hd_property_instance
	WHERE property_instance_id = '84e3a4a5-19ae-61ea-17f7-8baa6d45854f';

pgadmin-delete-hd_property_instance

10). Now the final step is to delete the custom property completely from the hd_property table. (constraints would prevent the deletion if any records are left over)

DELETE FROM public.hd_property
	WHERE property_id = '84e3a4a5-19ae-61ea-17f7-8baa6d45854f'

pgadmin-delete-hd_property

11). Let’s run the same select from step 6). to confirm that the property has been deleted and then we are done.

pgadmin-select-hd_property_2

12). To confirm that the custom properties are gone, I will now edit the view “Virtual Machine Properties List” and try to find the “VMAN|*” properties form the list

vrops-editview

We can now see from the screen below that the VMAN|COSTCODE no longer appears in the list… so looks like we are all good!

vrops-editviewlistproperties

13). Confirm everything is as expected, change the database access from  trusted to md5 and restart the service, then remove the snapshot..

Hope this helped you!

vMan