Click HERE to see how Saviynt Intelligence is transforming the industry. |
01/29/2024 07:07 AM
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.
01/29/2024 12:36 PM
use FIND_IN_SET
02/01/2024 07:23 AM
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:
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?
02/01/2024 08:59 PM
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})
)
02/05/2024 01:18 AM
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
02/05/2024 08:07 PM
You can't use string functions to inner query
02/05/2024 11:55 PM
Well not in Saviynt, anywhere else yes:
Another very simple issue that can't be resolved in Saviynt other than changing the data which of course is not an option.
02/06/2024 08:47 PM
Saviynt uses HQL not SQL
02/06/2024 11:27 PM
I'm aware of it, it doesn't help in any case
02/06/2024 11:28 PM
Yes you need to raise enhancement