Skip to Content
Technical Articles

ABAP Tool for receiving automated e-mails for SAP HANA Alerts

Introduction:

You want to receive automated e-mails for the currently active SAP HANA Alerts. These e-mails should be triggered by an ABAP program and the SAP Job scheduler (transaction SM36).

For this purpose, we have developed a little standalone ABAP program with the name RSHDB_SEND_ALERTS_BY_MAIL. This program sends automated emails with the currently active SAP HANA Alerts. Additionally, the program allows to restrict the alerts by the Rating and the Alert ID.

Example 1: You can tell the program that you want to receive e-mails only for alerts with rating Error/High/Medium, i.e., no e-mail should be sent for alerts with rating Low/Info.

Example 2: You could tell the program that you want to receive e-mails only for alerts which are related to CPU Usage.

Description of the ABAP program RSHDB_SEND_ALERTS_BY_MAIL:

First you must create a variant for this ABAP program via transaction SE38 and fill the selection fields of the program:

 

 

 

  • In the block “A” of the screenshot you must enter the name of the HANA as it is shown in transaction DBACOCKPIT. This field has a F4-Help, so you can simply pick up the HANA name via F4. You can choose either the local HANA of this SAP system or a remote HANA (in case you have integrated remote HANAs into DBACOCKPIT)
  • In the block “B” you can enter multiple e-mail recipients
  • In the block “C” you can specify the Ratings for which you wish to receive an e-mail. In the example of the above screenshot, alerts with rating Low/Info will be excluded from the e-mail
  • In the block “D” you can include or exclude specific Alert IDs from the e-mail. Example: If you want to receive e-mails only for alerts which are related to CPU Usage, enter the Alert ID = 5 in this field. You can find a list of all available Alert IDs in DBACOCKPIT –> Current Status –> Alerts

After you have saved your variant, the next step is to schedule a periodic job via transaction SM36. In the tab “Step” of SM36 specify program name RSHDB_SEND_ALERTS_BY_MAIL and the variant which you have previously created. In the tab “Start Condition” choose a suitable Date/Time and mark the Checkbox “Periodic Job”. A suitable period would be “hourly”. But if you wish, you can use a higher frequency such as e.g. “every 10 minutes”. Since the runtime of this job is just a few milliseconds, a high frequency for the job execution is not a problem.

 

Availability of the ABAP program RSHDB_SEND_ALERTS_BY_MAIL:

It is shipped with the following SAP_BASIS Support Packages:

  • 7.55 and higher: All SPs
  • 7.54 SP1
  • 7.53 SP4
  • 7.52 SP6
  • 7.51 SP10
  • 7.50 SP17
  • 7.40 SP24
  • 7.31 SP27
  • 7.30 SP21
  • 7.02 SP23

 

If you want to use this tool in older SAP_BASIS Support Packages: You can simply create this program in the customer name range, i.e., with a program name which begins with the letter Z or Y. The coding for your program Z* is attached to this article.

 

REPORT rshdb_send_alerts_by_mail LINE-SIZE 250.
TABLES: hdb_alert_current, db6navsyst, dba_config.

SELECTION-SCREEN BEGIN OF BLOCK syst WITH FRAME TITLE title_1.
  SELECTION-SCREEN BEGIN OF LINE.
    PARAMETERS: sysid TYPE sysysid DEFAULT sy-sysid MATCHCODE OBJECT sdb_navsyst OBLIGATORY.
  SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN END OF BLOCK syst.

SELECTION-SCREEN SKIP.

SELECTION-SCREEN BEGIN OF BLOCK rec WITH FRAME TITLE title_2.
  SELECTION-SCREEN BEGIN OF LINE.
    SELECT-OPTIONS: send_to FOR dba_config-value LOWER CASE NO INTERVALS.
  SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN END OF BLOCK rec.

SELECTION-SCREEN SKIP.

