How to enhance CCDB with database content from custom SQL

This document describes how to enhance the data collection of the Advanced Configuration Monitoring with custom SQL commands and how to extend existing config stores in CCDB for custom tailored content.

The following steps must be performed:

  1. Create SQL definition
  2. Identify suitable CCDB table
  3. Create a custom config store definition
  4. Perform SSI Setup

The sections below describe the above-mentioned steps in detail. For further questions please check the FAQ section at the end of this page.

SQL Definition 

Step 1: Create SQL Definition

For loading custom-specific data from a database into CCDB, the database interface of the host agent must be enhanced with the corresponding SQL statements. Accessible tables are limited by the authorizations of the SAP Hostagent administrator user. Usually, this user's authorizations are limited to views of the HANA Statistics Server and to HANA System Views. In this case, no application data is accessible by this user.


Actions performed in this step must be performed on OS Level. Please refer to SAP Help Portal for latest SAP Hostagent documentation as referenced in SAP Note 1907566 - Accessing the Latest SAP Host Agent Documentation

 

Host Agent file location

Directory /usr/sap/hostctrl/exe/config.d contains several dbmetric.definition.<dbtype>.cimobj files. Each file contains database specific SQL commands which could be issued via SAP host agent. It contains also SQL which is used by the Focused Run Configuration and Security Analytics Collector Framework (FRUN CSA CF) to retrieve configuration data for config stores. The file contains cimobject definitions. The ones used for FRUN CSA CF are labelled: “SAP_ITSAM_DatabaseConfigStore” and “SAP_ITSAMDatabaseConfigSQLStatement”.

 

SQL Execution

On the command line the following command can be issued to get the output of an SQL statement:

./saphostctrl -function GetCIMObject -enuminstances SAP_ITSAMDatabaseConfigStore -properties "Name=<DB_NAME>/<INSTANCE_ID>&Type=<DB_SAP_TYPE>&CheckManagementHostMode=strict" arguments "ID=<DataID>"

The DataID argument should indicate semantics of the related SQL query, e.g.: For querying configured HANA Audit Policies (as used for config store HDB_POLICIES) for database H40 with DB instance HDB40  the following command can be used:

/usr/sap/hostctrl/exe/saphostctrl -function GetCIMObject -enuminstances SAP_ITSAMDatabaseConfigStore properties "Name=H40&InstanceName=HDB40&Type=HDB&CheckManagementHostMode=strict" arguments "ID=HDB_POLICIES"

 

Custom SQL definitions

Custom specific SQL metrics can be created in the path “/usr/sap/hostctrl/exe/config.d/dbmetrics.d”: The file names should follow the syntax “<NN>.<CustomName>.<DB-Type>.cimobj

Screenshot of OS level folder structure

Figure: SAP Host Agent folder structure

Each file has to contain a header cimobject. In the SAP_ITSAMMetricDefinition section, the bold values should be replaced with customer name and description.

In the SAP_ITSAMDatabaseConfigStore section, you need to replace the value of the “Name”, so that it matches to the value of the DataID in the corresponding Store Collector Item Template on FRUN CSA CF side (see section: …). The value for StatementName is used by the Host Agent internally. It must be identical with the name in the SAP_ITSAMDatabaseConfigSQLStatement section. We recommend choosing a name that corresponds to the DataID. Note the different format of the prefix: “HDB_” vs. “DB.HDB.”!

In section SAP_ITSAMDatabaseConfigSQLStatement, you define the details of the SQL statement. Apart from the name you need to maintain the software versions that the SQL statement shall be applied to. Specifying a correct version dependence of the SQL is important: When creating policy checks for security validation on the collected data, it is very useful to define dedicated rules for compliance and non-compliance, while it is usually the desired behavior to suppress check-output if no data is found. Avoiding collection of meaningless data, simplifies policy creation later-on.

The SQL statement itself should be created in base64 format in order to minimize efforts with escape expressions. Standard tools from the internet are recommended for encoding to Base64 format.

The following example can serve as a reference for a custom definition of a cimobj file. The example creates an additional cimobj file in directory dbmetrics.d. The file contains the SQL to retrieve existing audit polices from HANA using table AUDIT_POLICY:

 

Figure: Example content of a Cimobj file containing SQL in base64 format

Currently, FRUN CSA CF does not support the integration of customer created CCDB tables into the CF framework. That is why the example uses the existing CCDB table for TableStores to store the data for audit policies.

For loading configuration data from a database into the CCDB, the custom SQL must return the following columns:

  • STORE must match template definition name
  • NAME main key field  DB_ATTR_NAME as config store field
  • RESOURCE additional concatenated key field (max 5000 chars)  Extractor is able to split fields into column fields of the extractor
  • SYSTEM_HOST could be empty
  • SYSTEM_ID could be empty
  • SYSTEM_TYPE could be empty
  • VALUE_ATTRIBUTES concatenated data  field (max 5000 chars)  Extractor is able to split fields into column fields of the extractor
  • VALUE main data field DB_ATTR_VALUE as config store field

