# Sap hana database

# Introduction

**Ver.** 28-06-2022

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

**Type**: Server plug-in

# Compatibility matrix

<table border="1" id="bkmrk-sistemas-donde-se-ha"><tbody><tr><td>**Systems tested**</td><td>CentOS 7

</td></tr><tr><td>**Systems where it should work**</td><td>Any linux system</td></tr></tbody></table>

<table border="1" id="bkmrk-versi%C3%B3n-de-sap-donde"><tbody><tr><td>**SAP version tested on**</td><td>2.00.059

</td></tr><tr><td>**Systems where it should work**</td><td>Any version

</td></tr></tbody></table>

# Pre requisites

- Pandora FMS Data Server enabled
- Pandora FMS Plugin Server enabled.

# Parameters

<table border="1" id="bkmrk-par%C3%A1metro-descripci%C3%B3" style="border-collapse: collapse; width: 100%; height: 419px;"><tbody><tr style="height: 29px;"><td style="width: 50%; height: 29px;">**Parámetro**</td><td style="width: 50%; height: 29px;">**Descripción**</td></tr><tr style="height: 29px;"><td style="width: 50%; height: 29px;">--ip</td><td style="width: 50%; height: 29px;">IP or host of the database (required)</td></tr><tr style="height: 29px;"><td style="width: 50%; height: 29px;">--user</td><td style="width: 50%; height: 29px;">SAP Hana user name(required)</td></tr><tr style="height: 29px;"><td style="width: 50%; height: 29px;">--password</td><td style="width: 50%; height: 29px;">SAP Hana password (required)</td></tr><tr style="height: 31px;"><td style="width: 50%; height: 31px;">--port</td><td style="width: 50%; height: 31px;">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.</td></tr><tr style="height: 29px;"><td style="width: 50%; height: 29px;">--tentacle\_port</td><td style="width: 50%; height: 29px;">Tentacle port Default 41121 (optional)</td></tr><tr style="height: 29px;"><td style="width: 50%; height: 29px;">--tentacle\_address</td><td style="width: 50%; height: 29px;">Tentacle server Ip to send data to (optional)</td></tr><tr style="height: 29px;"><td style="width: 50%; height: 29px;">--agent\_name</td><td style="width: 50%; height: 29px;">To name the agent that will contain the modules of the custom queries, by default : "Sap\_Hana" (optional).</td></tr><tr style="height: 29px;"><td style="width: 50%; height: 29px;">--prefix\_module</td><td style="width: 50%; height: 29px;">To put a prefix to the modules in case you want to differentiate them from other executions (optional).</td></tr><tr style="height: 29px;"><td style="width: 50%; height: 29px;">--conf</td><td style="width: 50%; height: 29px;">Path of the conf file with the queries (required)</td></tr><tr style="height: 29px;"><td style="width: 50%; height: 29px;">-g,--group</td><td style="width: 50%; height: 29px;">Pandora FMS target group (optional)</td></tr><tr style="height: 46px;"><td style="width: 50%; height: 46px;">--data\_dir</td><td style="width: 50%; height: 46px;">Pandora FMS data directory. By default it is /var/spool/pandora/data\_in/ (optional)</td></tr><tr style="height: 63px;"><td style="width: 50%; height: 52px;">--as\_agent\_plugin</td><td style="width: 50%; height: 52px;">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)</td></tr></tbody></table>

**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](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/scaled-1680-/image-1656435359935.PNG)](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/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](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/scaled-1680-/image-1656435538204.PNG)](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/image-1656435538204.PNG)

# Configuration in PandoraFMS

Manual installation

Go to servers &gt; plugins:

[![image-1629974405286.png](https://pandorafms.com/guides/public/uploads/images/gallery/2021-08/scaled-1680-/image-1629974405286.png)](https://pandorafms.com/guides/public/uploads/images/gallery/2021-08/image-1629974405286.png)

Click on add:

[![image-1629974430627.png](https://pandorafms.com/guides/public/uploads/images/gallery/2021-08/scaled-1680-/image-1629974430627.png)](https://pandorafms.com/guides/public/uploads/images/gallery/2021-08/image-1629974430627.png)

We put the name and description of your choice:

[![image-1656437269121.png](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/scaled-1680-/image-1656437269121.png)](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/image-1656437269121.png)

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

```
/path_pandora_hana
```

<p class="callout info align-center">Remember that the recommended path for the use of the server plugins is: /usr/share/pandora\_server/util/plugin/</p>

[![image-1656437381803.png](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/scaled-1680-/image-1656437381803.png)](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/image-1656437381803.png)

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

<p class="callout warning">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.</p>

**--IP**

[![image-1656437985204.png](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/scaled-1680-/image-1656437985204.png)](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/image-1656437985204.png)

**--PORT**

[![image-1656438071776.png](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/scaled-1680-/image-1656438071776.png)](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/image-1656438071776.png)

**--USER**

[![image-1656438378630.png](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/scaled-1680-/image-1656438378630.png)](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/image-1656438378630.png)

**--PASSWORD**

[![image-1656438434646.png](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/scaled-1680-/image-1656438434646.png)](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/image-1656438434646.png)

**--CONF**

[![image-1656438528074.png](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/scaled-1680-/image-1656438528074.png)](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/image-1656438528074.png)

**--AGENT\_NAME**

[![image-1656438571290.png](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/scaled-1680-/image-1656438571290.png)](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/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](https://pandorafms.com/guides/public/uploads/images/gallery/2022-03/scaled-1680-/image-1646741530197.png)](https://pandorafms.com/guides/public/uploads/images/gallery/2022-03/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](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/scaled-1680-/image-1656438657656.png)](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/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](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/scaled-1680-/image-1656438714123.png)](https://pandorafms.com/guides/public/uploads/images/gallery/2022-06/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](https://pandorafms.com/guides/public/uploads/images/gallery/2022-09/scaled-1680-/image-1664371961325.png)](https://pandorafms.com/guides/public/uploads/images/gallery/2022-09/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:

<table border="1" id="bkmrk-backups_size-estimat"><tbody><tr><td>Backups\_Size</td><td>Estimated backup size</td></tr><tr><td>Timestamp</td><td>Current timestamp</td></tr><tr><td>Hana\_version</td><td>version Sap hana</td></tr><tr><td>Data\_disk\_information</td><td>Retrieve Hana disk info</td></tr><tr><td>Hana\_service\_statics</td><td>Retrieve Hana Requests per second</td></tr><tr><td>Hana\_cpu\_time</td><td>Retrieve Hana cpu usage</td></tr><tr><td>Hana\_rs\_fragmentation</td><td>Retrieve Hana row store fragmentation</td></tr><tr><td>Hana\_license</td><td>Retrieve license usage</td></tr><tr><td>Hana\_IO\_Stats</td><td>Retrieve IO bandwidth</td></tr><tr><td>Hana\_get\_stated\_connections</td><td>retrieve connection status</td></tr><tr><td>Hana\_Memory</td><td>retrieve memory usages</td></tr><tr><td>Hana\_Transactions</td><td>retrieve SAP transaction status</td></tr><tr><td>Check\_norunning\_backups</td><td>Check to make sure there are no running backups still</td></tr><tr><td>Backup\_id\_running\_backup</td><td>Query to find BACKUP\_ID of running data backup</td></tr></tbody></table>