How To Create Custom DB Metric

You would like to create and collect custom database (DB) metrics which are not provided as standard metrics of SAP Focused Run DB monitoring templates. For this purpose, you need to define:

  • A custom metric in a custom template in the System Monitoring (Template Maintenance) of SAP Focused Run.
  • A metric definition and corresponding SQL query for your custom metric within the DB metric definition file matching to the respective DB type within the SAP Host Agent configuration directory. This file has to be distributed to all DB hosts involved.  

Background

The database metric collections work for database metrics in SAP Focused Run quite different from SAP Solution Manager. In SAP Solution Manager a pull-based approach was used via a metric extractor ABAP function module within SAP Solution Manager to monitor DB metrics collected via remote DB connectivity of the DBA Cockpit.

This required a direct DB connection from SAP Solution Manager to the monitored database, but this is not supported in

SAP Focused Run due to communication channel simplification and security improvement.

In SAP Focused Run the DB specific metric extractors are realized by the Simple Diagnostic Agent (SDA) calling the SAP Host Agent with a DB specific function (ListDatabaseMetrics). The SAP Standard Metric configurations are delivered with FRUN-CONT.

You need to create your custom metric in a custom template (e.g. derived from SAP HANA DB template). From the template and the technical landscape data in the Landscape Management Database (LMDB) the configuration for the Monitoring and Alerting Infrastructure (MAI) will be generated. The distribution of the MAI configurations to the Simple Diagnostics Agents is done by the SAP Focused Run. For your custom metric you need to create as well an annotated SQL definition in the SAP Host Agent configuration within a custom metric definition file (same format as /usr/sap/hostctrl/exe/config.d/dbmetric.<dbtype>.cimobj). This needs to be distributed by you.

Only metrics which can be collected by SQL statement are possible to be collected by custom SAP Focused RUN DB Metrics.

Metrics where the database must implement e.g. a new API, or a new stored DB procedure are typically developed on customer request to SAP Focused Run and then become part of the SAP Focused Run Standard after the DB vendor provided the feature.
Please check internally if the desired metric can be queried by a SQL statement.

Figure 1: Technical Architecture Model for Database Monitoring with SAP Focused Run

DB Data Collectors

When creating a custom metric in SAP Focused Run, you need to select:

  • For Hana DB we have the data collectors:
    • "Database Status DB Level": is mapped to the Host Agent function "GetDatabaseClusterStatus". This cannot be used for custom metrics. It is used for availability metrics on DB level.
    • "Database Cluster Status": is mapped to the Host Agent function "GetDatabaseClusterStatus". This cannot be used for custom metrics. It is used for availability metrics on DB instance level.
    • "Database Metrics (HANA DB)": is mapped to the Host Agent function "ListDatabaseMetrics". This can be used for custom metrics (if the metric value(s) can be retrieved by executing an SQL statement on the database). See below for details.
  • For MSSQL we have the data collectors :
    • "Database Status MSSQL": is mapped to the Host Agent function "GetDatabaseStatus". This cannot be used for custom metrics.
    • "Database Metrics (MS SQL)": is mapped to the Host Agent function "ListDatabaseMetrics". This can be used for custom metrics (if the metric value(s) can be retrieved by executing an SQL statement on the database). See below for details.
  • For Oracle we have the data collectors :
    • "Database Status Oracle": is mapped to the Host Agent function "GetDatabaseStatus". This cannot be used for custom metrics.
    • "Database Metrics (Oracle)": is mapped to the Host Agent function "ListDatabaseMetrics". This can be used for custom metrics (if the metric value(s) can be retrieved by executing an SQL statement on the database). See below for details.

Your custom database metrics have to be added to a custom template derived from the SAP template on database level (“Database”). The levels “Tenant Database Instance” or “Database Instance” are only used for instance level availability metrics and not for SQL based metrics. The level “Database Replication Group” is used for Hana DB metrics specific to replication scenario information. If your metric is replication specific it would make sense to add it in a custom template on this level.

