How to enhance CCDB with database content from custom SQL

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

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:

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

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 !

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

Figure: ABAP Dictionary Definition of table CCDB_DATA_502

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.