Monitoring XS Jobs and send eMail with finish status
The aim of this function is to check the return code of the execution of XS jobs and send via eMail a table with Job Name, how its finish (SUCCESS or ERROR) and date of the execution.
Following a function to check every day how finish the XS jobs and send email with format:
function ENVIAR_MAIL()
{
var output = “<br>”;
var conn = $.db.getConnection();
var pstmt = conn.prepareStatement(“SELECT NAME,STATUS,CAST(finished_at AS DATE) from _SYS_XS.JOB_LOG WHERE NAME NOT LIKE ‘%ENVIAR_MAIL’ AND finished_at LIKE CONCAT(cast (now() as date),’%’)”);
var rs = pstmt.executeQuery();
var meta = rs.getMetaData();
var i=1;
var colCount = meta.getColumnCount();
output = output + “<table border=\”2\”><tr bgcolor=\”#0000FF\”>”;
var code = “”;
var despedida = “The execution of all jobs finish successfully”;
for (i=1; i<= colCount ; i++)
{
output = output + “<td>” + meta.getColumnName(i) + “</td>”;
}
output = output + “</tr>”;
var INDICE = 1;
var vals = [];
var data = [];
while(rs.next())
{
INDICE = 2;
try
{
vals = rs.getString(INDICE);
data.push(vals);
if (vals===”SUCCESS”)
{
output=output+”<tr bgcolor=\”lime\”> <font color=\”white\”>”;
INDICE ++;
}
else
{
output=output+”<tr bgcolor=\”red\”> <font color=\”white\”>”;
INDICE ++;
despedida = “Please, check the jobs with erros marked in red in previous table.”;
}
}
catch (e)
{
output=output+”<tr bgcolor=\”lime\”> <font color=\”white\”>”;
code = e.message;
}
for (i = 1; i <= colCount ; i++)
{
if ((i+2)/3===1)
{
output = output + “<td>” + rs.getString(i).split(“::”)[1] + “</td>”;
}
else
{
output = output + “<td>” + rs.getString(i) + “</td>”;
}
}
output=output+”</tr>”;
}
output = output + “</table>”;
rs.close();
pstmt.close();
conn.close();
var mail = new $.net.Mail({
sender: {address: “SMaxus@gmail.com“},
to:
[
{name: “Name and Surname1”, address: “user1@gmail.com“, nameEncoding: “US-ASCII”},
{name: “Name and Surname2”, address: “user2@gmail.com“, nameEncoding: “US-ASCII”},
{name: “Name and Surname3”, address: “user3@gmail.com“, nameEncoding: “US-ASCII”}
],
cc: [{name: “Juan de la Cruz de Arellano Royo”, address: “Juande.SAP.Certified@gmail.com“, nameEncoding: “US-ASCII”},],
subject: “Job Monitoring”,
subjectEncoding: “UTF-8”,
parts: [ new $.net.Mail.Part({
type: $.net.Mail.Part.TYPE_TEXT,
text: “Hola, <br><br>This is an automatic mail checking the status of job’s execution. <br><br>Following a table with the executed jobs today and their status: <br>” + output + code + “<br><br>” + despedida + “<br><br>Grettings<br>Juan de la Cruz de Arellano Royo”,
contentType: “text/html”,
encoding: “UTF-8”
})]
});
mail.send();
}
How it’s works
———————–
VARIABLES:
output = Is the HTML code to embedded the query output to SYS_XS.JOB_LOG
conn = Is used to create the connection to HANA DB
pstmt = Prepare the statement to be executed in HANA to query the status of executed jobs.
rs = Is used to save the resoult of the query
meta = I use it to get metadata and can build the HTML table with job names, status and date
i = Is just a counter
colCount = Is the number of columns of this query
code = I use it to save exceptions and show the error at the end of mail
despedida = Is a text that depends on if there are any error or every job have been succesfully create the end of mail
LOOPS:
1. Create the table with the correct column name
for (i=1; i<= colCount ; i++) { output = output + “<td>” + meta.getColumnName(i) + “</td>”; } |
2. How to fill the HTML table from XSjs query
while(rs.next()) { INDICE = 2; try { vals = rs.getString(INDICE); data.push(vals); if (vals===”SUCCESS”) { output=output+”<tr bgcolor=\”lime\”> <font color=\”white\”>”; INDICE ++; } else { output=output+”<tr bgcolor=\”red\”> <font color=\”white\”>”; INDICE ++; despedida = “Please, check the jobs with erros marked in red in previous table.”; } } catch (e) { output=output+”<tr bgcolor=\”lime\”> <font color=\”white\”>”; code = e.message; }
for (i = 1; i <= colCount ; i++) { if ((i+2)/3===1) { output = output + “<td>” + rs.getString(i).split(“::”)[1] + “</td>”; } else { output = output + “<td>” + rs.getString(i) + “</td>”; } } output=output+”</tr>”; } |
3. In the previous loop we check the below sentence if the field STATUS is SUCCESS or ERROR to create the table row with background red color or green (if SUCCESS)
if (vals===”SUCCESS”) { output=output+”<tr bgcolor=\”lime\”> <font color=\”white\”>”; INDICE ++; } else { output=output+”<tr bgcolor=\”red\”> <font color=\”white\”>”; INDICE ++; despedida = “Please, check the jobs with erros marked in red in previous table.”; } |
The end of this fuction started with var mail = new $.net.Mail is the construction of the mail and send it
Enjoy it
Juan de la Cruz Arellano Royo
Hi,
this blog is very useful, is there a standard way of sending alerts if XS job fails?
In Solution Manager, you can configure a Custom Job Type on the Job Monitoring (Powered by Monitoring and Alerting Infrastructure). Let me know in case you need more information
Hi Benu, We dont have solution manager configured, Is not there any standard notification available in HANA XS for job failure?
Siva
Hi Benu Mariantony,
Kindly share further details to enable XS Job monitoring alert (in case of fail/any) using Solution Manger. We've the requirement to set up the same.
Regards
Chandra
Hello Chandra,
Please follow the document Job Management - Solution Manager - Community Wiki (sap.com). It is for the IBP Job Monitoring. The steps are the same for XS Job Montoring.
Regards,
Benu