You have to select the Data Collector when you create a custom metric. As “Data Collector Type” you have to choose “Diagnostics Agent (push)” and the corresponding Collector Name for the DB type.

Figure 2  Create Custom Metric

Metric Definition (Host Agent)

Annotated SQL

For each supported DB type there is a configuration file delivered together with the SHA located in the directory /usr/sap/hostctrl/exe/config.d.

For Hana DB the configuration file is named "dbmetric.definition.hdb.cimobj", for MSSQL it is named "dbmetric.definition.mss.cimobj", for Oracle it is named "dbmetric.definition.ora.cimobj", etc.

These files contain actually the definition of SQL statements per metric. The parameter KEY_FIG in the metric definition holds the key (identifier) of the metric within the dbmetric file.

Therefore, to add a custom metric you can use one of the described options.
The easiest one is just to copy a standard metric and replace the KEY_FIG value with own defined one which is not already used for any other metric within the dbmetric file (e.g. by starting the custom metric name with something like “Z”). Afterwards, you can copy the section in the dbmetric file which belongs to the copied SAP metric and replace the corresponding KEY_FIG value everywhere in the copied section. Then you replace the copied SQL statement with the one you want to have, like dbmetric.definition.mss.cimobj.

 

 

Remark: this option has the disadvantage that a file is getting overwritten by applying a new SHA patch level, that's why it's only suitable for testing purposes!

As of Host Agent 7.21 PL40 it is per default looking into a sub-directory named /usr/sap/hostctrl/exe/config.d/dbmetrics.d for a custom dbmetric file. The recommended approach would be to create a new file custom.definition.<dbtype>.cimobj and copy it into that folder. This needs to have the same format as dbmetric.definition.<dbtype>.cimobj, but should only contain the custom metric definitions. The advantage of using this sub-directory is that custom files inside there will be kept also during Host Agent upgrades. Otherwise, a modified file /usr/sap/hostctrl/exe/config.d/dbmetric.definition.<dbtype>.cimobj would be overwritten with the next Host Agent upgrade!

Format

The file consists of two main areas: the header -

SAP_ITSAMMetricDefinition, followed by SAP_ITSAMDatabaseMetric and SAP_ITSAMSQLStatement.

The SAP_ITSAMMetricDefinition is valid for the whole file and contains the name of the file as well as the optional description, versioning and the time stamp.

SAP_ITSAMDatabaseMetric contains the actual name of the metric and

SAP_ITSAMSQLStatement has a corresponding SQL statement, which will be executed. This has to be defined per metric.

cimobject {
   {Name: CreationClassName; Type: String; Value: SAP_ITSAMMetricDefinition}
   {Name: Name; Type: String; Value: CUSTOM_MSSQL_METRIC_DEFINITIONS}
   {Name: Description; Type: String; Value: Custom MSSQL Monitoring Metrics Content}
   {Name: Release; Type: String; Value: 01.0000.0000}
   {Name: Date; Type: String; Value: 2019-02-14}
   {Name: Time; Type: String; Value: 17:39:00}
  cimobject {
    {Name: CreationClassName; Type: String; Value: SAP_ITSAMDatabaseMetric}
     {Name: Name; Type: String; Value: ZZ_MyNewMetric}
     {Name: SysReleaseMin; Type: String; Value: 10.00.0000.00}
     {Name: SysReleaseMax; Type: String; Value: 99.99.9999.00}
     {Name: Dependencies; Type: String[] sep=; Value:}
     {Name: StatementName; Type: String; Value: ZZ_MyNewMetric_SQL}
  }
  cimobject {
     {Name: CreationClassName; Type: String; Value: SAP_ITSAMSQLStatement}
     {Name: Name; Type: String; Value: ZZ_MyNewMetric_SQL}
     {Name: CallOrder; Type: int32; Value: 1}
     {Name: Comment; Type: String; Value: "My custom metric"}
     {Name: Kind; Type: String; Value: QUERY}
     {Name: SysReleaseMin; Type: String; Value: 10.00.0000.00}
     {Name: SysReleaseMax; Type: String; Value: 99.99.9999.00}
     {Name: Dependencies; Type: String[] sep=; Value:}
     {Name: Placeholder; Type: String[] sep=; Value:}
     {Name: Statement; Type:String; Value: XXXXXXXX HERE GOES YOUR SQL STATEMENT XXXXXXX }
  }
}

 

