Launch Certification from Rule Job failing

[h_sapkota]

Hi Team,

We are triggering certification on change in few attributes as part of the Mover/Transfer process.

Below is the Screenshot of User Update Rule:

Mover Rule is triggering for a user but when I run the Launch Certification from Rule Job, the JOB is completing with the Status as Failure.

Upon checking logs, we found below:

So, Now It can be because of our condition:
STR_TO_DATE(a.customproperty21, '%Y-%m-%d') <= DATE_FORMAT(curdate(), '%Y-%m-%d') AND DATEDIFF(curdate(), STR_TO_DATE(a.customproperty21, '%Y-%m-%d')) <= 15

How do we write the above condition which is also supported in HQL so that our JOB for Launching campaign is successful.

The JOB was successful before writing this two condition in the User Update RULE.

[naveenss]

Hi @h_sapkota ,

I don't see any problem with the above condition. Can you share the detailed log along with the configurations done for "Launch Campaign" under the user update rule? Also, share the full query configured in the rule. 

[AmitM]

Hi @h_sapkota , I dont think it is the query as the rule has triggered. That was the job of query.

If the Launch cert job is failing it must be in campaign configs , something might be wrong there.

Thanks,

Amit

If this answers your query, Please ACCEPT SOLUTION and give KUDOS.

[h_sapkota]

Hi @AmitM and @naveenss ,

The Launch Cert Job was working up until yesterday before the change is done in the added condition.
No Change was done in the Campaign Configuration side.

