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

[KG]

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

[Raghu]

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)

[Saathvik]

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

[KG]

No, 

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

[PremMahadikar · Accepted solution]

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

[Saathvik]

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

[rushikeshvartak]

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