As a result, the SQL Statement has to return one row (single metric) or multiple rows (metric group) in the following result row format:

Column Name SQL Type
TIMESTAMP TIMESTAMP
METRIC_ID  VARCHAR(128)
SYSTEM_TYPE VARCHAR(128) -> Database / Instance / ...
SYSTEM_ID   VARCHAR(128) -> DB Name / Instance Name / other Identifier
SYSTEM_HOST VARCHAR(128) -> Hostname
RESOURCE  VARCHAR(255)
VALUE VARCHAR(255)
UNIT                 VARCHAR(32)
RATING                VARCHAR(2)

  • As TIMESTAMP return the current UTC timestamp.
  • As METRIC_ID/METRIC* return the KEY_FIG value for the metric (in our example this would be 'ZZ_MyNewMetric')
  • As SYSTEM_TYPE return the constant 'Database' (as you define a metric on DB level).
  • As SYSTEM_ID returns the DB name of the database.
  • As SYSTEM_HOST return the host name the DB is running on (resp. the host where the statement is executed).
  • As RESOURCE return an empty string '' for a single metric and a list of key-value pairs for all metric variant parameters for a metric group. The syntax of providing a metric variant parameter list is as follows: <PARAMETER_NAME_1>=<VALUE_1>|...|<PARAMETER_NAME_N>=<VALUE_N> (separator character is a vertical bar '|'). Here an example if you would have two variant parameters named HOST and FOLDER:
    'HOST=ld314.google.com|FOLDER=/usr/sap' AS RESOURCE
  • As VALUE return the metric value.
  • As UNIT return a string identifying a unit name (e.g. 'ms' for milliseconds) – currently it is not used anywhere and therefore is optional.
  • As RATING/ALERT_RATING** you should return a metric rating which is used as rating if the threshold type 'Already rated' is used in the metric definition. The following RATING values are allowed:
    • '1' for green rating
    • '2' for yellow rating
    • '3' for red rating
    • '0' for gray rating
    • '-1' for an error that happened during metric data collection

Remark:
(*) all DB types expecting METRIC_ID as an input parameter except HANA DB, for HANA DB please use just METRIC.
(**) ALERT_RATING to be used in context of HANA DB, for the rest of DBs just RATING is expected.

The SQL Statements can be stored as decoded or encoded in the definition file. The default type is “String”, means the statement is stored decoded in one string.

The SQL Statements can also be stored as Base64 encoded in order to avoid conflicts with the cimobject parser. In this case you have to use the type “Base64”. In the examples below you will find a sample with Base64 encoding for MS SQL and without it for HANA DB.

Example MS-SQL Metric (with Base64 Encoding)

Assuming to start with a copy of the MSSQL metric "Days since last backup" in your custom template to a new custom metric named "My custom MSSQL metric". The original KEY_FIG value is "DB.MSS.DaysSinceLastBackup" and we change it to the custom KEY_FIG value "ZZ_MyNewMetric".

The new metric definition in the dbmetric file could be defined by adding it into the original file /usr/sap/hostctrl/exe/config.d/dbmetric.definition.mss.cimobj.

Complex SQL Statement should be encoded using Base64 without line wrapping (using for example base64 -w0 on linux). 
sapdbctrl will automatically transform the Base64 value in a string once the content file is parsed and loaded for processing.