SELECTION-SCREEN BEGIN OF BLOCK ausw WITH FRAME TITLE title_3.
  SELECTION-SCREEN BEGIN OF LINE.
    SELECTION-SCREEN COMMENT 2(30) comm3.
    PARAMETERS: S_err AS CHECKBOX DEFAULT 'X'.
  SELECTION-SCREEN END OF LINE.

  SELECTION-SCREEN BEGIN OF LINE.
    SELECTION-SCREEN COMMENT 2(30) comm4.
    PARAMETERS: S_high AS CHECKBOX DEFAULT 'X'.
  SELECTION-SCREEN END OF LINE.

  SELECTION-SCREEN BEGIN OF LINE.
    SELECTION-SCREEN COMMENT 2(30) comm5.
    PARAMETERS: S_med AS CHECKBOX DEFAULT 'X'.
  SELECTION-SCREEN END OF LINE.

  SELECTION-SCREEN BEGIN OF LINE.
    SELECTION-SCREEN COMMENT 2(30) comm6.
    PARAMETERS: S_low AS CHECKBOX.
  SELECTION-SCREEN END OF LINE.

  SELECTION-SCREEN BEGIN OF LINE.
    SELECTION-SCREEN COMMENT 2(30) comm7.
    PARAMETERS: S_info AS CHECKBOX.
  SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN END OF BLOCK ausw.

SELECTION-SCREEN BEGIN OF BLOCK ausw2 WITH FRAME TITLE title_4.
  SELECTION-SCREEN BEGIN OF LINE.
    SELECT-OPTIONS: alert_id FOR hdb_alert_current-alert_id.
  SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN END OF BLOCK ausw2.

RANGES: range_rating FOR hdb_alert_current-alert_rating.
TYPES: string_tab  TYPE STANDARD TABLE OF string.
CONSTANTS: co_raw  TYPE c LENGTH 3 VALUE 'RAW'.
DATA: sys_ref              TYPE REF TO cl_db6_sys,
      rdi                  TYPE REF TO cl_dba_rdi,
      root_excp            TYPE REF TO cx_dba_root,
      adbc                 TYPE REF TO cl_hdb_adbc,
      itab                 TYPE hdb_alert_current_tab,
      lt_hdb_alert_current TYPE	hdb_alert_current_tab,
      wa                   TYPE hdb_alert_current,
      wa_alert_id          TYPE string,
      rating               TYPE string,
      wa_db6navsyst        TYPE db6navsyst,
      lr_sql_exc           TYPE REF TO cx_sql_exception.
DATA: title            TYPE so_obj_des,
      mail_body        TYPE bcsy_text,
      addressen        TYPE string_tab,
      ls_address       TYPE string,
      lr_send_request  TYPE REF TO cl_bcs,
      lr_sender        TYPE REF TO cl_sapuser_bcs,
      lv_email         TYPE adr6-smtp_addr,
      lr_recipient     TYPE REF TO if_recipient_bcs,
      lr_bcs_exception TYPE REF TO cx_bcs,
      lr_adr_exception TYPE REF TO cx_address_bcs,
      lr_document      TYPE REF TO cl_document_bcs,
      wa_soli          TYPE soli.

*----------------------------------------------------
INITIALIZATION.
*----------------------------------------------------
  title_1 = 'Fetch Alerts for this HANA'.                   "#EC NOTEXT
  title_2 = 'Mail Recipients'.                              "#EC NOTEXT
  title_3 = 'Restriction for the Rating'.                   "#EC NOTEXT
  title_4 = 'Restriction for the Alert ID'.                 "#EC NOTEXT
  comm3 = 'Alerts with Rating "Error"'.                     "#EC NOTEXT
  comm4 = 'Alerts with Rating "High"'.                      "#EC NOTEXT
  comm5 = 'Alerts with Rating "Medium"'.                    "#EC NOTEXT
  comm6 = 'Alerts with Rating "Low"'.                       "#EC NOTEXT
  comm7 = 'Alerts with Rating "Info"'.                      "#EC NOTEXT

*----------------------------------------------------
AT SELECTION-SCREEN.
*----------------------------------------------------
  SELECT SINGLE * FROM db6navsyst INTO wa_db6navsyst WHERE sysid = sysid. "#EC CI_ALL_FIELDS_NEEDED
  IF sy-subrc NE 0.
    MESSAGE e001(sada) WITH sysid 'is not integrated in DBACOCKPIT'. "#EC NOTEXT
  ELSEIF wa_db6navsyst-dbsys NE 'HDB'.
    MESSAGE e001(sada) WITH sysid 'is not a HANA'. "#EC NOTEXT
  ENDIF.

