Sap hana database

Plugin to monitor a sap hana database, using custom queries.

Introduction

Ver. 28-06-2022

Plugin to monitor a SAP Hana database, using custom queries.

Type: Server plug-in

Compatibility matrix

 

Systems tested

CentOS 7

Systems where it should work Any linux system

 

SAP version tested on

2.00.059 

Systems where it should work

Any version

Pre requisites

 

Parameters

Parámetro Descripción
--ip IP or host of the database (required)
--user SAP Hana user name(required)
--password SAP Hana password (required)
--port Database port. For tenant HANA databases, use port number 3**NNN**13 (where NN is the SAP instance number, for example, 30013).
For HANA system databases on a multitenant system, the port number is 3**NNN**13.
For HANA system databases on single-tenant, the port number is 3**NNN**15.
--tentacle_port Tentacle port Default 41121 (optional)
--tentacle_address Tentacle server Ip to send data to (optional)
--agent_name To name the agent that will contain the modules of the custom queries, by default : "Sap_Hana" (optional).
--prefix_module To put a prefix to the modules in case you want to differentiate them from other executions (optional).
--conf Path of the conf file with the queries (required)
-g,--group Pandora FMS target group (optional)
--data_dir Pandora FMS data directory. By default it is /var/spool/pandora/data_in/ (optional)
--as_agent_plugin It is optional, if you want the plugin to be an agent plugin and put the modules in the pandora agent, execute this with a 1 (optional)

 

Pandora_hana.conf

In this file the customized SQL queries that will be executed in the SAP Hana database that will create modules in PandoraFMS will be introduced. 

The format to add the queries is the following:

nombre_modulo:query:typo:description

* The description is not mandatory, if not entered it will remain empty.

Example:

Backups_Size:SELECT SUM(allocated_page_size) FROM M_CONVERTER_STATISTICS;:generic_data:Estimated backup size
Timestamp:SELECT CURRENT_TIMESTAMP FROM DUMMY;:generic_data_string:Current timestamp
Hana_version:SELECT VERSION FROM M_DATABASE;:generic_data_string:version
Data_disk_information:SELECT HOST,PORT,FILE_NAME,STATE,SIZE,MAX_SIZE FROM M_DATA_VOLUMES:generic_data_string:Retrieve Hana disk info
Hana_service_statics:SELECT SERVICE_NAME, DATABASE_NAME, REQUESTS_PER_SEC, RESPONSE_TIME FROM SYS_DATABASES.M_SERVICE_STATISTICS:generic_data_string:Retrieve Hana Requests per second
Hana_cpu_time:SELECT HOST,PORT,SERVICE_NAME, SUM(CPU_TIME_SELF),SUM(CPU_TIME_CUMULATIVE) FROM M_SERVICE_THREADS GROUP BY HOST,PORT,SERVICE_NAME:generic_data_string:Retrieve Hana cpu usage
Hana_rs_fragmentation:SELECT HOST, PORT, TO_DECIMAL(SUM(FREE_SIZE) * 100 / SUM(ALLOCATED_SIZE),10,2), TO_DECIMAL( SUM(FREE_SIZE)/(1024*1024), 10, 2), TO_DECIMAL( SUM(ALLOCATED_SIZE)/(1024*1024), 10, 2) FROM M_RS_MEMORY WHERE CATEGORY = 'TABLE' OR CATEGORY = 'CATALOG'  GROUP BY HOST, PORT:generic_data_string:Retrieve Hana row store fragmentation
Hana_license:SELECT VALID, PERMANENT, PRODUCT_LIMIT, PRODUCT_USAGE FROM M_LICENSE;:generic_data_string:Retrieve license usage
Hana_IO_Stats:SELECT HOST,PORT,TYPE, TO_DECIMAL((TOTAL_READ_SIZE * 1.048576 / (TOTAL_READ_TIME+1)),10,2), TO_DECIMAL((TOTAL_WRITE_SIZE * 1.048576 / (TOTAL_WRITE_TIME+1)),10,2) FROM M_VOLUME_IO_TOTAL_STATISTICS:generic_data_string:Retrieve IO bandwidth
Hana_get_stated_connections:SELECT COUNT(*) FROM PUBLIC.M_CONNECTIONS WHERE CONNECTION_STATUS ='RUNNING':generic_data:retrieve connection status
Hana_Memory:SELECT HOST, round ( 100 * INSTANCE_TOTAL_MEMORY_USED_SIZE / INSTANCE_TOTAL_MEMORY_ALLOCATED_SIZE, 2), round (INSTANCE_TOTAL_MEMORY_ALLOCATED_SIZE / 1024/1024/1024, 2), round (INSTANCE_TOTAL_MEMORY_USED_SIZE / 1024/1024/1024, 2) from M_HOST_RESOURCE_UTILIZATION:generic_data_string:retrieve memory usages
Hana_Transactions:SELECT count(*) FROM M_BLOCKED_TRANSACTIONS;:generic_data:retrieve SAP transaction status
Check_norunning_backups:select * from SYS.M_BACKUP_CATALOG where STATE_NAME = 'running':generic_data_string:Check to  make sure there are no running backups still
Backup_id_running_backup:select BACKUP_ID from SYS.M_BACKUP_CATALOG where entry_type_name = 'complete data backup' and state_name = 'running'  order by sys_start_time desc:generic_data_string:Query to find BACKUP_ID of running data backup

