Advance Query in User Update Rule not working

[Tezinder]

Hi Team,

We are trying to use Advance query of user update rule and it is not working.

This is query that we want to use in advance query but is not working, may be because HQL format:

select distinct userkey as userkey from users u join customer c on c.customerkey = u.customer 
join customer_attributevalues ca on c.customerkey = ca.customerkey where ca.attributename = 'Classification' and ca.attributevalue in (select SUBSTRING_INDEX(SUBSTRING_INDEX(e.customproperty8, ',', n.n), ',', -1) AS endpoint_classification from endpoints e JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) n WHERE LENGTH(e.customproperty8) - LENGTH(REPLACE(e.customproperty8, ',', '')) >= n.n - 1)

In above query we are trying to multiple tables like Users, Endpoints and Customer, so we instead started testing with simple query on Users table and even that is not working, here is the query:

(##a.customproperty14 isupdated##) AND a.id in (select u.userkey from Users u where u.username = 'EXT1000001')

Can anyone help please?

[rushikeshvartak]

what is error you are getting ?

[Tezinder]

Thanks for responding @rushikeshvartak 

We were able to figure out the query for Users, Endpoints and Customer tables. Now we are stuck at Customer_AttributeValues table and it looks like because we are not using the correct class.

(##a.customproperty14 isupdated##) AND a.id in (select '3561' from Customer_AttributeValues catt where catt.AttributeValue= 'Classification')

We tried to search for class names in case HQL but didn't find any clear documentation. We found one forum ticket: https://forums.saviynt.com/t5/saviynt-knowledge-base/advanced-usage-of-eic-identity-objects-in-provi...

In here it is mentioned "Validate the class name in the war file", and we are not sure where to find this war file. Could you please us give the directions on where to find the war to validate class names or any documentation that we can get check the correct class names?

[Tezinder]

Hi Team,

Is there any update on this? We are looking for some clear documentation on what classes, tables can be used in HQL query. Or where can we validate the class name from the war file?

[rushikeshvartak]

share error from log. It seems that table is not exposed in UUR

[Tezinder]

@rushikeshvartak Error:

2023-11-14T15:07:45+05:30-ecm-worker----org.springframework.orm.hibernate3.HibernateQueryException: could not resolve property: AttributeValue of: com.saviynt.survey.Customer_AttributeValues [select a.id from com.saviynt.ecm.identitywarehouse.domain.Users a where a.id=3561 AND (( 1 = 1 ) AND a.id in (select '3561' from com.saviynt.survey.Customer_AttributeValues catt where catt.AttributeValue= 'Classification') AND 1=1 )]; nested exception is org.hibernate.QueryException: could not resolve property: AttributeValue of: com.saviynt.survey.Customer_AttributeValues [select a.id from com.saviynt.ecm.identitywarehouse.domain.Users a where a.id=3561 AND (( 1 = 1 ) AND a.id in (select '3561' from com.saviynt.survey.Customer_AttributeValues catt where catt.AttributeValue= 'Classification') AND 1=1 )] at com.saviynt.ecm.services.changeaction.UserChangeActionService$_getMatchRuleForuser_closure71.doCall(UserChangeActionService.groovy:3226) at com.saviynt.ecm.services.changeaction.UserChangeActionService.getMatchRuleForuser(UserChangeActionService.groovy:3070) at com.saviynt.ecm.services.changeaction.UserChangeActionService.checkAndExecuteRules(UserChangeActionService.groovy:268) at com.saviynt.rulerun.RuleRunService$_runRulesWithTrail_closure11_closure32_closure33.doCall(RuleRunService.groovy:672) at com.saviynt.rulerun.RuleRunService$_runRulesWithTrail_closure11_closure32.doCall(RuleRunService.groovy:616) at com.saviynt.rulerun.RuleRunService$_runRulesWithTrail_closure11.doCall(RuleRunService.groovy:614) at com.saviynt.rulerun.RuleRunService.runRulesWithTrail(RuleRunService.groovy:613) at com.saviynt.rulerun.RuleRunService.runRules(RuleRunService.groovy:57) at RuleRunJob.execute(RuleRunJob.groovy:60) at org.quartz.core.JobRunShell.run(JobRunShell.java:199) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)Caused by: org.hibernate.QueryException: could not resolve property: AttributeValue of: com.saviynt.survey.Customer_AttributeValues [select a.id from com.saviynt.ecm.identitywarehouse.domain.Users a where a.id=3561 AND (( 1 = 1 ) AND a.id in (select '3561' from com.saviynt.survey.Customer_AttributeValues catt where catt.AttributeValue= 'Classification') AND 1=1 )]

[Dhruv_S]

Hi @Tezinder 

Userkey is not supported in userupdate rule.

Tested with (##a.customproperty14 isupdated##) AND (a.userkey= '144')  -->Failed

HibernateQueryException: could not resolve property: userkey of: com.saviynt.ecm.identitywarehouse.domain.Users [select a.id from com.saviynt.ecm.identitywarehouse.domain.Users a where a.id=144 AND (( 1 = 1 ) AND (a.userkey= '144')"

Tested with (##a.customproperty14 isupdated##) AND (a.username= 'DarshanBA')--> Worked

Userkey is not supported. Username is supported.

Similarly the logs you have mentioned also contains unsupported attributes like AttributeValue.

Please refer below for the list of supported attributes mentioned in the documentation.

The user attributes specified in the Advanced Config query are case-sensitive. The supported user attributes to be used in the Advanced Config are as follows:

username,firstname,preferedFirstName,lastname,middlename,street,city,comments,statuskey,startdate,enddate,manager,password,location,jobCode,jobDescription,employeeType, systemUserName,departmentNumber,title,state,companyname,costcenter,departmentname,employeeclass,entity,jobcodedesc,locationdesc,locationnumber,siteid,orgunitid,region,regioncode, owner,employeeid,lastsyncdate,createdate,email,phonenumber,job_function,country,displayname,locale,customproperty1-65

Regards,

Dhruv Sharma