*----------------------------------------------------
* Fetch the Alerts
*----------------------------------------------------
START-OF-SELECTION.
  TRY.
      sys_ref = cl_db6_sys=>get_sys_ref( sysid ).
      rdi = cl_dba_rdi=>get_instance( sys_ref ).
      rdi->query->reset( ).
      rdi->query->get_snapshot( EXPORTING ddic_src = cl_hdb_rdi_meta=>co_ddic_current_alerts
                                IMPORTING data = itab ).
      IF sys_ref IS BOUND AND sys_ref->is_r3_system( ) = abap_true AND sys_ref->sys_data-saprel GE 740.
        CALL FUNCTION 'GET_LCAALERTS_SDB'
          EXPORTING
            iv_con_name          = sys_ref->sys_data-dbcname
          IMPORTING
            et_hdb_alert_current = lt_hdb_alert_current
          EXCEPTIONS
            OTHERS               = 0.
      ENDIF.
      LOOP AT lt_hdb_alert_current INTO wa.
        APPEND wa TO itab.
      ENDLOOP.
    CATCH cx_dba_root INTO root_excp.
      IF root_excp IS BOUND AND root_excp->previous IS BOUND.
        TRY.
            lr_sql_exc ?= root_excp->previous.
          CATCH cx_sy_move_cast_error.
            IF root_excp->previous->previous IS BOUND.
              TRY.
                  lr_sql_exc ?= root_excp->previous->previous.
                CATCH cx_sy_move_cast_error.
              ENDTRY.
            ENDIF.
        ENDTRY.
        IF lr_sql_exc IS BOUND.
          IF lr_sql_exc->sql_code = 258.
            wa_soli = 'Missing SELECT privilege for schema _SYS_STATISTICS'. "#EC NOTEXT
          ELSE.
            wa_soli = lr_sql_exc->get_text( ).
            IF wa_soli IS INITIAL.
              wa_soli = 'System is not reachable'.          "#EC NOTEXT
            ENDIF.
          ENDIF.
        ELSE.
          wa_soli = 'System is not reachable'.              "#EC NOTEXT
        ENDIF.
      ELSE.
        wa_soli = 'System is not reachable'.                "#EC NOTEXT
      ENDIF.
      FORMAT COLOR = 6.
      WRITE: wa_soli.
      MESSAGE s232(sada) WITH wa_soli.
      APPEND wa_soli TO mail_body.
  ENDTRY.

*----------------------------------------------------
* Format the output
*----------------------------------------------------
  IF S_err = 'X'.
    range_rating-low = 5.
    range_rating-sign = 'I'.
    range_rating-option = 'GE'.
    APPEND range_rating.
  ENDIF.
  IF S_high = 'X'.
    range_rating-low = 4.
    range_rating-sign = 'I'.
    range_rating-option = 'EQ'.
    APPEND range_rating.
  ENDIF.
  IF s_med = 'X'.
    range_rating-low = 3.
    range_rating-sign = 'I'.
    range_rating-option = 'EQ'.
    APPEND range_rating.
  ENDIF.
  IF s_low = 'X'.
    range_rating-low = 2.
    range_rating-sign = 'I'.
    range_rating-option = 'EQ'.
    APPEND range_rating.
  ENDIF.
  IF s_info = 'X'.
    range_rating-low = 1.
    range_rating-sign = 'I'.
    range_rating-option = 'EQ'.
    APPEND range_rating.
  ENDIF.

  SORT itab BY snapshot_id.
  LOOP AT itab INTO wa WHERE alert_rating IN range_rating AND alert_id IN alert_id.
    IF wa-alert_rating GE 5.
      wa-rating_text = 'Error'.                             "#EC NOTEXT
      wa-check_icon = cl_hdb_utilities=>get_icon( icon_name = 'ICON_MESSAGE_ERROR_SMALL' text = wa-rating_text ).
    ELSEIF wa-alert_rating EQ 4.
      wa-rating_text = 'High'.                              "#EC NOTEXT
      wa-check_icon = cl_hdb_utilities=>get_icon( icon_name = 'ICON_LED_RED' text = wa-rating_text ).
    ELSEIF wa-alert_rating EQ 3.
      wa-rating_text = 'Medium'.                            "#EC NOTEXT
      wa-check_icon = cl_hdb_utilities=>get_icon( icon_name = 'ICON_LED_YELLOW' text = wa-rating_text  ).
    ELSEIF wa-alert_rating EQ 2.
      wa-rating_text = 'Low'.                               "#EC NOTEXT
      wa-check_icon = cl_hdb_utilities=>get_icon( icon_name = 'ICON_LED_YELLOW' text = wa-rating_text  ).
    ELSEIF wa-alert_rating EQ 1.
      wa-rating_text = 'Info'.                              "#EC NOTEXT
      wa-check_icon = cl_hdb_utilities=>get_icon( icon_name = 'ICON_INFORMATION' text = wa-rating_text ).
    ENDIF.
