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
    Option (a) Recommended: SQL interface DAGT_DB_EXE (since FRUN 3.00 FP00 and available for HANA databases only)
    Option (b): SQL interface DAGT_DB (since FRUN 2.00 FP00)
  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.

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

OS level folder structure

Figure: SAP Host Agent folder structure

Step 1 Option (a): SQL Interface DAGT_DB_EXE (recommended)

The SQL interfaces uses the SAPHostAgent command 'ExecuteDatabaseOperation' and is available since FRUN 3.00 FP00 together with a minimum agent version 1.48.0 . Compared to the previous CSA SQL interface DAGT_DB it allows a free selection of column names instead of a predefined structure. This simplifies the creation of the SQL statement enormously.

SQL Execution

On the command line the following command can be used to get the output of the defined SQL metric:

./saphostctrl -function ExecuteDatabaseOperation -name "<DataID>" -dbname <DB_NAME> -dbinstance <INSTANCE_ID> -dbtype <DB_SAP_TYPE> -format table -dbhostcheck strict -dberroronval yes [<PARNAME>='<PARVALUE>' <PARNAME2>='<PARVALUE2>' ]

The DataID argument indicates the name of the metric to be performed which is the value of the class "SAP_ITSAMDatabaseOperation". Optionally, the usage of parameter values is supported by 'ExecuteDatabaseOperation'.

Metrics File Format

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

The CSA SQL interface DAGT_DB_EXE uses the classnames "SAP_ITSAMDatabaseOperation" and "SAP_ITSAMSQLStatement".

The SQL statement itself is defined in the definition of class "SAP_ITSAMSQLStatement". In the example below, the SQL statement is configured in plain SQL which is possible if no special character is used which conflicts to the file format. To avoid this problem it is recommended to encode the SQL to base64. In this case the statement type must be set to "Base64".

The SQL statement must not contain any line breaks and/or line feeds and the usage of LOBs is not supported.

After the creation of the cimobjects in the custom file and a successful test on the operation system console using saphostctrl (see 'SQL Execution' above) proceed with 'Step 2: Identify suitable CCDB table'.

 

Example of a custom metric file

cimobject {
  {Name: CreationClassName; Type: String; Value: SAP_ITSAMMetricDefinition}
  {Name: Name; Type: String; Value: Z_CUSTOM_DEMO_HANA_METRIC_DEFINITIONS}
  {Name: Description; Type: String; Value: Customer specific DatabaseConfigStore}
  {Name: Release; Type: String; Value: 01.0000.0000}
  {Name: Date; Type: String; Value: 2020-01-01}
  {Name: Time; Type: String; Value: 00:00:00}

  cimobject {
    {Name: CreationClassName; Type: String; Value: SAP_ITSAMDatabaseOperation}
    {Name: Name; Type: String; Value: DEMO_HDB_CSA_DBOP_1 }
    {Name: SysType; Type: String; Value: DBMS}
    {Name: SysReleaseMin; Type: String; Value: 1.00.109.00.0000000000}
    {Name: SysReleaseMax; Type: String; Value: 9.00.029.99.9999999999}
    {Name: StatementName; Type: String; Value: DEMO_HDB_CSA_DBOP_1.SQL}
   }

   cimobject {
    {Name: CreationClassName; Type: String; Value: SAP_ITSAMSQLStatement}
    {Name: Name; Type: String; Value: DEMO_HDB_CSA_DBOP_1.SQL }
    {Name: CallOrder; Type: int32; Value: 1}
    {Name: Dependencies; Type: String[] sep=|; Value:}
    {Name: Statement; Type: String; Value: SELECT 'Key1' as DEMO_KEY, 'Val1' as DEMO_VAL FROM DUMMY }
   }
}

Notes

  • The delivered column names of the SQL statement must not exceed a maximum length of 30 characters !

Step 1 Option (b): SQL Interface DAGT_DB

The SQL interface DAGT_DB is available since FRUN 2.00 FP00.

 

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"

Metrics File Format

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

The CSA SQL interface DAGT_DB does use the classnames “SAP_ITSAM_DatabaseConfigStore” and “SAP_ITSAMDatabaseConfigSQLStatement”.

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:

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):

ABAP transaction DBACOCKPIT

Figure: SQL Input Query with all adaptations required for CCDB 

Notes

  • For Oracle it is mandatory to close the SQL statement above with the character ";" !
  • For ASE it is mandatory to have the columns in the following order in the select statement

    'store' = '…',
     'name' = '...',
     'resource' = '',
      'system_type' = 'Database',
      'system_id' = '',
      'system_host' = '',
      'value_attributes' = '',
      'value'" = …
  • The delivered column names must not exceed a maximum length of 30 characters !

ABAP transaction DBACOCKPIT

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

ABAP transaction SE11

Figure: ABAP Dictionary Definition of table CCDB_DATA_502

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.

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.

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.

Note
Since FRUN 3.00 FP00 you can also use the Template Management Wizard to create a collector item definition. By default, its generic definition consists of a simple name / value pair but you can manually adapt it regarding the custom SQL statement.

SCI Collector Template

The XML defines

  • The source EC_OB_DEST_ID specifies the interface to be used:
    - see Step 1 Option (a): "DAGT_DB_EXE" - The new CSA SQL interface available since FRUN 3.00 FP00
    - see Step 1 Option (b): “DAGT_DB” - The previous CSA SQL interface 
  • 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. The VALUE contains the field mapping in the format "<FIELDROLE>-<DELIVERED_COL_NAME>-<DB_COL_NAME>". <FIELDROLE> is either 'K' or 'D' for key or data field. Please note that incorrect key columns can lead to a duplicate record error.

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> between 01-79. (Others are reserved SAP, do not use them). To find an unused value for the counter, you can check in SE16 th View CCDB_GDATA_DEFV:

ABAP Data Browser

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

search result in ABAP Data Browser

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.

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.

A2: No, the number of key fields can be less

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.