An encoded  Statement string for the DB.MSS.DaysSinceLastBackup metric looks like this:

cimobject {
{Name: CreationClassName; Type: String; Value: SAP_ITSAMDatabaseMetric}
{Name: Name; Type: String; Value: ZZ_MyNewMetric}
{Name: SysReleaseMin; Type: String; Value: 10.00.0000.00}
{Name: SysReleaseMax; Type: String; Value: 99.99.9999.00}
{Name: Dependencies; Type: String[] sep=; Value:}
{Name: StatementName; Type: String; Value: ZZ_MyNewMetric_SQL}
}
cimobject {
{Name: CreationClassName; Type: String; Value: SAP_ITSAMSQLStatement}
{Name: Name; Type: String; Value: ZZ_MyNewMetric_SQL}
{Name: CallOrder; Type: int32; Value: 1}
{Name: Comment; Type: String; Value: "Days Since Last Backup"}
{Name: Kind; Type: String; Value: QUERY}
{Name: SysReleaseMin; Type: String; Value: 10.00.0000.00}
{Name: SysReleaseMax; Type: String; Value: 99.99.9999.00}
{Name: Dependencies; Type: String[] sep=; Value:}
{Name: Placeholder; Type: String[] sep=; Value:}
{Name: Statement; Type: Base64; Value: U0VMRUNUIE4nREIuTVNTLkRheXNTaW5jZUxhc3RCYWNrdXAnIGFzIFtNRVRSSUNfSURdLCBjb252ZX
J0KG52YXJjaGFyLCBnZXRkYXRlKCksIDEyMSkgYXMgW1RJTUVTVEFNUF0sIE4nRGF0YWJhc2UnIGFz
IFtTWVNURU1fVFlQRV0sIENBU0UgV0hFTiBzZXJ2ZXJwcm9wZXJ0eShOJ0luc3RhbmNlTmFtZScpIE
lTIE5VTEwgVEhFTiBOJyRkYl9uYW1lJCcgRUxTRSBOJyRkYl9uYW1lJC8nICsgQ0FTVChzZXJ2ZXJw
cm9wZXJ0eShOJ0luc3RhbmNlTmFtZScpIEFTIG52YXJjaGFyKSBFTkQgYXMgW1NZU1RFTV9JRF0sIH
NlcnZlcnByb3BlcnR5KE4nQ29tcHV0ZXJOYW1lUGh5c2ljYWxOZXRCSU9TJykgYXMgW1NZU1RFTV9I
T1NUXSwgTicnIGFzIFtSRVNPVVJDRV0sIGRhdGVkaWZmKGRheSxiYWNrdXBfc3RhcnRfZGF0ZSxnZX
RkYXRlKCkpIGFzIFtWQUxVRV0sIE4nRGF5cycgYXMgW1VOSVRdIEZST00gbXNkYi4uYmFja3Vwc2V0
IHdoZXJlIGRhdGFiYXNlX25hbWUgPSBOJyRkYl9uYW1lJCcgYW5kIGJhY2t1cF9zdGFydF9kYXRlID
0gKHNlbGVjdCBtYXgoYmFja3VwX3N0YXJ0X2RhdGUpIGZyb20gbXNkYi4uYmFja3Vwc2V0IHdoZXJl
IGRhdGFiYXNlX25hbWUgPSBOJyRkYl9uYW1lJCcgYW5kIHR5cGUgPD4gJ0wnKQ==}
}

 

In order to encode or decode your SQL statement, you can use e.g. https://www.base64decode.org/.

This is how the decoded statement from above example would look like:

