ulranh / Hana_sql_exporter

SAP Hana SQL Exporter for Prometheus

The purpose of this exporter is to support monitoring SAP and SAP HanaDB instances with Prometheus and Grafana.

Installation

The exporter can be downloaded as released Version. To build the current version you need the Go programming language:

$ git clone [email protected]:ulranh/hana_sql_exporter.git
$ cd hana_sql_exporter
$ go build

Preparation

Database User

A database user is necessary for every tenant with read access for all affected schemas:

# Login with authorized user:
$ create user <user> password <pw> no force_first_password_change;
$ alter user <user> disable password lifetime;
$ grant catalog read to <user>;

# Login with authorized user:
$ grant select on schema <schema> to <user>;
# <schema>: _SYS_STATISTICS, SAPABAP1, SAPHANADB ... 

Configfile

The next necessary piece is a toml configuration file where the encrypted passwords, the tenant- and metric-information are stored. The expected default name is .hana_sql_exporter.toml and the expected default location of this file is the users home directory. The flag --config (-c) can be used to assign other locations or names.

The file contains a Tenants slice followed by a Metrics Slice:

[[Tenants]]
  Name = "q01"
  Tags = ["abap", "ewm"]
  ConnStr = "hanaq01.example.com:32041"
  User = "dbuser1"

[[Tenants]]
  Name = "q02"
  Tags = ["abap", "erp"]
  ConnStr = "hanaqj1.example.com:31044"
  User = "dbuser2"

[[Metrics]]
  Name = "hdb_backup_status"
  Help = "Status of last hana backup."
  MetricType = "gauge"
  TagFilter = []
  SchemaFilter = [] # the sys schema will be added automatically
  SQL = "select (case when state_name = 'successful' then 0 when state_name = 'running' then 1 else -1 end) as val, entry_type_name as type from <SCHEMA>.m_backup_catalog where entry_id in (select max(entry_id) from m_backup_catalog group by entry_type_name)"

[[Metrics]]
  Name = "hdb_cancelled_jobs"
  Help = "Sap jobs with status cancelled/aborted (today)"
  MetricType = "counter"
  TagFilter = ["abap"]
  SchemaFilter = ["sapabap1", "sapabap","sapewm"]
  SQL = "select count(*) from <SCHEMA>.tbtco where enddate=current_utcdate and status='A'"

Below is a description of the tenant and metric struct fields:

Tenant information

Metric information

Database passwords

With the following commands the passwords for the example tenants above can be written to the Secret section of the configfile:

$ ./hana_sql_exporter pw --tenant q01 --config ./hana_sql_exporter.toml
$ ./hana_sql_exporter pw -t qj1 -c ./.hana_sql_exporter.toml

With one password for multiple tenants, the following notation is also possible:

$ ./hana_sql_exporter pw --tenant q01,qj1 --config ./hana_sql_exporter.toml

Usage

Now the web server can be started:

Binary

The default port is 9658 which can be changed with the -port flag. The standard timeout is set to 10 seconds, which means that if a scrape for one metric and tenant takes more than 10 seconds, it will be aborted. This is normally only the case, if a tenant is overloaded or the selects are really extensive. In my experience the scrapes for 25 tenants and 30 metrics in one config file take approximately 250ms altogether, if all tenants are responsive. Normally I set the timeout flag to 5 seconds, the scrape timeout for the corresponding Prometheus job to 10 seconds and the scrape intervall to one minute.

$ ./hana_sql_exporter web --config ./hana_sql_exporter.toml --timeout 5

Then you should be able to find the desired metrics after calling localhost:9658/metrics in the browser.

Docker

The Docker image can be downloaded from Docker Hub or built with the Dockerfile. Then it can be started as follows:

$ docker run -d --name=hana_exporter --restart=always -p 9658:9658 -v /home/<user>/.hana_sql_exporter.toml:/app/.hana_sql_exporter.toml <image name>

Kubernetes

An example config can be found in the examples folder. First of all create a sap namespace. Then apply the created configfile as configmap and start the deployment:

$ kubectl apply -f sap-namespace.yaml 
$ kubectl create configmap hana-config -n sap --from-file ./hana_sql_exporter.toml -o yaml
$ kubectl apply -f hana-deployment.yaml

Configfile changes can be applied in the following way:

$ kubectl create configmap hana-config -n sap --from-file ./hana_sql_exporter.toml -o yaml --dry-run | sudo kubectl replace -f -
$ kubectl scale --replicas=0 -n sap deployment hana-sql-exporter
$ kubectl scale --replicas=1 -n sap deployment hana-sql-exporter

Prometheus configfile

The necessary entries in the prometheus configfile can look something like the following:

  - job_name: sap
        scrape_interval: 60s
        static_configs:
          - targets: ['172.45.111.105:9658']
            labels:  {'instance': 'hana-exporter-test'}
          - targets: ['hana-exporter.sap.svc.cluster.local:9658']
            labels:  {'instance': 'hana-exporter-dev'}

Result

The resulting information can be found in the Prometheus expression browser and can be used as normal for creating alerts or displaying dashboards in Grafana.

The image below shows for example the duration of all complete data backups. With one dashboard it is possible to detect hanging or aborted backups of all systems:

More Information

• Monitoring SAP and Hana Instances with Prometheus and Grafana