When provisioning / destroy fails or is stuck due to whatever reason , there is every chance that we have to manually clean them.
In our scenario, these stale entries were present under vRA Items -> Configuration Management
Note : Before we get into steps to remove these entries from Database , we have to have a full backup of vRA vPostgres database
Also ensure , Virtual Machine tagged to this entry is no longer present on the endpoint and managed by vRA
Steps to remove these entries from Database
After database backup has been taken, take a snapshot of vRA appliance
Connect to vRA postgres database
These entries would be present under cat_resource and cat_resource_owners tables
Filter active Puppet entries using below query
select * FROM public.cat_resource WHERE resourcetype_id = 'ConfigManagement.Puppet' AND status = 'ACTIVE';
One above query is executed, you would be presented with all active Puppet entries, this would match with the UI entries
Before we delete entries from cat_resource , we need to remove references from cat_resource_owner
Using the value present in id column of above query execute following query on cat_resource_owners
select * FROM public.cat_resource_owners WHERE resource_id = 'XXXXX';
You would be presented with one result, then delete it
delete * FROM public.cat_resource_owners WHERE resource_id = 'XXXXX';
Now for the resource_id which was used in the previous query to remove from cat_resource_owners , select the binding ID and then remove it from cat_resource table
select * FROM public.cat_resource WHERE binding_id = 'YYYYY';
then delete this entry from database
delete * FROM public.cat_resource WHERE binding_id = 'YYYYY';
Now refresh vRA portal , removed Puppet entry would be no longer present.