Manual execution

With an execution with "as_agent_plugin" in 1 we will be able to see the xml by terminal.

./pandora_hana --ip <host o ip> --port <database port> --user <database user> --password <password database> --conf <conf path> --as_agent_plugin 1

image-1656435359935.PNG


Without the "as_agent_plugin" parameter, a 1 will be displayed to indicate the correct execution of the plugin:

./pandora_hana --ip <host o ip> --port <database port> --user <database user> --password <password database> --conf <path conf> --data_dir <path .data>

image-1656435538204.PNG

Configuration in PandoraFMS

Manual installation

Go to servers > plugins:

image-1629974405286.png

Click on add:

image-1629974430627.png

We put the name and description of your choice:

image-1656437269121.png

 

We enter as command the execution with the path of the plugin:

/path_pandora_hana

 

Remember that the recommended path for the use of the server plugins is: /usr/share/pandora_server/util/plugin/

image-1656437381803.png

And in plugin parameters we will introduce these followed by the macro "_field<N>_", the mandatory ones for the plugin to work are --ip,--port,--user,--password and --conf.

Although it is not mandatory, it is highly recommended the use of the --agent_name parameter, since it allows us to customize the name of the agent that will contain the modules created for each customized query. It is also advisable to use the --prefix_module parameter, because this will allow us to assign a prefix to the modules, which can be useful to recognize them faster and differentiate them if we create several executions for different databases.

--IP

image-1656437985204.png

--PORT

image-1656438071776.png

--USER

image-1656438378630.png

--PASSWORD

image-1656438434646.png

--CONF

image-1656438528074.png

--AGENT_NAME

image-1656438571290.png

 

Once this is done, we will click on "create".

Once this is done, the only thing left to do is to call it, so we will go to some agent's view and create an add-in module:

image-1646741530197.png

We will give it a name and in the section "plugin" we will put the one we have just configured.

image-1656438657656.png

 

Once this is done, click on create.

If the module is shown with 1, it means that it is running correctly. 

image-1656438714123.png

Modules generated by the plugin

The plugin will create an agent with the name that we have set with the parameter "--agent_name" if this parameter is not used, it will remain with "Sap Hana" by default:

image-1664371961325.png

In this agent, a module will be created for each customized query specified in pandora_hana.conf, with the name that we have assigned to each module in each one of the customized queries.

For example the following conf:

