Issue in send email via analytics

[TheSaviyntBoy]

Hi,

We have a usecase where we need to send an email to the user's manager that their reportee's AD account will be deleted in 10 days. Their deletion policy is 30 days so I am sending an email 20 days after the enddate of the user. 

I am getting the following error: 

groovy.lang.MissingPropertyException: No such property: manager for class: SimpleTemplateScript15728 at SimpleTemplateScript15728.run(SimpleTemplateScript15728.groovy:1) at com.saviynt.ecm.analytics.AnalyticsESService.notifyEmailToOwner(AnalyticsESService.groovy:2677) at com.saviynt.ecm.analytics.AnalyticsESService.emailAction(AnalyticsESService.groovy:16630) at com.saviynt.ecm.analytics.AnalyticsESService.runControl(AnalyticsESService.groovy:14651) at com.saviynt.ecm.analytics.AnalyticsESHelperService.$tt__doExecute(AnalyticsESHelperService.groovy:711) at com.saviynt.ecm.analytics.AnalyticsESHelperService$_$tt__runAnalyticsFromJob_closure27.doCall(AnalyticsESHelperService.groovy:651) at com.saviynt.ecm.analytics.AnalyticsESHelperService.$tt__runAnalyticsFromJob(AnalyticsESHelperService.groovy:644) at RunAllAnalyticsJob.execute(RunAllAnalyticsJob.groovy:174) at org.quartz.core.JobRunShell.run(JobRunShell.java:199) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)

My analytics config: 

The query: 

SELECT
u.username AS 'user username',
(SELECT m.username FROM users m WHERE u.manager = m.userkey) AS 'username',
(SELECT m.firstname FROM users m WHERE u.manager = m.userkey) AS 'manager.firstname'
u.email,
u.startdate,
u.enddate
FROM
users u
WHERE
CURDATE() = DATE_ADD(u.enddate, INTERVAL 20 DAY);

Email template: 

If I give static values then the email is getting triggered correctly. 

I have crosschecked if the manager has a firstname and user has customproperty8

[NM]

Hi @TheSaviyntBoy , In TO field give attribute ${user.email}

By this analytics will be send to user in an excel format

[TheSaviyntBoy]

Hi @NM 

The TO field will be ${manager.email} since we are sending the email to the user's manager. 

And we just need to notify that the account will be removed after 10 days. 

[pmahalle]

Hi @TheSaviyntBoy ,

Make sure to select all those fields which you want to send in body in your select query use below expression in your email template:

${ANALYTICSDATA.<column-name-from-query>[0]}

SELECT
u.username AS 'user username',
(SELECT m.username FROM users m WHERE u.manager = m.userkey) AS 'username',
(SELECT m.firstname FROM users m WHERE u.manager = m.userkey) AS 'managerfirstname'
u.email,
u.startdate,
u.enddate,

u.customproperty8 as application

FROM
users u
WHERE
CURDATE() = DATE_ADD(u.enddate, INTERVAL 20 DAY);

So, in your case use below expression

Manager Name: ${ANALYTICSDATA.managerfirstname[0]}  

Customproperty8: ${ANALYTICSDATA.application[0]}

[NM]

Hi @TheSaviyntBoy , as per your query username column is storing manager username so you can directly use it.

[TheSaviyntBoy]

Hi @pmahalle @NM 

Giving the same kind of error:

groovy.lang.MissingPropertyException: No such property: ANALYTICSDATA for class: SimpleTemplateScript16015 at SimpleTemplateScript16015.run(SimpleTemplateScript16015.groovy:1) at com.saviynt.ecm.analytics.AnalyticsESService.notifyEmailToOwner(AnalyticsESService.groovy:2677) at com.saviynt.ecm.analytics.AnalyticsESService.emailAction(AnalyticsESService.groovy:16630) at com.saviynt.ecm.analytics.AnalyticsESService.runControl(AnalyticsESService.groovy:14651) at com.saviynt.ecm.analytics.AnalyticsESHelperService.$tt__doExecute(AnalyticsESHelperService.groovy:711) at com.saviynt.ecm.analytics.AnalyticsESHelperService$_$tt__runAnalyticsFromJob_closure27.doCall(AnalyticsESHelperService.groovy:651) at com.saviynt.ecm.analytics.AnalyticsESHelperService.$tt__runAnalyticsFromJob(AnalyticsESHelperService.groovy:644) at RunAllAnalyticsJob.execute(RunAllAnalyticsJob.groovy:174) at org.quartz.core.JobRunShell.run(JobRunShell.java:199) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)

Modified my query to:

SELECT
u.username AS 'user username',
(SELECT m.username FROM users m WHERE u.manager = m.userkey) AS 'username',
(SELECT m.firstname FROM users m WHERE u.manager = m.userkey) AS 'managerfirstname',
u.email,
u.startdate,
u.enddate,
u.customproperty8 as 'sAMAccountName'
FROM
users u
WHERE
CURDATE() = DATE_ADD(u.enddate, INTERVAL 20 DAY);

And the template: 

[pmahalle]

@TheSaviyntBoy  Make sure to enable Group emails by username option under Global configurations --> Analytics, if not enabled already and check again.

[TheSaviyntBoy]

Hi @pmahalle 

It is already enabled.

[TheSaviyntBoy]

Hi @pmahalle @NM 

