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