For columns RESOURCE and VALUE_ATTRIBUTES there is a special feature in the SQL. For both, multiple name/value pairs can be specified separated by a semicolon:

Example of the format: “<DBFIELDNAME_B>=<VALUE_A>;<DBFIELDNAME_B>=<VALUE_B>;…”

To make sure the name/value separator does not conflict with the values which may also contain the semicolon and the character ‘=', these characters must be escaped in the values <VALUE_A> and <VALUE_B>.

To achieve this the DAGT Collector Framework implementation uses the character “\” as escape symbol. For the defined SQL statement, it means that as soon as the name/value pairs are used in the columns RESOURCE or VALUE_ATTRIBUTES and one of the characters “;” and “=” are contained in the values, an escaping must be done.

Because of concatenation in the columns RESOURCE and VALUE_ATTRIBUTES columns which can contain null values have to be converted to a character type explicitly!

If the SQL statement is encoded by binary64 (as recommended), the basic syntax rules for escaping are:

  • Escaped expression if column cannot have null values: replace(replace(replace(<COLUMN>, '\','\\'), ';', '\;'), '=', '\=')
  • Escaped expression with conversion of null values (always possible): replace(replace(replace(coalesce( <COLUMN>, ''), '\','\\'), ';', '\;'), '=', '\=')

Additionally, the SQL statement itself must not contain windows or unix line breaks (LF, CRLF).

To create your SQL statement with the required content and format, proceed as follows:

  • Identify the HANA system view that contains the relevant information. Check its structure and field attributes in SAP HANA SQL and System Views Reference (in SAP Help Portal).
  • Create a simple SQL to check the actual content in a HANA database. Identify the columns and restrict the SQL to select only relevant data.

For the example with the Audit Policies this is the SQL statement:

Screenshot of an initial SQL Query

Figure: Initial SQL Query (with the usual syntax)

Adapt your SQL Statement, so that during data upload the actual column structure of the database table can be automatically transformed to a column structure that fits to the chosen CCDB generic table store. The SQL definition must include a mapping of table columns to all CCDB attributes listed in the table.

Field in SQL Definition Attribute in FRUN CSA CF template Comment
NAME

DB_ATTR_NAME

The main key column

STORE

DB_ATTR_STORE

 

SYSTEM_HOST

DB_ATTR_SYSTEM_HOST

 

SYSTEM_ID

DB_ATTR_SYSTEM_ID

 

SYSTEM_TYPE

DB_ATTR_SYSTEM_TYPE

 

VALUE

DB_ATTR_VALUE

 

RESSOURCE

Column Name of value pairs is used as fieldname, for example: “FIELDNAME1=Paul;FIELDNAME2=Fred”

All key columns as concatenated key/value pairs (except the column specified as “NAME”).

VALUE_ATTRIBUTES

Column Name of value pairs is used as fieldname

All value columns as concatenated key/value pairs (except the column specified as “VALUE”)

Table: Mapping of column names in SQL query to mandatory attributes of the FRUN CSA Collector Framework 

The fieldnames (e.g. “FIELDNAME1”) in RESSOURCE and VALUE_ATTRIBUTES will be used on the user interfaces of CSA applications. The order of fieldnames on UI-level is specified in the CSA Template Management as part of the custom config store definition (see section 3). Related parameters the custom config store definition are named "CCDB_Generic_Column-01”, "CCDB_Generic_Column-02”, …

For our example this is the adapted SQL statement (Make sure that it is tested prior to its used in the base64 encoded format):

Screenshot taken from ABAP transaction DBACOCKPIT (Diagnostics - SQL Editor)

Figure: SQL Input Query with all adaptations required for CCDB 

Screenshot taken from ABAP transaction DBACOCKPIT (Diagnostics - SQL Editor)

Figure: SQL Result (transaction DBACOCKPIT is used)

4.     Encode the SQL to Base64 with a tool of your choice. Add the Output to your cimobject file.

5.     Distribute the new cimobject file to the correct directory in the file system of the managed systems

CCDB Table

Step 2: Identify suitable CCDB Table

The data in FRUN CCDB is saved in regular transparent tables. In contrary to previous releases of Solution Manager the FRUN CCDB provides individual tables for each table structure. This allows a fast access using database means like regular SQL.

For custom SQL extractions we recommend using generic TableStores with names in the range of CCDB_DATA_500 to CCDB_DATA_599. The table below provides an overview about the currently available generic TableStores:

Length of data fields /
Table name
Key Fields 1 16 32 64 128 256 512 1024 1333
CCDB_DATA_500 Hash 2         4      
CCDB_DATA_501 3 3 3   3 (+2) 3 (+1) 3 3    
CCDB_DATA_502 Hash       11   7     1
CCDB_DATA_503 Hash     8 8   5 2 2  

Table: Column structure of the ABAP Dictionary Tables of CSA generic Table Stores

Column 1 lists the names of the corresponding transparent tables in the ABAP Dictionary.
Column 2 indicates type and number of key fields
Column 3 to 11 indicate the number of data fields (+key fields) per field length. Data type of the fields is character.

Table CCDB_DATA_501 is recommended for data sets having 1 to 3 key fields. For data sets containing just key – value pairs, you can use table CCDB_DATA_001. If more than 3 columns are required as key fields of a TableStore, you can use e.g. to tables CCDB_DATA_500, CCDB_DATA_502 and CCDB_DATA_503. In these tables a generated Hash_ID acts as key. The real key fields themselves are then created as regular data fields. Any of the data fields can serve as key field. Also, there are no restrictions about the order of fields in the key. Note that in table CCDB_DATA_501, the key fields have to be stored in fields GCOL_01, GCOL_02 or GCOL_03. Again, there are no restrictions about the order of fields. Also, the number of key fields can be lower than 3. Make sure to follow SAP's general recommendation to keep the total length of the primary key is as small as possible.

The screenshots below show the field definitions of tables CCDB_DATA_501 and CCDB_DATA_502 as represented in the ABAP Dictionary:

Example: Table CCDB_DATA_501

Screenshot taken from ABAP transaction SE11

Figure: ABAP Dictionary Definition of table CCDB_DATA_502

Screenshot taken from ABAP transaction SE11

Figure: ABAP Dictionary Definition of table CCDB_DATA_501

Examples for use of CCDB TableStores. Some tables may be missing depending on your system's Support Package level:

CCDB_DATA_500 – DB6 Manager Configuration (S00323)

CCDB_DATA_501 – HANA Encryption (S00328), HANA User 'SYSTEM' status (S00326), Global RFC Destinations (S00064)

CCDB_DATA_502 - HANA Audit Policies (S00327), HTTP Whitelist (UCON) (S00333)

CCDB_DATA_503 – CPI Certificates (S00346)

CCDB_DATA_001 - HANA Version

You can find existing config stores that are using a certain CCDB table by searching the value for the GDATA_TYPE in XML definition filter of the CSA Template Management.

Screenshot from CSA Template Management

Figure: Identifying Config Stores using ABAP Dictionary table CCDB_DATA_500 (Example)

Custom Config Store Definition

Step 3: Create a Custom Config Store Definition

The CCDB content is controlled via the CCDB store Templates. It's available from the FRUN Launchpad via Tile Configuration & Security Analytics Administration. Collector Framework Administration can be opened via the link CSA –Template Management. The application supports custom config store definitions. For all custom definitions the name must begin with Z.

Screenshot from CSA Administration

Figure: Access to Template Management of the CSA - Collector Framework

It contains several entries organized via Store Collector Items (SCI). Each SCI defines the control information about what kind of data should be collected when this specific SCI is processed by the Simple Diagnostic Agent (SDA). The SCI also includes information about applicable software components and internal information used by the Collector framework for ensuring.

Content example taken from a SCI Collector Template (CSA Administration - CSA CF Teamplate Management)

The XML defines

  • The source EC_OB_DEST_ID = “DAGT_DB”. This is the source: using GetCIMObject for DB config.
  • The name of the config store STORE_NAME as it shall appear on the application UI
  • The type of the config store STORE_TYPE                   
  • The database storage: DDIC_TABLE_NAME=CCDB_DATA_502.
  • Name=DataId VALUE= HDB_AUDIT_POLICIES_TEST is the link into the CIMOBJ to identify the SQL going to be used for the extraction.
  • Parameter NAME="CCDB_Generic_Column-01, ~-02, … specifies the order in which fields of the config store are displayed in the CSA User Interface.

In addition, it is required to specify a value for GDATA_TYPE that is unique for each config store, e.g. GDATA_TYPE="GDATA_502_01". The value includes the number <N> of the CCDB_DATA_<N> table with a two-digit counter _<nn>. To find an unused value for the counter, you can check in SE16 th View CCDB_GDATA_DEFV:

Screenshot from ABAP Data Browser (SE16)

Figure: Finding an unused counter of the GDATA_TYPE (required unique attribute in a SCI Template)

Screenshot of the search result in ABAP Data Browser (SE16)

Figure: Result of existing GDATA_TYPES of table CCDB_DATA_500 (example)

SSI Setup

Step 4: Perform SSI Setup

CCDB store template changes are triggered automatically down to the SDA's after changes have been done. The trigger should be completed for all systems within 1 hour.
You can also trigger the SSI manually for each system in the CSA Administration by running the Setup. For triggering an immediate update on the SDA, you have to select and setup systems one by one.

Screenshot from CSA Administration

Figure: Run Setup to trigger store template changes for a system

FAQ

Frequently Asked Questions

 

A1: Choose one of the pre-defined TableStores and specify the key fields as required. FRUN CSA CF will automatically take care that a hash value is created that serves as a unique key.

A3: Key fields of the extracted database table must be mapped to the first fields of the chosen CCDB table. Their order should correspond to the order of key fields in the data source.

A4: No, only data collection with a single SQL statement and an a priori defined where clause is possible.