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