Alerts in SAP HANA Database and Data Lake

In this step, the SAP HANA cockpit will be used to examine three alert definitions.

1. Open Alerts from HANA Cloud Central.

   

   SAP HANA Cloud Central has an alerts app to view triggered alerts. It displays alerts for all instances in a subaccount and additionally displays additional alerts that do not originate from the SAP HANA embedded statistics server (ESS). Events corresponding to ESS alerts have an alert ID.

2. The three alerts that will be triggered in step 2 of this tutorial are HDBCSTableRecordCount, HDBLongRunningStatement, and HDBTestAlert.

   You can find these alerts by selecting Configure Alert Rules.

   

   Then select an instance and search for the alerts.

   

   Notice that alerts have a name, description, and categories.

   The categories are shown below.

   SAP HANA Database Categories
   Schedule
   Interval
   Threshold

3. Alerts may have threshold levels.
   As an example, long-running statements, has its threshold values set to 60 minutes for Warning, 45 minutes for Notice, and 30 minutes for Information. These will be set to much lower thresholds of 3, 2 and 1 minutes in step 2.

   

4. Alert checks are scheduled to run at a specified interval and can be enabled or disabled. For example, record count of non-partitioned column-store tables (HDBCSTableRecordCount) has an interval value of 1 hour, meaning that this check is performed each hour and is currently enabled.

   

5. Alerts also have a retention period. Once triggered, depending on their type, they will remain for a set duration such as 14 or 42 days.

For additional details, consult Alerts in SAP HANA Cloud in the administration guide for SAP HANA cockpit.

The following instructions demonstrate a few examples of triggering alerts in a SAP HANA database.

Perform these actions on a non-production system, such as a trial or free tier account.

1. Open the SQL console.

   

2. Execute the following query in the SQL console to trigger a high (indicated by the parameter value of 4) severity test alert.

   SQL

   Copy

   CALL _SYS_STATISTICS.Trigger_Test_Alert(?, 4, 'High test alert');
   

   

   The alert will be viewed in the alerts app in step 3.

3. Trigger the long-running statements alert.

   Click Configure Alert Rules and select your instance. Then, search for the Long-running statements alert (HDBLongRunningStatement) in search bar.

   Select Edit and set the thresholds to 180, 120, and 60 seconds. The interval time set to 5 minutes by default.

   

   Execute the following SQL.

   SQL

   Copy

   DO BEGIN 
   USING SQLSCRIPT_SYNC AS SYNCLIB;
   CALL SYNCLIB:SLEEP_SECONDS( 300 );  --the SQL runs for 5 minutes
   -- Now execute a query
   SELECT * FROM M_TABLES;
   END;
   

4. The record count of non-partitioned column-store tables alert can be triggered by executing the following SQL.

   SQL

   Copy

   --The default threshold for 'Record count of non-partitioned column-store tables' is 300 million
   -- This SQL may take a minute or two to run
   -- Create a table and insert more than 300 million rows into it
   DO BEGIN
          DECLARE i INT;
          CREATE TABLE MYTABLE(MYVALUE INT);
          INSERT INTO MYTABLE VALUES(1);
          INSERT INTO MYTABLE VALUES(2);
          INSERT INTO MYTABLE VALUES(3);
          INSERT INTO MYTABLE VALUES(4);
          INSERT INTO MYTABLE VALUES(5);
          FOR i IN 1 .. 26 DO
                 INSERT INTO MYTABLE (SELECT * FROM MYTABLE);
          END FOR;
          SELECT COUNT(*) FROM MYTABLE;
   END;
   SELECT TOP 100 * FROM MYTABLE;
   
   -- To resolve, partition the table
   -- ALTER TABLE MYTABLE PARTITION BY HASH(MYVALUE) PARTITIONS 5;
   
   -- Clean up
   -- DROP TABLE MYTABLE;
   

   Note that two other alerts may also be triggered by the above SQL: table growth of non-partitioned column-store tables and record count of column-store table partitions.

The following instructions will show how to view a triggered SAP HANA database alert in SAP HANA Cloud Central.

1. In SAP HANA Cloud Central, alerts can be seen for all instances in an SAP BTP subaccount. Navigate to the alerts tab.

   

   A filter can be set for Type, Severity, Instance, Instance Type, and Time Range.

   

   Alerts can be filtered by Current or All. Current Alerts have a Start Time value but no End Time value, as the end time is added when the alert is closed. Closed alerts appear in type filter All rather than Current.

   Additional details can be found in Open Alerts in SAP HANA Cloud Central.