Below is the query in User Update Rule:
((##a.customproperty16 isupdated##) OR (##a.jobCode isupdated##) OR (##a.customproperty10 isupdated##) OR (##a.customproperty5 isupdated##)) AND a.customproperty2 in ('5','6','7','8','9','10') AND a.employeeType in ('Employee', 'Agency Employee') AND STR_TO_DATE(a.customproperty21, '%Y-%m-%d') <= DATE_FORMAT(curdate(), '%Y-%m-%d') AND DATEDIFF(curdate(), STR_TO_DATE(a.customproperty21, '%Y-%m-%d')) <= 15

Attaching the SS of the campaign config:

Below are the logs details:

DEBUG jobs.LaunchCertificationFromRuleJob - Launch Certification from Rule Job Starts

2024-03-12T16:26:22+05:30-ecm-worker-2024-03-12T10:56:21.937476583Z stdout F 2024-03-12 10:56:21,937 [quartzScheduler_Worker-9] DEBUG campaigns.CampaignService - Finding users satisfying Condition for User update Rule to launch Certification

2024-03-12T16:26:22+05:30-ecm-worker-2024-03-12T10:56:22.002367122Z stdout F 2024-03-12 10:56:22,002 [quartzScheduler_Worker-9] DEBUG campaigns.CampaignService - userlist-[ca16007, nj16009]

2024-03-12T16:26:22+05:30-ecm-worker-2024-03-12T10:56:22.007989542Z stdout F 2024-03-12 10:56:22,007 [quartzScheduler_Worker-9] DEBUG campaigns.CampaignService - ruleUserMap=-[2:[83, 92]]

2024-03-12T16:26:22+05:30-ecm-worker-2024-03-12T10:56:22.009658148Z stdout F 2024-03-12 10:56:22,009 [quartzScheduler_Worker-9] DEBUG campaigns.CampaignService - hanaRule=-com.saviynt.hana.HanaRule : 2

2024-03-12T16:26:22+05:30-ecm-worker-2024-03-12T10:56:22.032471932Z stdout F 2024-03-12 10:56:22,032 [quartzScheduler_Worker-9] ERROR hql.PARSER - <AST>:0:0: unexpected end of subtree

2024-03-12T16:26:22+05:30-ecm-worker-2024-03-12T10:56:22.033491936Z stdout F 2024-03-12 10:56:22,033 [quartzScheduler_Worker-9] DEBUG jobs.LaunchCertificationFromRuleJob - END Launch Certification from Rule Job

2024-03-12T16:26:21+05:30-ecm-2024-03-12T10:56:21.008832566Z stdout F 2024-03-12 10:56:21,008 [http-nio-8080-exec-11] DEBUG domain.FlatViewJobcontrolController -

[naveenss]

Hi @h_sapkota  the exact same configurations are working in my instance along with the advanced query. Ideally it should work for you as well. I don't see any configuration problem here. 

Quick question, you mentioned if you remove the condition STR_TO_DATE(a.customproperty21, '%Y-%m-%d') <= DATE_FORMAT(curdate(), '%Y-%m-%d') AND DATEDIFF(curdate(), STR_TO_DATE(a.customproperty21, '%Y-%m-%d')) <= 15,  the certification is getting launched. Is that correct? Can you please validate this behavior once again?

[h_sapkota]

Hi @naveenss ,

The issues was with the cache of previous Rule runs. Since I was running multiple times same rule for a user, Old Errored HQL query was in the cache of rule which prevented from launching the Campaign. 

When I change the query back to Old and trigger certification properly. And Again on changing the query back to the DATE condition's one, it is working as expected now.

[naveenss]

Great. Please close the thread!

[h_sapkota]

Hi @naveenss ,

The issue that we are facing is In QA environment where we have same configuration as Dev, It is still giving us the failure as mentioned above.

DEBUG jobs.LaunchCertificationFromRuleJob - Launch Certification from Rule Job Starts

2024-03-12T16:26:22+05:30-ecm-worker-2024-03-12T10:56:21.937476583Z stdout F 2024-03-12 10:56:21,937 [quartzScheduler_Worker-9] DEBUG campaigns.CampaignService - Finding users satisfying Condition for User update Rule to launch Certification

2024-03-12T16:26:22+05:30-ecm-worker-2024-03-12T10:56:22.002367122Z stdout F 2024-03-12 10:56:22,002 [quartzScheduler_Worker-9] DEBUG campaigns.CampaignService - userlist-[ca16007, nj16009]

2024-03-12T16:26:22+05:30-ecm-worker-2024-03-12T10:56:22.007989542Z stdout F 2024-03-12 10:56:22,007 [quartzScheduler_Worker-9] DEBUG campaigns.CampaignService - ruleUserMap=-[2:[83, 92]]

2024-03-12T16:26:22+05:30-ecm-worker-2024-03-12T10:56:22.009658148Z stdout F 2024-03-12 10:56:22,009 [quartzScheduler_Worker-9] DEBUG campaigns.CampaignService - hanaRule=-com.saviynt.hana.HanaRule : 2

2024-03-12T16:26:22+05:30-ecm-worker-2024-03-12T10:56:22.032471932Z stdout F 2024-03-12 10:56:22,032 [quartzScheduler_Worker-9] ERROR hql.PARSER - <AST>:0:0: unexpected end of subtree

2024-03-12T16:26:22+05:30-ecm-worker-2024-03-12T10:56:22.033491936Z stdout F 2024-03-12 10:56:22,033 [quartzScheduler_Worker-9] DEBUG jobs.LaunchCertificationFromRuleJob - END Launch Certification from Rule Job

2024-03-12T16:26:21+05:30-ecm-2024-03-12T10:56:21.008832566Z stdout F 2024-03-12 10:56:21,008 [http-nio-8080-exec-11] DEBUG domain.FlatViewJobcontrolController -

Below are the configuration in QA:
((##a.customproperty16 isupdated##) OR (##a.jobCode isupdated##) OR (##a.customproperty10 isupdated##) OR (##a.customproperty5 isupdated##)) AND a.customproperty2 in ('5','6','7','8','9','10') AND a.employeeType in ('Employee', 'Agency Employee') AND (DATEDIFF(curdate(), a.customproperty21) <= 15 and DATEDIFF(curdate(), a.customproperty21) >= 0 )

Please note, we have optimized our query as above working as expected in DEV environment.

Campaign Configuration:

[rushikeshvartak]

its data issue in QA