I have added ${this.binding.variables.each {k,v -> println "$k = $v" + "<" + "br" + ">"}} to see all the exposed objects in the template and this is the result: 

[rushikeshvartak]

Your TO field in email template should be ${userEmail} only

[TheSaviyntBoy]

@rushikeshvartak Currently I am giving a static email address for testing in the TO field.

The issue is coming with the "${ANALYTICSDATA.managerfirstname[0]}" and "${ANALYTICSDATA.sAMAccountName[0]}" as if I change these binding variables to static words, the email comes through correctly

[rushikeshvartak]

As mentioned TO address should be ${userEmail} 

you can keep your email in cc / bcc not in TO then only it will work

[TheSaviyntBoy]

@rushikeshvartak I did as you suggested, getting a different error now:

groovy.lang.MissingPropertyException: No such property: lastAnalyticsHistoryKey for class: com.saviynt.ecm.analytics.AnalyticsESService at com.saviynt.ecm.analytics.AnalyticsESService.notifyEmailToOwner(AnalyticsESService.groovy:2465) at com.saviynt.ecm.analytics.AnalyticsESService.emailAction(AnalyticsESService.groovy:16630) at com.saviynt.ecm.analytics.AnalyticsESService.runControl(AnalyticsESService.groovy:14651) at com.saviynt.ecm.analytics.AnalyticsESHelperService.$tt__doExecute(AnalyticsESHelperService.groovy:711) at com.saviynt.ecm.analytics.AnalyticsESHelperService$_$tt__runAnalyticsFromJob_closure27.doCall(AnalyticsESHelperService.groovy:651) at com.saviynt.ecm.analytics.AnalyticsESHelperService.$tt__runAnalyticsFromJob(AnalyticsESHelperService.groovy:644) at RunAllAnalyticsJob.execute(RunAllAnalyticsJob.groovy:174) at org.quartz.core.JobRunShell.run(JobRunShell.java:199) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)

[rushikeshvartak]

Now keep below only in body and validate 

To get what variables are present to use in the email template, check using the below expression. By putting it in the email body, it will print all variable names with values, and then accordingly, you can use it:


${this.binding.variables.each {k,v -> println "$k = $v" + "" + "br" + ">"}}

[TheSaviyntBoy]

@rushikeshvartak I am getting the same error as I mentioned above:

groovy.lang.MissingPropertyException: No such property: lastAnalyticsHistoryKey for class: com.saviynt.ecm.analytics.AnalyticsESService at com.saviynt.ecm.analytics.AnalyticsESService.notifyEmailToOwner(AnalyticsESService.groovy:2465) at com.saviynt.ecm.analytics.AnalyticsESService.emailAction(AnalyticsESService.groovy:16630) at com.saviynt.ecm.analytics.AnalyticsESService.runControl(AnalyticsESService.groovy:14651) at com.saviynt.ecm.analytics.AnalyticsESHelperService.$tt__doExecute(AnalyticsESHelperService.groovy:711) at com.saviynt.ecm.analytics.AnalyticsESHelperService$_$tt__runAnalyticsFromJob_closure27.doCall(AnalyticsESHelperService.groovy:651) at com.saviynt.ecm.analytics.AnalyticsESHelperService.$tt__runAnalyticsFromJob(AnalyticsESHelperService.groovy:644) at RunAllAnalyticsJob.execute(RunAllAnalyticsJob.groovy:174) at org.quartz.core.JobRunShell.run(JobRunShell.java:199) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)

[rushikeshvartak]

Create new report and validate. It seems issue with existing report

[TheSaviyntBoy]

@rushikeshvartak 

Still getting the same error.

groovy.lang.MissingPropertyException: No such property: lastAnalyticsHistoryKey for class: com.saviynt.ecm.analytics.AnalyticsESService at com.saviynt.ecm.analytics.AnalyticsESService.notifyEmailToOwner(AnalyticsESService.groovy:2465) at com.saviynt.ecm.analytics.AnalyticsESService.emailAction(AnalyticsESService.groovy:16630) at com.saviynt.ecm.analytics.AnalyticsESService.doFileArchivalAndSendEmail(AnalyticsESService.groovy:14802) at com.saviynt.ecm.analytics.AnalyticsESService$_runControl_closure211_closure322.doCall(AnalyticsESService.groovy:14634) at com.saviynt.ecm.analytics.AnalyticsESService$_runControl_closure211.doCall(AnalyticsESService.groovy:14633) at java.lang.Thread.run(Thread.java:750)

[NM]

@TheSaviyntBoy , send a static text once in email body

[TheSaviyntBoy]

Even with a static body in the email, its giving the same error. This time I created new email template and new analytics too. 

[rushikeshvartak]

This looks like issue with your instance or Elastic search issue. Please raise support ticket.

Also share query here

[Raghu]

@TheSaviyntBoy  check in below advance html css also with 'to' can u try

Query use :

SELECT
u.username,u.username AS 'user username',
(SELECT m.username FROM users m WHERE u.manager = m.userkey) AS 'username',
(SELECT m.firstname FROM users m WHERE u.manager = m.userkey) AS 'managerfirstname',
u.email,
u.startdate,
u.enddate,
u.customproperty8 as 'sAMAccountName'
FROM
users u
WHERE
CURDATE() = DATE_ADD(u.enddate, INTERVAL 20 DAY)