2. Details about a SQL statement from a long-running statement alert can be found out with the following query. The statement hash can be found in the alert description.

   SQL

   Copy

   SELECT STATEMENT_STRING
     FROM M_SQL_PLAN_CACHE 
     WHERE STATEMENT_HASH='XXXXXXXXXXXXXXXXXXXXX';
   

   

   You can find the statement hash here:

   

3. The test alert will resolve itself after 5 minutes or be resolved (indicated by the parameter value of 0) by executing the following statement.

   SQL

   Copy

   CALL _SYS_STATISTICS.Trigger_Test_Alert(?, 0, 'Resolve test alert');
   

   Additional details on the test alert are available at SAP Note 3004477 - Usage of statistics server test alert (ID 999).

Alerts can also be accessed via a REST API as shown at Accessing SAP HANA Cloud Alerts and Metrics using a REST API.

The following instructions show one example of triggering the data lake locked user event. The alert will be triggered when a user attempts to log in after the user account has been locked because an incorrect password has been provided too many times.

This alert is not available in trial accounts.

1. In a SQL console that is connected to a data lake, execute the following SQL to create a login policy and a new user.

   SQL

   Copy

   CREATE LOGIN POLICY lp max_failed_login_attempts=3;
   GRANT CONNECT TO user2 IDENTIFIED BY 'Password2';
   GRANT SELECT ANY TABLE TO user2;
   GRANT SET ANY CUSTOMER PUBLIC OPTION to user2;
   ALTER USER user2 LOGIN POLICY lp;
   

   Additional details can be found at Login Policy Options.

2. The instructions below will attempt to connect to the data lake using user2 but with an incorrect password.

   • Click on the instance, select the data lake instance, uncheck Use cached credentials if possible.

     

   • Enter user2 and an incorrect password.

     

   • After pressing OK, an attempt will be made to connect to the data lake. After three failed attempts with an incorrect password, user2 will become locked. This can be seen in the User & Role Management app in SAP HANA Cloud Central.

     

3. Attempt to connect one more time after the account has been locked (4th time) to trigger the alert.

4. The alert can now be seen in the alerts app in SAP HANA Cloud Central.

   

5. Additional details about users can be seen by calling the procedure sa_get_user_status. The user can be unlocked using by resetting the login policy.

   SQL

   Copy

   CALL sa_get_user_status;
   ALTER USER user2 RESET LOGIN POLICY;
   --DROP USER user2;
   --DROP LOGIN POLICY lp;
   

   The tutorial Monitor a Standalone Data Lake in SAP HANA Cloud may also be of interest as it demonstrates the data lake Relational Engine monitoring views.

The SAP Business Technology Platform (BTP) includes a service called the SAP Alert Notification service for SAP BTP (ANS) that provides a common way for other services or applications running in the SAP BTP to send out notifications such as an email, a post to a Microsoft Teams or Slack channel, the creation of a ticket in ServiceNow, or a webhook to send events to any Internet REST endpoint. The SAP HANA Cloud database and data lake pass on events to the SAP ANS when an alert is triggered.

For an overview of the SAP Alert Notification service for SAP BTP and information about the different service plans including free tier, see SAP Alert Notification service for SAP BTP in the SAP Discovery Center and watch the associated video SAP Alert Notification service for SAP BTP - Overview.

In this step, the Alert Notification Service will be configured to act on the incoming notifications by sending an email with the details of the alert. First, an instance of the Alert Notification Service will be created. Then, two conditions will be created; one that matches notifications sent from a SAP HANA database and the other from a data lake. An email action will also be created that describes who to send an email to and what content to include in the email when one of the conditions occurs. Finally, a subscription will be created that will use the two conditions and the action. Having the conditions and actions separate from the subscription enables them to be reused in multiple subscriptions.

1. Create an instance of the Alert Notification service in the SAP BTP Cockpit.

   

   The SAP Alert Notification service must be in the same subaccount as the SAP HANA Cloud instances which it will be receiving notifications from.

   ---

   If the SAP Alert Notification service does not appear, it may be that the entitlement needs to be added to the subaccount. To do so, navigate to the subaccount, select Entitlements, Edit, Add Service Plans, and Alert Notification.

   

   

2. Select the Cloud Foundry runtime environment and provide a name for the Alert Notification service instance and press the Create button.

   

   Service	Plan	Runtime Environment	Instance Name
   Alert Notification	standard	‘Cloud Foundry’	ANS-1

3. Once the instance has been created, click on Manage Instance.

   

4. Create a condition for SAP HANA database alerts by selecting Conditions and then pressing Create.

   

5. Specify the condition details and press Create.

   

   Name	Condition
   HANA-DB-Alerts	eventType Starts With HDB

   Alternatively, a condition resource.resourceType set to equal hana-cloud-hdb could be used.

   ---

   Note that the Mandatory checkbox is left unchecked. This means that this condition can be ORed with one or more other non mandatory conditions.

   

