Advanced Query for User Update Rule

[aarthianand93]

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

[PremMahadikar]

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

[aarthianand93]

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]

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

[rushikeshvartak]

Please share logs and rule screenshot.

[aarthianand93]

Hi @rushikeshvartak,

Attached are the logs and rule screenshot. 

Thank you. 

Regards,

Aarthi Anand

[PremMahadikar]

@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

[rushikeshvartak]

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

@PremMahadikar  typo comma fixed

[aarthianand93]

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

[PremMahadikar]

@aarthianand93 ,

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

[aarthianand93]

@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

[rushikeshvartak]

Please share logs

[aarthianand93]

@rushikeshvartak @PremMahadikar 

Attached are the logs.

[rushikeshvartak]

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

[aarthianand93]

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

[aarthianand93]

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 · Accepted solution]

@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)

Please check once again with the above query.

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

[aarthianand93]

Hi @rushikeshvartak and @PremMahadikar,

Thank you, it is working now. 

Regards,

Aarthi Anand

[PremMahadikar]

@aarthianand93 , Perfect!

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

Also hit kudos!

[PremMahadikar]

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

[iam01]

@PremMahadikar  @aarthianand93 Hey, when we do the date comparison at the end and start by checking the update condition, it's not working as we expected? Is my understanding correct?? Is that the fix??

[PremMahadikar]

@iam01 ,

The comparison works with update condition.

Did you exactly try the below format?

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