Skip to Content
Technical Articles

Monitoring HANA Service: Custom Email Alerting

Background of this article

There are several options to monitor the SAP Cloud Platform HANA Service.

  1. Web interface (SAP HANA Cockpit)
  2. SQL (SELECT on _SYS_STATISTICS)
  3. Combine SQL and scripts (Main subject)

However, there is no e-mail notification in the 1st and 2nd options. How do you notice system failure without notification? Do you want to monitor in front of your computer for 24 hours???

I think we need automated notification. (that’s 3rd option)

1st option (SAP HANA Cockpit)

SAP HANA Cockpit has an essential role in the Administration of SAP HANA. It is used for monitoring and managing checks such as:

  • Monitor overall database health
  • Monitor status and resource usage of individual database services
  • Analyze database performance across a range of key performance indicators related to memory, disk, and CPU usage
  • Monitor alerts occurring in the database and analyze patterns of occurrence
  • Configure the alerting mechanism, for example, change alert threshold values, switch alert checkers on/off, and check for alerts out of schedule

By using HANA Cockpit, we can check the database status, alerts, memory usage via web-based interface.

We can change the warning threshold value in cockpit view.

However, the alert is only displayed on Cockpit’s screen, and it does not notify outside.

 

2nd Option (SQL)

Embedded Statistics Service Views are part of the internal monitoring infrastructure of the SAP HANA database.

For example, if you refer to STATISTICS_CURRENT_ALERTS view, you can check the alerts that are currently happening.

SELECT ALERT_ID,INDEX,ALERT_NAME,ALERT_RATING,ALERT_TIMESTAMP FROM "_SYS_STATISTICS"."STATISTICS_CURRENT_ALERTS" WHERE ALERT_RATING > 2;

 

3rd Option (Combine SQL and scripts)

If you want automatic email notification, you need to create a script and combine Statistics Service Views SQL.

Tools and services


Appendix: About SAP Cloud Platform Alert Notification

Alert Notification offers platform alerts and notifications for resources in the SAP Cloud Platform and gives you the possibility to create custom alerts for these resources.
This GA released at June 2019.
See this article for an overview of custom alerts.


Prerequirements

Outline of the procedure

  1. Configure the Alert Notification.
  2. Create a Node.js project.
  3. Run the app locally.
  4. Deploy the app to CF.

 

1.Configure the Alert Notification.

Please refer the my previous article.

2.Create a Node.js project.

Open Command Prompt and move to your workspace directory. In my case, the workspace is “D:\html5”.

cd /d D:\html5

Make project folder. In this time, I named “hanamonitoring”.

mkdir hanamonitoring
cd hanamonitoring

Create package.json file like below.

{
  "name": "hanamonitoring",
  "version": "0.0.1",
  "private": true,
  "scripts": {
    "start": "node app.js"
  },
  "dependencies": {
    "@sap/hana-client": "*",
    "node-schedule": "*",
    "request": "*"
  }
}

Then install dependencies.

npm install

Create app.js file like below.
Please change <YOUR_PORT>  place to your HANA DB port number.
Please change <YOUR_DB_PASSWORD> place to your HANA DB SYSTEM user password.
Please change <YOUR_BASIC_USER>  place to your basic auth user id.
Please change <YOUR_BASIC_PASS> place to your basic auth password.

■app.js

var schedule = require('node-schedule');
var hana = require('@sap/hana-client');
var conn = hana.createConnection();
var request = require('request');

var conn_params = {
    serverNode  : "zeus.hana.prod.eu-central-1.whitney.dbaas.ondemand.com:<YOUR_PORT_NUMBER>", // Change here.
    encrypt     : true,
    schema      : "_SYS_STATISTICS",
    uid         : "SYSTEM",
    pwd     : "<YOUR_DB_PASSWORD>" // Change here.
  };

var job = schedule.scheduleJob({
  second:  0 // Run as every minites.
}, function (fireTime) {

  conn.connect(conn_params, function(err) {
    if (err) {
      console.log("DB Error: DB Connection --- ", err);
      var msg = [{msg: "DB Error: DB Connection"}];
      res.json({searchResult: msg});
      return;
    }

    var sql = 'SELECT ALERT_ID,INDEX,ALERT_NAME,ALERT_RATING,ALERT_TIMESTAMP FROM "_SYS_STATISTICS"."STATISTICS_CURRENT_ALERTS" WHERE ALERT_RATING > 2;';
	// Run SQL Query 
	conn.exec(sql, function(err, result) {
      if (err) {
        console.log("DB Error: SQL Execution --- ", err);
      }

      conn.disconnect();
      console.log(result);

      if (result.length == 0){
        // do nothing
      }
      else{
        // send mail
		var options = {
		  url: 'https://clm-sl-ans-live-ans-service-api.cfapps.eu10.hana.ondemand.com/cf/producer/v1/resource-events', // Endpoint URL + "/cf/producer/v1/resource-events"
		  method: 'POST',
		  auth: {
		    user: "<YOUR_BASIC_USER>", // Change here.
		    password: "<YOUR_BASIC_PASS>"  // Change here.
		  },
		  json: {
				  "eventType": "mycustomevent",
				  "resource": {
				    "resourceName": "Your Node.js App.",
				    "resourceType": "app",
				    "tags": {
				      "env": "HANA Service develop environment"
				    }
				  },
				  "severity": "FATAL",
				  "category": "ALERT",
				  "subject": "HANA Service Alert occured",
				  "body": result.length+" alerts occuring!!\n" + JSON.stringify(result)
			}
		}

		// Send Alert request.
		request(options, function (error, response, body) {
			//console.log(response.body);

			console.log('Send E-mail Notification.');
		});

      }
    });
  });
});

3.Run the app locally.

Run the application by

npm start

You will receive an email from Alert Notification.

Next, we’ll deploy this app to your CF environment and run on cloud.

 

4.Deploy the app to CF.

Using CF CLI, login and deploy the app into Cloud Foundly.

cf api https://api.cf.eu10.hana.ondemand.com
cf login

# Deploy command.
cf push hanamonitoring
Pushing app hanamonitoring to org XXXXXXXX.XXXXXXXX / space dev as unoys@qunie.com...
Getting app info...
Updating app with these attributes...
  name:                hanamonitoring
  path:                D:\html5\hanamonitoring
  routes:
    hanamonitoring.cfapps.eu10.hana.ondemand.com

Updating app hanamonitoring...
Mapping routes...
Comparing local files to remote cache...
Packaging files to upload...
Uploading files...
 1.50 MiB / 1.50 MiB [=====================================================================================] 100.00% 6s

Waiting for API to complete processing files...

Stopping app...

Staging app and tracing logs...
   Downloaded app package (1.8M)

Now it will works on your CF space.

 

3 Comments
You must be Logged on to comment or reply to a post.