*
    wa_alert_id = wa-alert_id. CONDENSE wa_alert_id.
    CONCATENATE 'Alert ID:'  wa_alert_id wa-alert_name INTO wa_alert_id SEPARATED BY space.    "#EC NOTEXT
    CONCATENATE 'Rating:' wa-rating_text INTO rating SEPARATED BY space.                       "#EC NOTEXT
*
    WRITE: / wa-snapshot_id,
           / wa_alert_id,
           / wa-check_icon,
           / wa-alert_description,
           / wa-alert_details,
           / wa-alert_useraction.
    ULINE.
*
    WRITE wa-snapshot_id TO wa_soli. APPEND wa_soli TO mail_body.
    wa_soli = wa_alert_id.           APPEND wa_soli TO mail_body.
    wa_soli = rating.                APPEND wa_soli TO mail_body.
    wa_soli = wa-alert_description.  APPEND wa_soli TO mail_body.
    wa_soli = wa-alert_details.      APPEND wa_soli TO mail_body.
    wa_soli = wa-alert_useraction.   APPEND wa_soli TO mail_body.
    wa_soli = '-------------------------------------------------------------'.  APPEND wa_soli TO mail_body.
  ENDLOOP.
  IF sy-subrc NE 0.
    wa_soli = 'No alerts found'.                            "#EC NOTEXT
    WRITE: wa_soli.
    MESSAGE s232(sada) WITH wa_soli.
    RETURN.
  ENDIF.

*----------------------------------------------------
* SEND MAIL
*----------------------------------------------------
  IF send_to IS INITIAL.
    wa_soli = 'Mail was not sent (Recipients missing)'.     "#EC NOTEXT
    WRITE: wa_soli.
    MESSAGE s232(sada) WITH wa_soli.
    RETURN.
  ENDIF.

  LOOP AT send_to.
    APPEND send_to-low TO addressen.
  ENDLOOP.
  CONCATENATE 'Alerts for' sysid INTO title SEPARATED BY space. "#EC NOTEXT

  TRY .
*     Create send request
      lr_send_request = cl_bcs=>create_persistent( ).

*     Email FROM...
      lr_sender = cl_sapuser_bcs=>create( sy-uname ).

*     Email TO...
      LOOP AT addressen INTO ls_address.
        CONDENSE ls_address.
        lv_email = ls_address.
        lr_recipient = cl_cam_address_bcs=>create_internet_address( lv_email ).

*       Add recipient to send request
        CALL METHOD lr_send_request->add_recipient
          EXPORTING
            i_recipient = lr_recipient
            i_express   = 'X'.
      ENDLOOP.

      lr_document = cl_document_bcs=>create_document(
                      i_type     = co_raw
                      i_text     = mail_body
                      i_language = sy-langu
                      i_subject  = title ).

*     Add document to send request
      lr_send_request->set_document( lr_document ).

*     No mail delivery report to sender
      lr_send_request->set_status_attributes( i_requested_status = 'E'
                                              i_status_mail = 'N' ).

      lr_send_request->set_send_immediately( abap_true ).

*     Send email
      lr_send_request->send( ).

      wa_soli = 'Mail was sent'.                            "#EC NOTEXT
      WRITE: wa_soli.
      MESSAGE s232(sada) WITH wa_soli.

*     Commit to send email
      COMMIT WORK.

    CATCH cx_address_bcs INTO lr_adr_exception.
      FORMAT COLOR = 6.
      wa_soli = 'Mail could not be sent'.                   "#EC NOTEXT
      WRITE: wa_soli.
      MESSAGE s232(sada) WITH wa_soli.
      wa_soli = lr_bcs_exception->get_text( ).
      IF wa_soli IS NOT INITIAL.
        WRITE: wa_soli.
        MESSAGE s232(sada) WITH wa_soli.
      ELSE.
        wa_soli = lr_bcs_exception->get_longtext( ).
        IF wa_soli IS NOT INITIAL.
          WRITE: wa_soli.
          MESSAGE s232(sada) WITH wa_soli.
        ENDIF.
      ENDIF.

    CATCH cx_bcs INTO lr_bcs_exception.
      FORMAT COLOR = 6.
      wa_soli = 'Mail could not be sent'.                   "#EC NOTEXT
      WRITE: wa_soli.
      MESSAGE s232(sada) WITH wa_soli.
      wa_soli = lr_bcs_exception->get_text( ).
      IF wa_soli IS NOT INITIAL.
        WRITE: wa_soli.
        MESSAGE s232(sada) WITH wa_soli.
      ELSE.
        wa_soli = lr_bcs_exception->get_longtext( ).
        IF wa_soli IS NOT INITIAL.
          WRITE: wa_soli.
          MESSAGE s232(sada) WITH wa_soli.
        ENDIF.
      ENDIF.
  ENDTRY.

