Deployment / Provisioning requests are stuck at 'In Progress' or 'Pending Approval' status

Products

• VMware Aria Suite

Issue/Introduction

Symptoms:

In the vRealize Automation Requests page, multiple tasks get stuck in In Progress or Pending Approval status for a long time.

 

 

Environment

VMware vRealize Automation 7.x

Cause

This issue occurs when a task hangs up or when the backing infrastructure for that task is changed or removed.

Resolution

To resolve this issue, clean up the postgres database for those tasks.

 

• Take a backup of the postgres database by running these commands:

         su -m -c "/opt/vmware/vpostgres/current/bin/pg_dump -Fc vcac > /tmp/vcac.sql" - postgres

         Note: -Fc switch already provides a compressed file.  No need to bzip.

• Connect to the database by running these commands:
  
  /opt/vmware/vpostgres/current/bin/psql vcac postgres
\connect vcac;
   
• To ensure that the update is accurate, run all these cleanup scripts in a transaction:
  BEGIN;
UPDATE comp_bprequest
SET status = 'FAILED'
WHERE external_request_id::uuid IN
(SELECT id
FROM cat_request
WHERE STATE in ('IN_PROGRESS', 'PROVIDER_COMPLETED','PRE_APPROVED','SUBMITTED','PROVIDER_SEND_ERROR','POST_APPROVED')
AND (DATE_PART('day', now() - lastupdated) * 24 + DATE_PART('hour', now() - lastupdated) > '8'));

UPDATE cat_request
SET state = 'FAILED'
WHERE id::uuid IN
(SELECT id
FROM cat_request
WHERE STATE in ('IN_PROGRESS', 'PROVIDER_COMPLETED','PRE_APPROVED','SUBMITTED','PROVIDER_SEND_ERROR','POST_APPROVED')
AND (DATE_PART('day', now() - lastupdated) * 24 + DATE_PART('hour', now() - lastupdated) > '8'));

UPDATE cat_resource
SET
status = 'DELETED'
WHERE request_id in
( select id from cat_request where state in ( 'FAILED' ) AND type = 'CI' AND (DATE_PART('day', now() - lastupdated) * 24 + DATE_PART('hour', now() - lastupdated) > '8'));
• Verify that updates are successful by running Select queries:
  
  SELECT id,
status
FROM comp_bprequest
WHERE external_request_id::uuid IN
(SELECT id
FROM cat_request
WHERE STATE in ('IN_PROGRESS', 'PROVIDER_COMPLETED','PRE_APPROVED','SUBMITTED','PROVIDER_SEND_ERROR','POST_APPROVED')
AND (DATE_PART('day', now() - lastupdated) * 24 + DATE_PART('hour', now() - lastupdated) > '8'));

SELECT count(*)
FROM cat_request
WHERE STATE in ('IN_PROGRESS', 'PROVIDER_COMPLETED','PRE_APPROVED','SUBMITTED','PROVIDER_SEND_ERROR','POST_APPROVED')
AND (DATE_PART('day', now() - lastupdated) * 24 + DATE_PART('hour', now() - lastupdated) > '8');

SELECT count(*) FROM cat_resource WHERE request_id IN (SELECT id FROM cat_request WHERE STATE in ('IN_PROGRESS', 'PROVIDER_COMPLETED','PRE_APPROVED','SUBMITTED','PROVIDER_SEND_ERROR','POST_APPROVED') AND type = 'CI');
 
• If count is 0 for all 3 of the select queries, then COMMIT else ROLLBACK;
  
  COMMIT / ROLLBACK;

Note : These scripts just update the DB on VCAC but it doesn't explicitly delete the resources from the endpoint. In order to delete the VMs provisioned as a part of these stuck requests, see the KB article, Removing a virtual machine from vRealize Automation 7.x using Cloud Client (2144269)

Additional Information

Request stuck at 'In Progress' if not approved within 7 days