Using Analytics Data in Email Template

[Bwagne]

We are running 23.10 and having issues trying to use variables from analytics in an email template.

Originally we had an analytics running to disable unused accounts after 30 days and the email would go to the user for that.  This worked in 5.5.

According to the documentation: https://docs.saviyntcloud.com/bundle/EIC-Admin-v24x/page/Content/Chapter06-EIC-Configurations/Creati... 

It says to use ${ANALYTICSDATAMAP} if Group Analytics Emails by Username in Global config is disabled, which ours is.

I have been trying to build a basic test and it also doesn't seem to work.  Not sure what I might be doing wrong.

I built basic analytics based on this post: https://forums.saviynt.com/t5/saviynt-knowledge-base/leverage-analytics-to-send-mails-to-users-individually-with/ta-p/47644 

Here is the analytics:
SELECT DISTINCT u.username AS USERNAME, Count(ua.accountkey) AS 'Count of Accounts in SSM', u.manager, u.displayname as Displayname, u.systemusername as 'Weird Name' FROM users u, user_accounts ua WHERE u.userkey = ua.userkey AND u.userkey IN ( 107, 3) GROUP BY u.username

Here is the email template:

When I view the logs I can see that the analytics ran and once I also set to save the data I can see the run in the analytics history.  However I see nothing in the logs that it's even trying to send an email.

[JohnLawson]

I'm accessing analytic data via the template like so:
${ANALYTICSDATA.'validthrough'[0]}

where validthrough is the column name. I would also suggest renaming the column name to have no spaces as that may be causing issues possibly.

[JohnLawson]

Also, consider using an email template and adding an email to the disable account task on the endpoint.

The analytic can have a default action to disable the account, which would create a disable account task that, once completed, would send out the notification:

[Bwagne]

I already had this working with the Default action in the analytics.  It's the email template that breaks the analytics as it will not even run if the email template has improper variables or just doesn't fire in general.  If you remove the template the analytics runs fine.  The product supports it, just trying to figure out how it's supposed to work as it seems to be very finicky.

I could maybe setup an email template for this account disable, but that won't help me for other analytics.

[rushikeshvartak]

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" + ">"}}

[Bwagne]

This did not provide me a list of variables from my analytics.

Here is the email template:

And here's what I got:
date = 2024-01-30 15:08:25br> endpoint = nullbr> totalRecords = 2br> analyticsConfig = com.saviynt.ecm.analytics.AnalyticsConfigES : 563br> endpointDisplayName = nullbr> baseUrlForEmail = https://ssm-dev-company.ssmcloud.net/ECM/br > ownerEmail = br> out = java.io.PrintWriter@517476ddbr> These are the variables for BW - Test Analytics Accounts - Email

[date:2024-01-30 15:08:25, endpoint:null, totalRecords:2, analyticsConfig:com.saviynt.ecm.analytics.AnalyticsConfigES : 563, endpointDisplayName:null, baseUrlForEmail:https://ssm-dev-company.ssmcloud.net/ECM/, ownerEmail:, out:java.io.PrintWriter@517476dd]

[rushikeshvartak]

what u need as output ?

[JohnLawson]

Try ${ANALYTICSDATA.'displayname'[0]} with the below query

select distinct u.username as username, count(ua.accountkey) as accountnum, u.manager,u.displayname as displayname, u.systemusername as sysusername from users u, user_accounts ua where u.userkey=ua.userkey group by u.username

[rushikeshvartak]

Make sure below option is enabled.

Where do you want to save your data?

 Send Email As Attachment

[Bwagne]

 I already have that selected.  I got an email with the attachment and the body had what I posted above in it.

[Dhruv_S]

Hi @Bwagne @JohnLawson 

We are looking into it and will keep you posted.

Regards,

Dhruv Sharma

[rushikeshvartak]

Below are configs 

• Global Config

• Create report
• 
• use email variables ${ANALYTICSDATA} in body

[Dhruv_S]

Hi @Bwagne @JohnLawson @rushikeshvartak 

We have tried to replicate the issue and email is getting received. Please try with below email template and analytics and see if it works. 

Please find the sample email template and query used below.

Analytics Query and details

Email template

Received email