Restrict Consult option to specific users whose id are present in the Account attributes

[nitishdas]

Hi Team,

We have a bit complex requirement for a user manager campaign where in the consult option we need to show only a specific consultee id to the user manager when he selects the consult option. 

The background of this is like there is a concept of supervisor for endpoint A in which the Accounts will be having a supervisor tagged to one of the custom attributes. So, when the campaign is launched, and the user's manager tries to review the Account A then when he selects the consult option he should only see the supervisor id which is tagged to that account A and no other values should be present. Can anyone please help in this>

[rushikeshvartak]

• You can’t get selected account in consult query 

[nitishdas]

Hi @rushikeshvartak ,

As per the use case in the accounts table there are certain list of accounts which has a specific entitlement values and are active. The accounts have the same name as that of the username. So, using the below query I am trying map multiple tables to get the users IDs and put in the consult query so that at least we can restrict to a smaller list but still it shows invalid syntax. This kind of queries also the consult user does not not support?

select u from users where u.username in (select username from users us inner join user_accounts ua on us.userkey=ua.userkey inner join accounts a on a.accountkey=ua.accountkey inner join account_entitlements1 ae on a.accountkey=ae.accountkey inner join entitlement_values ev on ae.entitlement_valuekey=ev.entitlement_valuekey where ev.customproperty2 in ('AOSEPU','AOSQM') and a.status=1 and a.endpointkey=168)

[rushikeshvartak]

Below query worked for me 

select u from Users u where u.username in (select us.username from Users us where exists (select 1 from User_accounts ua where ua.userkey = us.id and exists (select 1 from Accounts a where a.id = ua.accountkey and a.status = 1 and a.endpointkey = 168 and exists (select 1 from Account_entitlements1 ae where ae.accountkey = a.id and exists (select 1 from Entitlement_values ev where ev.id = ae.entitlement_valuekey and ev.customproperty2 in ('AOSEPU','AOSQM'))))))

[nitishdas]

I am trying this as well:

select u from Users where 1=1 and u.username in (select username from Users us inner join com.saviynt.ecm.identitywarehouse.domain.user_accounts ua on us.userkey=ua.userkey inner join com.saviynt.ecm.identitywarehouse.domain.accounts a on a.accountkey=ua.accountkey inner join com.saviynt.ecm.identitywarehouse.domain.account_entitlements1 ae on a.accountkey=ae.accountkey inner join com.saviynt.ecm.identitywarehouse.domain.entitlement_values ev on ae.entitlement_valuekey=ev.entitlement_valuekey where ev.customproperty2 in ('AOSEPU','AOSQM') and a.status=1 and a.endpointkey=168)

[indra_hema_95]

Hi @nitishdas You can't use these queries because in Consult/Reassign User Query only users table is exposed.

Regards,

Indra