Announcing the SAVIYNT KNOWLEDGE EXCHANGE unifying the Saviynt forums, documentation, training, and more in a single search tool across platforms. Click HERE to read the Announcement.

Analytics Query columns in Email Templates - body

IAM_99
Regular Contributor II
Regular Contributor II

Hi Team,

We have below analytics query

select users.userkey,users.firstname,users.lastname,users.email,users.username,account.lastlogondate from xxxxxx

We want to show to show  firstname,lastname,lastlogon ( sql results)  in email body and 

tried below but email is not generating

${users?.firstname} ${users?.lastname}    ${account.lastlogondate}

${users.firstname} ${users.lastname} ${account.lastlogondate}

10 REPLIES 10

rushikeshvartak
All-Star
All-Star

Refer Example 3 https://saviynt.freshdesk.com/support/solutions/articles/43000622153-managing-email-templates


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

sk
All-Star
All-Star

Update query as below 

select users.userkey,users.firstname as firstname ,users.lastname as lastname,users.email,users.username,account.lastlogondate as lastlogondate from xxxxxx

And you can use below parameters

ANALYTICSDATA.firstname

ANALYTICSDATA.lastname

ANALYTICSDATA.lastlogondate 

Refer link for more details: https://docs.saviyntcloud.com/bundle/SSM-Admin-v55x/page/Content/Chapter06-Configuring-SSM/Creating-...


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

IAM_99
Regular Contributor II
Regular Contributor II

Thanks for the response.  I am adding adding data format in the Query it works fine in Data analyzer.

but when we copy same in Analytics and run it says "Alert Fail" , Can we not use this below ?

DATE_FORMAT(a.lastlogondate, '%d %b %Y')

DATE_FORMAT(a.lastlogondate, '%d %b %Y') as lastlogondat


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

If I understand correctly if you run the query through analytics you are getting output but email is not triggering?


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

IAM_99
Regular Contributor II
Regular Contributor II

yes getting below , Query is this 

select users.userkey,users.firstname,users.lastname,users.email,users.username,
a.lastlogondate as "userlastlogondate", DATE_FORMAT(a.lastlogondate, '%d %b %Y') as "formateddate",

${ANALYTICSDATA.formateddate[0]} in email template

IAM_99_0-1674228899582.png

 

select users.userkey,users.firstname,users.lastname,users.email,users.username,
a.lastlogondate as "userlastlogondat", DATE_FORMAT(a.lastlogondat, '%d %b %Y') as "formateddat" from users 

column name should not ends with date keyword

${ANALYTICSDATA.formateddat[0]} in email template

 


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

Just remove the alias and try it should work. It worked when i tried the same.

If you need alias I would suggest not use date at then end of the name, you can try something like DATE_FORMATED, Same worked for me


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

IAM_99
Regular Contributor II
Regular Contributor II

Thanks Rushi and SK yeah it worked.

 i have one last question on this topic -  Can we send email (analytics email) with out attachment ?

No not possible


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.