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
- SAP Managed Tags:
2 Comments