Backups_Size:SELECT SUM(allocated_page_size) FROM M_CONVERTER_STATISTICS;:generic_data:Estimated backup size
Timestamp:SELECT CURRENT_TIMESTAMP FROM DUMMY;:generic_data_string:Current timestamp
Hana_version:SELECT VERSION FROM M_DATABASE;:generic_data_string:version
Data_disk_information:SELECT HOST,PORT,FILE_NAME,STATE,SIZE,MAX_SIZE FROM M_DATA_VOLUMES:generic_data_string:Retrieve Hana disk info
Hana_service_statics:SELECT SERVICE_NAME, DATABASE_NAME, REQUESTS_PER_SEC, RESPONSE_TIME FROM SYS_DATABASES.M_SERVICE_STATISTICS:generic_data_string:Retrieve Hana Requests per second
Hana_cpu_time:SELECT HOST,PORT,SERVICE_NAME, SUM(CPU_TIME_SELF),SUM(CPU_TIME_CUMULATIVE) FROM M_SERVICE_THREADS GROUP BY HOST,PORT,SERVICE_NAME:generic_data_string:Retrieve Hana cpu usage

Hana_rs_fragmentation:SELECT HOST, PORT, TO_DECIMAL(SUM(FREE_SIZE) * 100 / SUM(ALLOCATED_SIZE),10,2), TO_DECIMAL( SUM(FREE_SIZE)/(1024*1024), 10, 2), TO_DECIMAL( SUM(ALLOCATED_SIZE)/(1024*1024), 10, 2) FROM M_RS_MEMORY WHERE CATEGORY = 'TABLE' OR CATEGORY = 'CATALOG'  GROUP BY HOST, PORT:generic_data_string:Retrieve Hana row store fragmentation

Hana_license:SELECT VALID, PERMANENT, PRODUCT_LIMIT, PRODUCT_USAGE FROM M_LICENSE;:generic_data_string:Retrieve license usage

Hana_IO_Stats:SELECT HOST,PORT,TYPE, TO_DECIMAL((TOTAL_READ_SIZE * 1.048576 / (TOTAL_READ_TIME+1)),10,2), TO_DECIMAL((TOTAL_WRITE_SIZE * 1.048576 / (TOTAL_WRITE_TIME+1)),10,2) FROM M_VOLUME_IO_TOTAL_STATISTICS:generic_data_string:Retrieve IO bandwidth

Hana_get_stated_connections:SELECT COUNT(*) FROM PUBLIC.M_CONNECTIONS WHERE CONNECTION_STATUS ='RUNNING':generic_data:retrieve connection status
Hana_Memory:SELECT HOST, round ( 100 * INSTANCE_TOTAL_MEMORY_USED_SIZE / INSTANCE_TOTAL_MEMORY_ALLOCATED_SIZE, 2), round (INSTANCE_TOTAL_MEMORY_ALLOCATED_SIZE / 1024/1024/1024, 2), round (INSTANCE_TOTAL_MEMORY_USED_SIZE / 1024/1024/1024, 2) from M_HOST_RESOURCE_UTILIZATION:generic_data_string:retrieve memory usages
Hana_Transactions:SELECT count(*) FROM M_BLOCKED_TRANSACTIONS;:generic_data:retrieve SAP transaction status

Check_norunning_backups:select * from SYS.M_BACKUP_CATALOG where STATE_NAME = 'running':generic_data_string:Check to  make sure there are no running backups still

Backup_id_running_backup:select BACKUP_ID from SYS.M_BACKUP_CATALOG where entry_type_name = 'complete data backup' and state_name = 'running'  order by sys_start_time desc:generic_data_string:Query to find BACKUP_ID of running data backup.

It will create the following modules:

Backups_Size Estimated backup size
Timestamp Current timestamp
Hana_version version Sap hana
Data_disk_information Retrieve Hana disk info
Hana_service_statics Retrieve Hana Requests per second
Hana_cpu_time Retrieve Hana cpu usage
Hana_rs_fragmentation Retrieve Hana row store fragmentation
Hana_license Retrieve license usage
Hana_IO_Stats Retrieve IO bandwidth
Hana_get_stated_connections retrieve connection status
Hana_Memory retrieve memory usages
Hana_Transactions retrieve SAP transaction status
Check_norunning_backups Check to make sure there are no running backups still
Backup_id_running_backup Query to find BACKUP_ID of running data backup