SELECT N'DB.MSS.DaysSinceLastBackup' as [METRIC_ID], convert(nvarchar, getdate(), 121) as [TIMESTAMP], N'Database' as [SYSTEM_TYPE], CASE WHEN serverproperty(N'InstanceName') IS NULL THEN N'$db_name$' ELSE N'$db_name$/' + CAST(serverproperty(N'InstanceName') AS nvarchar) END as [SYSTEM_ID], serverproperty(N'ComputerNamePhysicalNetBIOS') as [SYSTEM_HOST], N'' as [RESOURCE], datediff(day,backup_start_date,getdate()) as [VALUE], N'Days' as [UNIT] FROM msdb..backupset where database_name = N'$db_name$' and backup_start_date = (select max(backup_start_date) from msdb..backupset where database_name = N'$db_name$' and type <> 'L')

Example HANA Metric

An example of HANA custom metric to monitor the total size of row store table indexes in GB :

cimobject {
  {Name: CreationClassName; Type: String; Value: SAP_ITSAMMetricDefinition}
  {Name: Name; Type: String; Value: CUSTOM_HDB_METRIC_DEFINITIONS}
  {Name: Description; Type: String; Value: Custom HDB Monitoring Metrics Content}
  {Name: Release; Type: String; Value: 01.0000.0000}
  {Name: Date; Type: String; Value: 2019-05-14}
  {Name: Time; Type: String; Value: 14:00:00}
  cimobject {
   {Name: CreationClassName; Type: String; Value: SAP_ITSAMDatabaseMetric}
    {Name: Name; Type: String; Value: Z_RowStoreTable}
    {Name: SysReleaseMin; Type: String; Value: 1.00.080.00.0000000000}
    {Name: SysReleaseMax; Type: String; Value: 9.99.999.99.9999999999}
    {Name: Dependencies; Type: String[] sep=; Value:}
    {Name: StatementName; Type: String; Value: Z_MyRowStoreMetric}
  }
  cimobject {
    {Name: CreationClassName; Type: String; Value: SAP_ITSAMSQLStatement}
    {Name: Name; Type: String; Value: Z_MyRowStoreMetric}
    {Name: CallOrder; Type: int32; Value: 1}
    {Name: Comment; Type: String; Value: "Row Store Tables Indexes Total Size"}
    {Name: Kind; Type: String; Value: QUERY}
    {Name: SysReleaseMin; Type: String; Value: 1.00.080.00.0000000000}
    {Name: SysReleaseMax; Type: String; Value: 9.99.999.99.9999999999}
    {Name: Dependencies; Type: String[] sep=; Value:}
    {Name: Placeholder; Type: String[] sep=; Value:}
    {Name: Statement; Type:String; Value: SELECT 'HOST=' || "HOST" AS "RESOURCE", 'Database' AS "SYSTEM_TYPE", CURRENT_UTCTIMESTAMP AS "TIMESTAMP", 'GB' AS "UNIT", "VALUE", 'Z_RowStoreTable' AS "METRIC", '0' AS "ALERT_RATING" from (select host, to_char(round( SUM(INDEX_SIZE)/1024/1024/1024,2 )) value from sys.M_RS_INDEXES group by host) }
  }
}

 

Test Custom Metric

Once a custom metric is defined and stored under mentioned folder, it can be tested by executing the simplified method “ListDatabaseMetrics”:

./saphostctrl -function ListDatabaseMetrics -dbname <DB Name> -dbtype <hdb|mss|...> -id <CustomMetricID>

For the above mentioned examples it would look like this:

MS SQL: saphostctrl.exe -function ListDatabaseMetrics -dbname ABC -dbtype mss -id ZZ_MyNewMetric

HANA: ./saphostctrl -function ListDatabaseMetrics -dbname XYZ -dbtype hdb -id Z_RowStoreTable

You should get a similar output: 

 

Metric Definition (SAP Focused Run)

Finally, on the SAP Focused Run side the name of the custom metric has to be entered as the parameter value of the KEY_FIG  during the custom metric creation process. The KEY_FIG value should match to the specified value of METRIC/METRIC_ID.

Figure 3: Key_FIG