Prerequisites
Configure pgAdmin to connect to a vRealize Automation instance
- Extracting the User ids from the Custom Groups("groupType"='DYNAMIC') from Group table.
select array_agg(e::text::int) from saas."Group" ,json_array_elements("compositionRules"::json -> 'addedUserIds') as e where "groupType"='DYNAMIC';Example:
- Take the UserIds from step 1 and query for impacted "soft deleted" users:
select "idUser" from saas."Users" where "idUserStatus"=3 and "idUser" IN(CommaDelimitedIDsFrom Step#1);
- Use problematic UserID to determine UserID and their associated group memberships:
select "id" from saas."Group" where "groupType"='DYNAMIC' AND "compositionRules" ~* 'idUser1|idUser2';
As shown in the above screenshot idUser 1959 was soft deleted from the environment.
select "id" from saas."Group" where "groupType"='DYNAMIC' AND "compositionRules" ~* '1959';

- Query the group using the id from step 3:
select * from saas."Group" where id = '1127';
- Double click on compositionRules:
- id 1959 should be a member of this group

- Edit this section to remove this id from this column and save the change with F6.
- 1959 should no longer exist:
