VMware vRA requests stuck at InProgress or Pending Approval

Overview:

Sometimes we notice in VMware vRealize Automation requests page, multiple tasks get stuck in In Progress or Pending Approval status for a long time. This issue occurs because of many reasons like – it does not have a valid approver in vRealize Automation. The approver for the request is no longer a member of the approver's group or no longer with the company. The approver was added to the approval group after the request was created. When a task hangs up or when the backing infrastructure for that task is changed or removed.

Resolution:

To resolve this issue, we need to clean up the vRA postgres database for those specific In Progress or Pending Approval tasks.

Prerequisites:

It is recommended to keep a snapshot just before the activity and schedule the activity in an approved downtime.
It is recommended to use normal virtual machine backup methods to back up the appliance. For more information see VMware Documentation
Take a backup of the postgres database. For more information see KB 2074214
Take a backup of the postgres database by running these commands and use SCP or WinSCP to transfer the vcacvadbdump.bz2 file out of the appliance.
- cloudpandavra1:'#service vcac-server stop
- cloudpandavra1:'#su -m -c "/opt/vmware/vpostgres/current/bin/pg_dumpall -c -f /tmp/vcac.sql" postgres
- cloudpandavra1:'#bzip2 -z /tmp/vcac.sql
- cloudpandavra1:'#service vcac-server start

Procedure:

Connect to database and run the below command to check the details of work item to ensure this is the correct approval in question
- cloudpandavra1:' #/opt/vmware/vpostgres/current/bin/psql vcac postgres
- vcac=#\connect vcac;
- vcac=#select * from work_item where workitemnumber= APPROVAL NUMBER;
You should see it includes all the relevant information to process this request once approved.
After verifying the right work item details, run the below command to change the status to "cancelled":
- vcac=#update work_item set state=’CANCELLED’ where workitemnumber= APPROVAL NUMBER;
After a UI refresh, you see that Pending Approval state is changed.
Again connect to database and execute the given commands. Finally COMMIT else ROLLBACK;
- cloudpandavra1:' #/opt/vmware/vpostgres/current/bin/psql vcac postgres
- vcac=#\connect vcac;
- vcac=#UPDATE comp_bprequest SET status = 'FAILED',last_updated = current_timestamp WHERE external_request_id::uuid IN (SELECT id FROM cat_request where 1=1 and requestNumber in (REQUEST NUMBER 1,REQUEST NUMBER 2, REQUEST NUMBER 3));
  - Output: UPDATE 0
- vcac=#UPDATE cat_request SET state = 'PRE_REJECTED',lastupdated = current_timestamp where 1=1 and requestNumber in (REQUEST NUMBER 1,REQUEST NUMBER 2, REQUEST NUMBER 3);
  - Output: UPDATE 0
- vcac=#UPDATE cat_resource SET status = 'DELETED',lastupdated = current_timestamp WHERE request_id in (SELECT id FROM cat_request where 1=1 and type = 'CI' and requestNumber in (REQUEST NUMBER 1,REQUEST NUMBER 2, REQUEST NUMBER 3));
  - Output: UPDATE 0
- vcac=#commit
- vcac-#\q
- cloudpandavra1:'#clear

Additional Information:

VMware KB 2114385
VMware KB 2148534

Overview:

Resolution:

Prerequisites:

Procedure:

Additional Information:

Write Review