Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

the Endpoint access query needed to not show an app in ARS if the user belongs to XYZ Entitlement

KG
New Contributor II
New Contributor II

Hi Team,

the Endpoint access query needed to not show an app in ARS if the user belongs to XYZ Entitlement

below query is working but it applies for all users

where $ {requestor.id} in (SELECT distinct UA.userkey FROM ACCOUNTS A, USER_ACCOUNTS UA, ENDPOINTS E, account_entitlements1 ae1 WHERE A.ACCOUNTKEY=UA.ACCOUNTKEY AND A.ENDPOINTKEY=E.ENDPOINTKEY and ae1.accountkey =a.accountkey and ae1.entitlement_valuekey = (select entitlement_valuekey from entitlement_values where entitlement_values='xxx'))

6 REPLIES 6

Raghu
Valued Contributor III
Valued Contributor III

yes it apply all users ? any specific scenario have ?

only active users want : add below one

where $ {requestor.id} in (SELECT distinct UA.userkey FROM ACCOUNTS A, USER_ACCOUNTS UA, ENDPOINTS E,Users U,account_entitlements1 ae1 WHERE U.userkey=ua.userkey and A.ACCOUNTKEY=UA.ACCOUNTKEY AND A.ENDPOINTKEY=E.ENDPOINTKEY and ae1.accountkey =a.accountkey and ae1.entitlement_valuekey = (select entitlement_valuekey from entitlement_values where entitlement_values='xxx') and u.status=1)


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

Saathvik
All-Star
All-Star

@KG : Yes condition applies for all users who have the entitlement. Do you have any specific condition to limit the users other than entitlement?


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

KG
New Contributor II
New Contributor II

No, 

But the above query, user don't have xxx entitlement even they are not able to see the endpoint

PremMahadikar
Valued Contributor
Valued Contributor

Hi @KG ,

There is a syntax error in your query. Try the below,

where users.userkey not in (SELECT distinct UA.userkey FROM ACCOUNTS A, USER_ACCOUNTS UA, ENDPOINTS E, account_entitlements1 ae1 WHERE A.ACCOUNTKEY=UA.ACCOUNTKEY AND A.ENDPOINTKEY=E.ENDPOINTKEY and ae1.accountkey =a.accountkey and ae1.entitlement_valuekey = (select ev.entitlement_valuekey from entitlement_values ev where ev.entitlement_value='XYZ' and ev.ENTITLEMENTTYPEKEY=2))

Note: Hardcode the desired entitlement value XYZ and entitlement type (example 2 just to avoid duplicates)

 

If you find the above response useful, Kindly Mark it as Accept As Solution and hit Kudos

@KG: Highlighted column is not valid. Please change it to entitlement_value and then validate

where $ {requestor.id} in (SELECT distinct UA.userkey FROM ACCOUNTS A, USER_ACCOUNTS UA, ENDPOINTS E, account_entitlements1 ae1 WHERE A.ACCOUNTKEY=UA.ACCOUNTKEY AND A.ENDPOINTKEY=E.ENDPOINTKEY and ae1.accountkey =a.accountkey and ae1.entitlement_valuekey = (select entitlement_valuekey from entitlement_values where entitlement_values='xxx'))


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

rushikeshvartak
All-Star
All-Star

where $ {requestor.id} in (SELECT distinct UA.userkey FROM ACCOUNTS A, USER_ACCOUNTS UA, ENDPOINTS E, account_entitlements1 ae1 WHERE A.ACCOUNTKEY=UA.ACCOUNTKEY AND A.ENDPOINTKEY=E.ENDPOINTKEY and ae1.accountkey =a.accountkey and ae1.entitlement_valuekey IN (select entitlement_valuekey from entitlement_values where entitlement_value='xxx'))


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.