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!
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
4). Restart the service
5). Connect to the Postgres DB
6). Search for the Custom Properties with the select below
SELECT property_id, property_name FROM public.hd_property where property_name like 'VMAN%';
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';
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';
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';
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'
11). Let’s run the same select from step 6). to confirm that the property has been deleted and then we are done.
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
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!
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!