6. Create another condition for data lake alerts. Specify the condition details and press Create.

   

   Name	Condition
   Data-Lake-Alerts	eventType Starts With Datalake

7. Create an action by selecting Actions and the pressing Create.

   

8. Specify the type of action to be Email.

   

   See also Managing Actions for details on other available action types.

9. Name the action email_action and then scroll to the Additional Properties section.

   

   In the Additional Properties section, provide values for Email Address, Subject Template and Payload Template.

   

    

   Subject Template

   Copy

   {severity} {resource.resourceType} {eventType} {tags.ans:status} occurred on {resource.resourceName}
   

   Payload Template

   Copy

   AlertID: {tags.hanaAlertRuleId}
   Body: {body}
   Recommended Action: {tags.ans:recommendedAction}
   Instance Details: {resource.tags.*}
   

   It is also possible to leave the subject and payload template fields empty. In this case, a default template will be used.

   Details on the available tags for each alert are provided at Built-In Events.

   

10. A confirmation token will be sent to the email address. Click on the provided link or copy that value and use it to confirm the action as shown in the next sub-step.

    

11. Click on email_action to open it.

    

    Press the Confirm Action button.

    

    Enter the confirmation token.

    

    The email action is now confirmed.

    

12. Create a subscription by selecting Subscriptions and pressing Create.

    

    Name the subscription ANS-HANA-Cloud-Subscription and press the Create button.

    

13. Assign the conditions to the subscription by including the two previously created conditions and pressing the Assign button.

    

    Another example is to specify the instance ID of a specific instance to monitor and to only send an alert when the alert first occurs (create state).

    

14. Assign the action to the subscription by including the previously created action and pressing the Assign button.

    

15. Completed subscription.

    

16. Repeating the actions in step 2 and 4 should now send emails like the ones shown below.

    Test Alert

    

    Test Alert (default template)

    

    Notice that the ans:status is CLOSE. Events can have a status of CREATE, UPDATE or CLOSE. Some alerts have multiple thresholds or severities so multiple CREATE and CLOSE alerts could be sent as a condition occurs and then is resolved. If an alert is unresolved after a period of time such as 4 hours, an alert with a status of update is sent.

    Long-Running Statement

    

    Record count of non-partitioned column-store tables

    

    Locked user

    

This step will briefly show an example of how to receive a notification in Microsoft Teams.

1. In Microsoft Teams, search for Incoming Webhook in Apps.

   

2. Choose to add the app to a team.

   

3. Specify a name for the webhook, optionally upload an image, and press Create.

   

   After pressing create, copy the provided URL and press Done.

   

   A notification will appear in the channel that the connector has been added.

   

4. Similar to the previous step where an ANS action was created for email, create one for Microsoft Teams in the SAP BTP Cockpit.

   

   Provide the webhook URL under the Additional Properties section.

   

5. Assign the action to a subscription.

   

6. After triggering an alert, the Microsoft Teams channel will show the notification.

   

This step will briefly show an example of how to receive a notification in Slack.

1. In a browser open api.slack.com, click on Create a custom app, and then choose from scratch.

   Provide an app name, a workspace, and press Create App.

   

2. Choose to add Incoming Webhooks.

   

3. After activating incoming webhooks, click on Add New Webhook to Workspace.

   Specify the Slack channel that will be used to post to and click Allow.

   

   Copy the provided Webhook URL.

4. Similar to the step where an ANS action was created for email, create one for Slack in the SAP BTP Cockpit.

   

   Provide the webhook URL under the Additional Properties section.

   

   An example of a payload template.

   JSON

   Copy

   [{
   	"type": "section",
   	"text": {
   		"type": "mrkdwn",
   		"text": "* {severity} {eventType} {tags.ans:status} on {resource.resourceName}*"
   	}
     	},
     	{
     		"type": "divider"
     	},
     	{
     		"type": "section",
     		"text": {
     			"type": "mrkdwn",
     			"text": ":pencil2: Subject: {subject}"
     		}
     	},
     	{
     		"type": "section",
     		"text": {
     			"type": "mrkdwn",
     			"text": ":scroll: Body: {body}"
     		}
     	},
     	{
     		"type": "section",
     		"text": {
     			"type": "mrkdwn",
     			"text": " :medical_symbol: Recommended Action: {tags.ans:recommendedAction}"
     		}
     	}]
   

5. Assign the action to a subscription.

   

6. After triggering an alert, the Slack channel will show the notification.

   

Congratulations! You have now configured alerts, triggered and viewed them via the SAP HANA cockpit and received them via email, Microsoft Teams, and Slack using the SAP Alert Notification service for SAP BTP.