Skip to Content
Author's profile photo Juan de la Cruz Arellano Royo

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

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Srinivas Manda
      Srinivas Manda

      Hi,

      this blog is very useful, is there a standard way of sending alerts if XS job fails?

      Author's profile photo Benu Mariantony
      Benu Mariantony

      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

      Author's profile photo Former Member
      Former Member

      Hi Benu, We dont have solution manager configured, Is not there any standard notification available in HANA XS for job failure?

       

      Siva

      Author's profile photo Kanimeni chandra_sekhar
      Kanimeni chandra_sekhar

      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

       

      Author's profile photo Benu Mariantony
      Benu Mariantony

      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