Monday 22 July 2019

xsjs – Job Notification Via Email with Attachment

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

Step 1) Create a SMTP Notification (Will not go into details as we have several blogs addressing SMTP Notification

Below is the setting for SAP Outlook

SAP HANA Study Materials, SAP HANA Online Exam, SAP HANA Tutorial and Materials, SAP HANA Certifications

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

2.1) Create an EMAILNOTIFICATION XSJS File

SAP HANA Study Materials, SAP HANA Online Exam, SAP HANA Tutorial and Materials, SAP HANA Certifications

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

SAP HANA Study Materials, SAP HANA Online Exam, SAP HANA Tutorial and Materials, SAP HANA Certifications

2.4) Build the email body and attachment contents

SAP HANA Study Materials, SAP HANA Online Exam, SAP HANA Tutorial and Materials, SAP HANA Certifications

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

SAP HANA Study Materials, SAP HANA Online Exam, SAP HANA Tutorial and Materials, SAP HANA Certifications

4) Check Email

SAP HANA Study Materials, SAP HANA Online Exam, SAP HANA Tutorial and Materials, SAP HANA Certifications

5) Check Email Attachment

SAP HANA Study Materials, SAP HANA Online Exam, SAP HANA Tutorial and Materials, SAP HANA Certifications

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

SAP HANA Study Materials, SAP HANA Online Exam, SAP HANA Tutorial and Materials, SAP HANA Certifications

So we need to change it to a single line

SAP HANA Study Materials, SAP HANA Online Exam, SAP HANA Tutorial and Materials, SAP HANA Certifications

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

SAP HANA Study Materials, SAP HANA Online Exam, SAP HANA Tutorial and Materials, SAP HANA Certifications

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

SAP HANA Study Materials, SAP HANA Online Exam, SAP HANA Tutorial and Materials, SAP HANA Certifications

No comments:

Post a Comment