Overview

You might have face the issue in vRealize Automation that the requests are stuck at ‘In Progress’ or ‘Pending Approval’ status. And we are unable to raise the same request for the service.Se we need to take proper steps to resolve vRealize Automation Inprogress requests carefully otherwise we may face critical issues in bra. So please follow the guide to resolve vRealize Automation Inprogress requests.

Resolution

We need to clear or delete the the vRealize Automation Inprogress or peding task tasks from vRealize Postgres Database.

Prerequisite

Step 1:

Take VM backup and it is strongly recommended to avoid any disaster.

Step 2:

Take VM snapshot just before the activity for more safety.

Step 3:

Get into the vRealize appliance through SSh.

Step 4:

Take Postgress database backup by running below command

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

You can change database name like vcac-23-03-2019.sql, It will help you to recognize the backup file inside the backup folder. After successful execution of the above command we need to check the backup file if it is backed up successfully or not by running below command.

cd /tmp/

If you can see the backup file then the db backup process is successful.Now we can head to next step,

Step 5:

Now Connect to Postgres database by running below commands

/opt/vmware/vpostgres/current/bin/psql vcac postgres
\connect vcac;

Step 6:

Now ensure the affected tables by running below three commands one by one,

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');

Step 7:

Now run all these below three cleanup scripts one by one to delete vRealize Automation Inprogress requests

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'));

then COMMIT the execution else ROLLBACK if require,

COMMIT; for commit

ROLLBACK; for rollback

Dont forget to enter ‘;’ after COMMIT command as shown above.

Step 8:

Now the execution is successful, we need to check the table again by executing Step 6 commands one by one, If it will show 0 in every execution then our removal of inprogress request is successful. You can see in the vRealize portal that the previous in-progress request has been cleared.

It is the step by step guide to resolve vRealize Automation Inprogress requests.

It is the extended version of Vmware KB Article.

Do you want to host wordpress for free ?