SAP HANA support for custom SQL queries

Products

• DX SaaS

Issue/Introduction

You are already using APMIA SAP-HANA Database extension, but for your monitoring requirements, there are some monitoring gaps and you need help to implement some type of solution to cover these gaps. 

You are using Broadcom APM SQL Execution Monitor to perform query-based monitoring for supported databases such as Oracle and SQL Server but this is not working for SAP HANA database .

As part of your requirement, you need to monitor SAP HANA database health, specifically around SAP-recommended operational checks (for example, monitoring delta merge behavior using SAP system views such as SYS.M_CS_TABLES).

Resolution

Add custom query in schema.json with APMIA SAP-HANA Database extension:

Perform the following steps:

• Ensure the out of the box APMIA SAP HANA DB monitoring extension is properly configured and able to report metrics. Please refer to the following online docs for the installation details and the available SAP HANA DB metrics - SAP-HANA Database.
• Ensure the SAP HANA DB user credential used for the OOTB APMIA SAP HANA DB monitoring extension also has sufficient permissions to run the custom queries for the new metrics.
• Back up the original schema.json under APMIA extension installation subfolder $<apmia>\extensions\saphana-db\config.

• Update schema.json to add the new metrics with the custom queries.  For example,

      {
      "query": "SELECT SCHEMA_NAME, TABLE_NAME, LAST_MERGE_TIME, MEMORY_SIZE_IN_DELTA, MODIFY_TIME FROM SYS.M_CS_TABLES WHERE (LAST_MERGE_TIME IS NULL OR LAST_MERGE_TIME < ADD_SECONDS(CURRENT_TIMESTAMP, -3 * 24 * 3600)) AND MEMORY_SIZE_IN_DELTA > 0 AND (MODIFY_TIME IS NULL OR MODIFY_TIME > ADD_SECONDS(CURRENT_TIMESTAMP, -7 * 24 * 3600)) AND TABLE_NAME NOT LIKE '/1CADMC/%' AND TABLE_NAME NOT LIKE '/1BW/%' ORDER BY CASE WHEN LAST_MERGE_TIME IS NULL THEN 0 ELSE 1 END, LAST_MERGE_TIME ASC;",
       "metrics": [
          {
           "metricPath": [
             {
               "name": "Delta Merge|"
             },
             {
               "name": "$.resultSet[*].SCHEMA_NAME"
             },
             {
               "name": "|"
             },
             {
               "name": "$.resultSet[*].TABLE_NAME"
             },
             {
               "name": ":Last Merge Time"
             }
           ],
           "metricType": "StringEvent",
           "metricValue": "$.resultSet[*].LAST_MERGE_TIME"
          },
          {
           "metricPath": [
             {
               "name": "Delta Merge|"
             },
             {
               "name": "$.resultSet[*].SCHEMA_NAME"
             },
             {
               "name": "|"
             },
             {
               "name": "$.resultSet[*].TABLE_NAME"
             },
             {
               "name": ":Modify Time"
             }
           ],
           "metricType": "StringEvent",
           "metricValue": "$.resultSet[*].MODIFY_TIME"
         },
          {
           "metricPath": [
             {
               "name": "Delta Merge|"
             },
             {
               "name": "$.resultSet[*].SCHEMA_NAME"
             },
             {
               "name": "|"
             },
             {
               "name": "$.resultSet[*].TABLE_NAME"
             },
             {
               "name": ":Memory Size In Delta"
             }
           ],
           "metricType": "LongAverage",
           "metricValue": "$.resultSet[*].MEMORY_SIZE_IN_DELTA"
         }
       ]
     },
   

• Restart APMIA.
• Verify the newly added metrics in metric viewer.