Conclusion:

With the described new ABAP program you can set up receiving automated e-mails which meet specific conditions.  I hope you like this little ABAP program

 

/
2 Comments
You must be Logged on to comment or reply to a post.
  • Thanks for sharing! I have quite a few questions though.

    The blog title says “SAP HANA Alerts” but then I noticed that the ABAP code was downported to 7.02 and I suspect this is not an accurate title.

    “this HANA” is also a very confusing choice of terminology. Based on the ABAP code, this is simply SAP system ID (= 3-character ID that we see when logging in). The code compiles in our EHP6 which obviously has nothing to do with HANA.

    Looking further at the code, it’s calling FM GET_LCAALERTS_SDB, which has a description (in English!) as “Auslesen der liveCache application alerts (lcaalerts)”. I’m not a Basis person but a quick Google search shows that liveCache is definitely not just for HANA. It would be nice to include more information on what exactly these alerts are.

    Personally, I really appreciate the code shared here and not having to wait for the support pack, so don’t get me wrong. But I have (and I’m afraid) to ask: is this the actual ABAP code SAP is shipping to the customers? I understand this is just a small utility program but still, things like TABLES or “LOOP AT itab INTO wa” would not pass a code review even in our system. And what we code is never seen outside of our department.

    Thank you.

    • Hello Jelena,

      This is the preliminary ABAP code from the program which SAP will ship to customers. The final ABAP code will have some smaller changes such as e.g. language dependent text elements. But the functionality between the attached preliminary version and the final version will be 100% identical. Since it will take about ½ year before the final version will be available via Support Package Upgrade, I decided to share the preliminary version so that interested persons do not need to wait ½ year.

      Regarding your comment “Based on the ABAP code, this is simply SAP system ID”: This is not correct. SYSID can be either the SAP system ID or a remote HANA which was integrated into DBACOCKPIT. So basically, you can run RSHDB_SEND_ALERTS_BY_MAIL for 20 different HANAs in case that 20 different HANAs have been integrated into DBACOCKPIT.

      Regarding your comment on Release 7.02: This is one of the SAP_BASIS Releases used by Solution Manager. A lot of Solution Manager customers integrate all HANA databases from their system landscape into the DBACOCKPIT of their Solution Manager system. Those customers can run RSHDB_SEND_ALERTS_BY_MAIL inside their Solution Manager system for each one of those HANA databases by entering the DBACOCKPIT name of these HANAs into the selection screen of my report. Example: If the Solution Manager system is an ORACLE system and you have integrated 20 HANAs into DBACOCKPIT, then you can run the program for those 20 HANAs but of course not for the SAP System ID (because this one is an ORACLE database).

      Regarding your comment ON FM “GET_LCAALERTS_SDB”: My report RSHDB_SEND_ALERTS_BY_MAIL reads two types of Alerts:

      1. The (normal) HANA Alerts which are returned by the HANA Statistic Server. This is done by the method GET_SNAPSHOT which is called in my program. This method basically executes the following query: SELECT * FROM _SYS_STATISTICS.STATISTICS_CURRENT_ALERTS
      2. The HANA liveCache Alerts. Those Alerts are not returned by the Statistic Server and therefore I fetch them by executing GET_LCAALERTS_SDB. Remark: If the liveCache of the selected HANA is not used and/or if the Plugin LCAPPS has not been installed, then this FM will never return anything

      Normally the program will only output the HANA Alerts of category 1). The alerts of category 2) are only interesting for a few customers who have installed the LCAPPS Plugin. FYI: When you call transaction DBACOCKPIT and go to the screen Current Status –> Overview, have a look at the field “Installed Plugins”. If LCAPPS does not appear in this field, then GET_LCAALERTS_SDB will never return anything and therefore, my report only will return the “normal” HANA Alerts of category 1)