Workflow - If/Else condition to check if Requestby and Entitlement is part of a usergroup

[Asha]

Hi,

            In the workflow If/Else condition, how do I check if the requestedBy user is part of a specific usergroup and if the requesting entitlement is part of that usergroup entitlement? 

[rushikeshvartak]

You can not check usergroup members in if else block however below are workaround

• Use Custom Assignment Block with CustomQuery as option.
• Use Dynamic attribute in Request Form which will validate if requestor is part of group then show only 1 value in Drop Down & use same in if-else block of workflow

Can we add entitlements under usergroup ?

[Asha]

Thanks @rushikeshvartak for the response.. But isnt the 'Custom Assignment' block delegates the approval, rather then doing an if-else condition? 

Yes, entitlements can be added in the usergroup. 

My usecase is as below:

1. There are usergroups with a list of users and entitlements

2. If a user belonging to the usergroup requests access to any of the entitlements in the usergroup, then it has one workflow approval path and if not, it takes a different workflow approval path

3. A user can belong to one or more usergroups and likewise, an entitlement as well can belong to multiple user groups. So its difficult to use one dynamic attribute... 

I would like to be able to use a query like below. If this return records, then one approval path and if not another approval path.. 

select count(ug.user_groupname)
from
user_groups ug,
usergroup_users ugu,
usergroup_entitlements uge
where
ugu.user_groupkey = ug.usergroupkey and
ug.usergroupkey = uge.user_groupkey and
ug.user_groupname like 'groupnameprefix_%' and
ugu.userkey = RequestedFor.userkey and
uge.entitlement_valuekey = entitlement.id

Any suggestion on how to achieve this in the ARS approval workflow? 

[rushikeshvartak]

You can use Custom assignment Block with Custom Query

Sample query- you need to make complex query with ${ARSREQUEST.Requestor}

select ug.USER_GROUPNAME,u.username from usergroup_users ua,users u ,user_groups ug where ug.USERGROUPKEY=ua.USER_GROUPKEY and u.userkey=ua.userkey

[Asha]

Thanks @rushikeshvartak . What attribute should I use to get the requestee details in the workflow?

Only '${requestedby.username}' works. but then this gives the 'Requestor' details. But I need the 'Requestee' details 

I'm trying to reference the requestee in the custom query using multiple ways.. but not able to get the requestee value from the ARS Request. What attribute should I use? 

select userkey from users where username = '${RequestedFor.username}' 

select userkey from users where userkey = '${RequestedFor.userkey}' 

select userkey from users where username = '${ARSRequest.REQUESTOR}' 

Cant find much in Saivynt documentation as well. 

[rushikeshvartak · Accepted solution]

${ARSREQUEST.requestor}

[Asha]

${ARSREQUEST.requestor} seems to give only the 'Requestor' details and not the Requestee details.. Eg. if a user (Requestor) is requesting for another user (Requestee), then the above attribute is giving only the details of the person who is requesting and not the details for whom he is requesting.. 

Is there any other attribute in ARSREQUEST object that I can use? 

[rushikeshvartak · Accepted solution]

join table with request_Access based on ${ARSREQUEST.id} & get requestee from userkey column of request_access table 

select ra.userkey from request_Access ra,ars_requests ar where ra.requestkey=ar.requestkey and ar.requestkey= ${ARSREQUEST.id}

[Asha]

Thanks @rushikeshvartak. The above solution worked. I'm using this custom query to check if the requestee is part of a usergroup having entitlement that he is requesting. If so, it should be auto approved (which I'm able to achieve), If the query doesn't find any records, then it should transfer the approval to Mgr and Ent Owner. But when there is no result, the custom query returns null and 'Custom Assignment' object sends the approval to Saviynt 'Admin' for approval. I can alter the query itself to return the Mgr and Ent Owner records so the approval is transferred to them. The 'Custom Assignment' object has 'Approve', 'Reject' and 'Escalate' outputs only. Is there a simple way to capture the null record from the 'Custom Assignment' object and then pass on to 'Mgr Approval' object?

[rushikeshvartak]

Please share query. else you can use case when then logic in case if its null assign to manager/ent owner

[Asha]

hi @rushikeshvartak, thanks for the reply. When I was checking I noticed that the ${user.id} returns the Requestee details. So I'm directly using this ${user.id} attribute itself. 

I have the below query. It works fine in Data Analyzer, but is not returning data in the workflow. The if condition returns the 'Requestor' itself if  the requestee/entitlement combination exists in usergroup so that it is auto-approved and in the else condition, when no such combination exists, it returns the manager and ent owner for approval.  

Am I missing anything here?

SELECT u.userkey
FROM
users u
WHERE
CASE WHEN
EXISTS (SELECT ugu.userkey
FROM
user_groups ug,
usergroup_users ugu,
usergroup_entitlements uge
WHERE
ug.usergroupkey = ugu.user_groupkey and
uge.user_groupkey = ug.usergroupkey and
ug.user_groupname like '<usergroupprefix>_%' and
uge.entitlement_valuekey = ${REQUESTACCESSOBJ.id} and
ugu.userkey = ${user.id})
THEN u.userkey = ${ARSREQUEST.REQUESTOR}
ELSE
u.userkey IN (
select manager from users where userkey = ${user.id}
UNION
select userkey from entitlement_owners where entitlement_valuekey=${REQUESTACCESSOBJ.id} and rank = 1
)
END

[rushikeshvartak]

