We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Config for Requestable Entitlement in ARS query for comma separated values

nmuzinic
New Contributor III
New Contributor III

We have a scenario where in user customproperties (46 and 47) we store comma-separated country codes (eg: DZ,AO,AI) and region (EMEA,AP). We mapped the entitlements in the same way, added country codes and region values in the entitlement customproperties (10 and 11), what we want is when users are requesting in ARS to only see the entitlements that match any of these conditions. Is there a way  to configure Config for Requestable Entitlement in ARS query in such a way? We have no issues with filtering entitlements this way when there is only one value but we just can't get the query right with multiple/comma separated values.

9 REPLIES 9

rushikeshvartak
All-Star
All-Star

use FIND_IN_SET


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

Hi @rushikeshvartak,

Thanks, it works but not without issues 🙂 So my query looks like this:

ev.customproperty23 in (FIND_IN_SET((select customproperty46 from Users where userkey in (${requestee})),ev.customproperty23)) or ev.customproperty19 in (FIND_IN_SET((select customproperty8 from Users where userkey in (${requestee})),ev.customproperty19))

And this works fine when there are no special characters, but i have a value with / char e.g. Test 1/2, as far as I can see FIND_IN_SET supports special chars:

nmuzinic_0-1706800805984.png

I did find this error in log viewer:

2024-02-01T15:39:27+01:00-arsms-c.s.s.a.s.impl.EndpointsServiceImpl-http-nio-8787-exec-3-q59gv-ERROR-Error in getEntitlementValuesCount :: {}|java.lang.IllegalArgumentException: org.hibernate.QueryException: unexpected char: '{' [Select ev.entitlementtypekey.id, count(distinct ev.id) from com.saviynt.ssm.entity.EntitlementValues ev where ev.entitlementtypekey.endpointkey.id = :endpointKey and ((ev.entitlementtypekey.id =29 and (ev.customproperty23 in (FIND_IN_SET((select customproperty46 from com.saviynt.ssm.entity.Users where userkey in (${requestee})),ev.customproperty23)) or ev.customproperty19 in (FIND_IN_SET((select customproperty8 from com.saviynt.ssm.entity.Users where userkey in (${requestee})),ev.customproperty19))) )) and ev.status=1 group by ev.entitlementtypekey.id]| at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:729) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:104) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at jdk.internal.reflect.GeneratedMethodAccessor109.invoke(Unknown Source) ~[na:na]|Caused by: org.hibernate.QueryException: unexpected char: '{' [Select ev.entitlementtypekey.id, count(distinct ev.id) from com.saviynt.ssm.entity.EntitlementValues ev where ev.entitlementtypekey.endpointkey.id = :endpointKey and ((ev.entitlementtypekey.id =29 and (ev.customproperty23 in (FIND_IN_SET((select customproperty46 from com.saviynt.ssm.entity.Users where userkey in (${requestee})),ev.customproperty23)) or ev.customproperty19 in (FIND_IN_SET((select customproperty8 from com.saviynt.ssm.entity.Users where userkey in (${requestee})),ev.customproperty19))) )) and ev.status=1 group by ev.entitlementtypekey.id]| at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:235) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:143) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:119) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:80) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:153) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:611) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]|

Any idea how to get around this issue?

REPLACE(ev.customproperty23, '/', '') IN (
    SELECT REPLACE(customproperty46, '/', '')
    FROM Users
    WHERE userkey IN (${requestee})
  )
  OR
  REPLACE(ev.customproperty19, '/', '') IN (
    SELECT REPLACE(customproperty8, '/', '')
    FROM Users
    WHERE userkey IN (${requestee})
  )

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

Hi  @rushikeshvartak,

I've actually tried that but I thought that I got the syntax wrong, but it seems this is not acceptable syntax for Config for Requestable Entitlement in ARS query. And again I need to combine FIND_IN_SET in there to get desired result

nmuzinic_0-1707124655882.png

 

You can't use string functions to inner query 

rushikeshvartak_0-1707192457486.png

 


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

nmuzinic
New Contributor III
New Contributor III

Well not in Saviynt, anywhere else yes:

nmuzinic_0-1707206033004.png

Another very simple issue that can't be resolved in Saviynt other than changing the data which of course is not an option.

Saviynt uses HQL not SQL


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

I'm aware of it, it doesn't help in any case

Yes you need to raise enhancement 


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