Background
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
◉ SAP HANA Service
◉ Node.js
◉ HANA database driver for Node.js
◉ SAP Cloud Platform Alert Notification
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.
Prerequirements
◉ SAP HANA Service instance in CF environment.
◉ Install the Cloud Foundry Command Line Interface (CLI)
◉ Enabled Alert Notification.
◉ Install Node.js on your PC
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.
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.
No comments:
Post a Comment