In workflow Customquery always use alias as userkey

SELECT u.userkey
FROM
users u
WHERE
CASE WHEN
EXISTS (SELECT ugu.userkey
FROM
user_groups ug,
usergroup_users ugu,
usergroup_entitlements uge
WHERE
ug.usergroupkey = ugu.user_groupkey and
uge.user_groupkey = ug.usergroupkey and
ug.user_groupname like '<usergroupprefix>_%' and
uge.entitlement_valuekey = ${REQUESTACCESSOBJ.id} and
ugu.userkey = ${user.id})
THEN u.userkey = ${ARSREQUEST.REQUESTOR}
ELSE
u.userkey IN (
select manager as userkey from users where userkey = ${user.id}
UNION
select userkey from entitlement_owners where entitlement_valuekey=${REQUESTACCESSOBJ.id} and rank = 1
)
END

[Asha]

Thanks @rushikeshvartak . Tried the above and also tried different ways.. All of these works fine in Data Analyzer.. but is not fetching records in workflow custom query. Any idea why its not working? Is there any special characters that I shouldnt use or query length restrictions?? But the app log doesnt give any error, but only returns null. 

1. In the above modified query using
   1. select manager as userkey from users where userkey = ${user.id}
   2. select userkey from users where userkey = ${user.manager}
2. Also tried the below query without 'CASE WHEN"
   1. SELECT u.userkey
      FROM
      users u
      WHERE
      (EXISTS (SELECT ugu.userkey
      FROM
      user_groups ug,
      usergroup_users ugu,
      usergroup_entitlements uge
      WHERE
      ug.usergroupkey = ugu.user_groupkey and
      uge.user_groupkey = ug.usergroupkey and
      ug.user_groupname like 'PREAPPR_%' and
      uge.entitlement_valuekey = ${REQUESTACCESSOBJ.id} and
      ugu.userkey = ${user.id})
      AND u.userkey = ${ARSREQUEST.REQUESTOR})
      OR
      ( NOT EXISTS (SELECT ugu.userkey
      FROM
      user_groups ug,
      usergroup_users ugu,
      usergroup_entitlements uge
      WHERE
      ug.usergroupkey = ugu.user_groupkey and
      uge.user_groupkey = ug.usergroupkey and
      ug.user_groupname like 'PREAPPR_%' and
      uge.entitlement_valuekey = ${REQUESTACCESSOBJ.id} and
      ugu.userkey = ${user.id})
      AND u.userkey IN (
      select userkey from users where userkey = ${user.manager}
      UNION
      select userkey from entitlement_owners where entitlement_valuekey=${REQUESTACCESSOBJ.id} and rank = 1
      ))

[rushikeshvartak]

SELECT case when(select ugu.userkey from user_groups ug,usergroup_users ugu,usergroup_entitlements uge WHERE ug.usergroupkey = ugu.user_groupkey and uge.user_groupkey = ug.usergroupkey and ug.user_groupname like 'PREAPPR_%' and uge.entitlement_valuekey = 1 and ugu.userkey = ${user.id} )is null then ${ARSREQUEST.REQUESTOR} else (select ${user.manager} as userkey from dual union select userkey from entitlement_owners where entitlement_valuekey=${REQUESTACCESSOBJ.id} and rank = 1) end as userkey from dual

[Asha]

Thanks @rushikeshvartak ..  Tried this as well... but no luck ☹️ No records are returning.. In the meantime, I've raised Saviynt ticket. 

[Asha]

Hi @rushikeshvartak ,

             While my ticket is still pending with Saviynt, I want to know how I can get the auto-approval work when the 'requestee-entitlement that he is requesting' is part of a specific user group? Basically the user-entitlement combination in the usergroup should get auto-approved. 

In the above query, I'm checking if the user-entitlement combination exists in a usergroup and if yes, I'm returning the 'requestor' itself so it can get auto-approved. But requestor-requestee can be the same person which might trigger an SOD condition. 

In such situation, how can I get the auto-approval working? Any thoughts?

[Mareetta]

Hi Asha, 

Did you find a way to handle auto approval? 

[rushikeshvartak]

  . 

[Asha]

@rushikeshvartak ... I modified the  ${ARSREQUEST.REQUESTOR} to ${requestedby.id} and it worked.. The entitlements in the usergroup are getting autoapproved. 

[rushikeshvartak]

Did you validated request for self  

Since requestor and requested for will be same what will happen with and without sod

[Asha]

Yes... I did for self and also tried 'Request for Others'... both worked.. 

[Asha]

Yes.. I was able to get this working. I used the below Custom Query and it worked.. 

SELECT u.userkey
FROM
users u
WHERE
CASE WHEN
EXISTS (SELECT ugu.userkey
FROM
user_groups ug,
usergroup_users ugu,
usergroup_entitlements uge
WHERE
ug.usergroupkey = ugu.user_groupkey and
uge.user_groupkey = ug.usergroupkey and
ug.user_groupname like '<usergroupprefix>_%' and
uge.entitlement_valuekey = ${REQUESTACCESSOBJ.id} and
ugu.userkey = ${user.id})
THEN u.userkey = ${requestedby.id}
ELSE
u.userkey IN (
select manager from users where userkey = ${user.id}
UNION
select userkey from entitlement_owners where entitlement_valuekey=${REQUESTACCESSOBJ.id} and rank = 1
)
END

[anuragG]

Hi @Asha Could you please share a screenshot of the workflow editor?