Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Advanced Query for User Update Rule

aarthianand93
New Contributor II
New Contributor II

Hi Team, 

I used the previous tickets as reference to make an advanced query for a user update rule. 

https://forums.saviynt.com/t5/identity-governance/advanced-query-for-create-date-in-technical-rule/t...

https://forums.saviynt.com/t5/identity-governance/user-update-rule-use-quot-is-updated-quot-in-advan...

However, this is not working. The objective is to achieve this condition - term+1<=sysdate().

a.statuskey = 0 AND (##a.statuskey is updated##) AND (date_add(a.enddate, INTERVAL 1 DAY) <= CURDATE())

I tried with the ## values as well, however the query is not working. Please assist. 

Regards,

Aarthi Anand

 

19 REPLIES 19

PremMahadikar
All-Star
All-Star

Hi @aarthianand93 ,

This is your current query -

a.statuskey = 0 AND (##a.statuskey is updated##) AND (date_add(a.enddate, INTERVAL 1 DAY) <= CURDATE())

The correct format is (##a.statuskey isupdated##) without space. Please try!

I would also suggest, split the query and try it: For example:

  1. Ony passign this - a.statuskey = 0 AND (##a.statuskey isupdated##)
  2. Only passing - date_add(a.enddate, INTERVAL 1 DAY) <= CURDATE()
  3. If both are working individually, join it and try.

 

If this helps your question, please consider selecting Accept As Solution and hit Kudos

Hi @PremMahadikar,

I have tried with the query by removing the space. But it is saying invalid condition. 

Please let me know if there is anything else that has to be modified. I did some trial and error but it's not working. Thank you.

a.statuskey = 0 AND (##a.statuskey isupdated##) AND (date_add(a.enddate, INTERVAL 1 DAY) <= CURDATE())

Regards,

Aarthi Anand

aarthianand93
New Contributor II
New Contributor II

Hi Team, 

We have tried in older version 5.5 with the same query and it is validating the condition. 

However, in 24.2 version the same query says invalid condition. 

Advanced Query for Condition {termdate+1<=curdate()} -

a.statuskey = 0 AND ##a.statuskey isupdated## AND date_add(a.enddate, INTERVAL 1 DAY) <= CURDATE()

Please let us know if we can modify this query in anyway to make it work in 24.2 version. 

Regards,

Aarthi Anand

Please share logs and rule screenshot.


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

Hi @rushikeshvartak,

Attached are the logs and rule screenshot. 

Thank you. 

Regards,

Aarthi Anand

@aarthianand93 ,

date_add is not working in 24.x version, even though it works in data analyzer.

Error: Unexpected token 

Check the below: a.statuskey = 0 AND ##a.statuskey isupdated## AND a.enddate,  <= CURDATE()

The above should work fine.

For date_add, please raise a bug on freshdesk.

If this helps your question, please consider selecting Accept As Solution and hit Kudos

a.statuskey = 0 AND ##a.statuskey isupdated## AND a.enddate  <= CURDATE()

 

@PremMahadikar  typo comma fixed


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

Hi @PremMahadikar,

I tried this query which you provided. 

a.statuskey = 0 AND ##a.statuskey isupdated## AND a.enddate <= CURDATE()

The isupdated condition is not working. If we remove this condition it is working. Is there anyway we can add the isupdated condition? 

Regards,

Aarthi Anand

@aarthianand93 ,

You tried preview or tried updating the user which would pick the rule?

 

@PremMahadikar 

Both. Preview says Invalid Condition. We updated a user and the Rule Run shows as blank. The rule is not previewable or triggered if we add the isupdated condition. 

Regards,

Aarthi Anand

Please share logs


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

@rushikeshvartak @PremMahadikar 

Attached are the logs.

fix below error in rule.

"2024-05-23T05:13:45.111+00:00","ecm-worker","hql.PARSER","quartzScheduler_Worker-2-8dx6d","ERROR","line 1:124: unexpected token: AN"
"2024-05-23T05:13:45.131+00:00","ecm-worker","changeaction.UserChangeActionService","quartzScheduler_Worker-2-8dx6d","DEBUG","error in User update rule executing qry:"
"2024-05-23T05:13:45.631+00:00","ecm-worker","","null-8dx6d","","org.springframework.orm.hibernate3.HibernateQueryException: unexpected token: AN near line 1, column 124 [select a.id from com.saviynt.ecm.identitywarehouse.domain.Users a where a.id=16 AND ( 1 = 1 AND a.customproperty2 in('('AN','FW','CD','WE','SF','SW','WF','WW')') AND a.statuskey = '1' AND 1=1 )]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: unexpected token: AN near line 1, column 124 [select a.id from com.saviynt.ecm.identitywarehouse.domain.Users a where a.id=16 AND ( 1 = 1 AND a.customproperty2 in('('AN','FW','CD','WE','SF','SW','WF','WW')') AND a.statuskey = '1' AND 1=1 )] at


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

Hi @rushikeshvartak,

The IN condition is not working in the user update query. That is a different rule. 

https://forums.saviynt.com/t5/identity-governance/user-update-rule-in-condition-not-working-possible...

I have raised a different thread for this. 

Regards,

Aarthi Anand

Hi @rushikeshvartak,

This error you mentioned in the logs shared is fixed. That is a different rule.

But the advanced query for termination condition with ##a.statustuskey isupdated## is not working. 

Please assist on this. I have added the screenshots as well in the previous reply. Thank you. 

Regards,

Aarthi Anand

PremMahadikar
All-Star
All-Star

@aarthianand93 ,

The below is working fine for me. (v24.5)

a.enddate <= CURDATE() AND a.statuskey=0 AND (##a.statuskey isupdated##)

The rule is getting picked up. (Preview will not work as you statuskey updated in query)

PremMahadikar_2-1716450506978.png

PremMahadikar_1-1716450476534.png

Please check once again with the above query.

 

If this helps your question, please select Accept As Solution and hit Kudos

aarthianand93
New Contributor II
New Contributor II

Hi @rushikeshvartak and @PremMahadikar,

Thank you, it is working now. 

Regards,

Aarthi Anand

PremMahadikar
All-Star
All-Star

@aarthianand93 , Perfect!

Can you please select 'Accept as Solution' for the reply (or replies) that best answered your question.

Also hit kudos!

PremMahadikar
All-Star
All-Star

Hi @aarthianand93 ,

I want to let you know the correct format to use date+1 in rules is

  • adddate(a.enddate,1)

I also tested the below, it works fine.

a.statuskey=0 and adddate(a.enddate,1) <= curdate() and (##a.statuskey isupdated##)

 

Please select Accept As Solution and hit Kudos