How to extract user List from endpoint custom property which has entitlement display name

[Shravan]

Hi Team,

we are trying to achive visibility control by using Endpoint Access Query.

And we have multiple entitlements for single endpoint, So we tried to update all entitlement values in endpoint custom property42. And calling the CP42 into endpint Access Query using IN function.

Using IN Finction when CP42 has multiple entitlement values it is not returning any users. anf CP 42 has single entitlement it is returning users.

below is the access query with multiple entitlements

where users.USERNAME in (select u.username from users u ,user_accounts ua, account_entitlements1 ae1, entitlement_values ev, entitlement_types et,accounts ac WHERE u.userkey = ua.userkey and u.statuskey = 1 and ua.accountkey = ac.accountkey and ae1.accountkey = ac.accountkey and ae1.ENTITLEMENT_VALUEKEY = ev.ENTITLEMENT_VALUEKEY and ev.entitlementtypekey = et.entitlementtypekey and et.endpointkey = '1150' and ev.displayname IN (select customproperty42 entitlement_value from endpoints where endpointname = <AWS Instance ID>))

Please suggest us posibilities to achive this usecase.

Regards,

Shravan

[NageshK]

@Shravan Thanks for posting your issue. In your query, the last select statement seems to be incorrect. Are you trying to extract customproperty42 as entitlement value? If yes, you are missing "as" keyword there. 

Also, try using FIND_IN_SET instead of IN. Also, if your entitlement_value and displayname values are same then use entitlement_value column as it is indexed. I have also modified your query to prevent the hardcoding of endpointkey. Here is the updated query. Try it out and see if this works:

select * from users where users.USERNAME in (select u.username from users u ,user_accounts ua, account_entitlements1 ae1, entitlement_values ev, entitlement_types et,accounts ac, endpoints ep WHERE u.userkey = ua.userkey and u.statuskey = 1 and ua.accountkey = ac.accountkey and ae1.accountkey = ac.accountkey and ae1.ENTITLEMENT_VALUEKEY = ev.ENTITLEMENT_VALUEKEY and ev.entitlementtypekey = et.entitlementtypekey and et.endpointkey = ep.endpointkey and FIND_IN_SET(ev.entitlement_value,ep.customproperty42) and ep.endpointname = '<AWS Endpoint name>');

Thanks

Nagesh K