xsjs – Job Notification Via Email with Attachment

Hello All

One of the requirement that we often come across is Jobs Execution Notification in SAP HANA via email .I also want to address the issue of email attachment (We often come across requirements asking to automate the results notification via email as an attachment) – So I thought we can address both the issues today
That’s exactly what we are going to do in this blog, we will build Notification Flow.

1) Email Notification of Job Execution Status
2) Excel Attachment with Job Execution Details

Step1) Create a SMTP Notification (Will not go into details as we have several blogs addressing SMTP Notification
Below is the setting for SAP Outlook

Step2) Create a XSJS File to build the attachment and also send the email

2.1) Create an EMAILNOTIFICATION XSJS File

2.2) Read the Job Execution Details from _SYS_XS.JOB_LOG table

var conn = $.db.getConnection();
var pstmt = conn.prepareStatement("SELECT TOP 100 NAME,STATUS,CAST(finished_at AS DATE) AS FINISHED_AT,ERROR_MESSAGE as ERROR_MESSAGE from _SYS_XS.JOB_LOG WHERE status 'SCHEDULED'");
var pstmt1 = conn.prepareStatement("SELECT DATABASE_NAME FROM SYS.M_DATABASE");
var rs = pstmt.executeQuery();
var rs1 = pstmt1.executeQuery();


2.3)Build the column headings for both Table in email body and Excel Attachment

2.4)Build the email body and attachment contents

2.5) Close the db connections
rs.close();
pstmt.close();
rs1.close();
pstmt1.close();
conn.close();

2.6)Build Email Body and Attachment
var firstPart = new $.net.Mail.Part();
firstPart.type = $.net.Mail.Part.TYPE_ATTACHMENT;
firstPart.data = attachment; // data for the excel attachment
firstPart.contentType = "application/vnd.ms-excel";
firstPart.fileName = "JobStatus.xls";
firstPart.encoding = "UTF-16le";

var thirdPart = new $.net.Mail.Part();
thirdPart.type = $.net.Mail.Part.TYPE_TEXT;
thirdPart.text = " Please Find Job Execution status.
" + emailoutput + message + "" + notification ;
thirdPart.contentType = "text/html";
thirdPart.encoding = "UTF-8";

2.7)Build Send Email
var mail = new $.net.Mail({
sender: {address: "xyz@sap.com"},
to:
[
{name: "Venkat", address: "xyz@sap.com", nameEncoding: "US-ASCII"}
],
cc: [{name: "Venkat", address: "xyz@sap.com", nameEncoding: "US-ASCII"},],
subject: "Job Status from System : " + sid + "--" + notification,
subjectEncoding: "UTF-8"
});
mail.parts.push(firstPart,thirdPart);
var returnValue = mail.send();
var response = "MessageId = " + returnValue.messageId + ", final reply = " + returnValue.finalReply;
$.response.setBody(response);

3) Test XSJS File – For now we will test using XSJS file

4)Check Email

5)Check Email Attachment

If you see the error message shifted to next line
Reason if the see the Error Message in JOB_LOG table the data is in multiline

So we need to change it to a single line

var conn = $.db.getConnection();
var pstmt = conn.prepareStatement("SELECT TOP 100 NAME,STATUS,CAST(finished_at AS DATE) AS FINISHED_AT,REPLACE(REPLACE(ERROR_MESSAGE, CHAR(13), ' '), CHAR(10), ' ') as ERROR_MESSAGE from _SYS_XS.JOB_LOG WHERE status 'SCHEDULED'");
var pstmt1 = conn.prepareStatement("SELECT DATABASE_NAME FROM SYS.M_DATABASE");

Run XSJS again and check file attachment

Finally we need to create an XS Job calling the EMAILNOTIFICATION XSJS and schedule it so that the email notifications with job status are sent out

You can find the entire code at
https://github.com/venkatgoli9/SAPHANA

Thanks
Venkat Goli