(DI-2205) Create a Detail Table for a Custom KPI Assigned to the Collector for a User Defined SQL

This chapter contains instructions on how to create a Detail table for a custom KPI assigned to the Collector for a User Defined SQL.

What is a Detail table, is explained in the chapter (DI-2205) KPI Details Displayed in a Detail Table.

Prerequisites

• Created custom KPI assigned to the Collector for a User Defined SQL. To check how to create this type of custom KPI, see the chapter (DI-2205) Create a KPI through a SQL Select Statement.
  

Step 1: Create a Detail table for a custom KPI assigned to the Collector for a User defined SQL

To define your custom Detail table that collects data according to the specified SQL statement:

1. Go to Settings > Collector settings.
2. Select the Collector for a User Defined SQL (/DVD/MON_CL_COL_SQL) and double-click Detail tables.
3. Click New entries.
   

4. Fill in Detail table name and its description.

   You can create a custom Detail table, only if it starts with the capital letters Z* or Y*.

   If you label the Detail table the same way as a structure or a table type existing in DDIC, then the field description of the Detail table will be retrieved from the DDIC structure.
   In the other case, when the Detail table name isn't included in DDIC, then the fields of the Detail table are defined by the customs collector (technical names of the fields are used as their descriptions).
   

5. Save (CTRL + S).
6. Double-click Inputs & rules and select Edit next to the Input table /DVD/MON_KPISQL2 (table for a Detail table).
   
   
7. Click Edit (Ctrl + F1) and then  Create new records (F6). 
   
   

8. In the next dialog enter the name of your custom Detail table and fill in the SQL statement.
   In the case the SQL statement is longer than 255 characters, it must be divided into multiple rows starting from the 1st. Use the column Rule nbr. to number the parts of the SQL statement.
   The fields of the custom Detail table are created dynamically according to the columns you specified in the SQL statement.
   

   Please note that one Detail table can have only one SQL statement.
   

   Placeholders usage

   In your SQL statement, you may use a placeholder. These placeholders allow you to create a more generic SQL statement. By default, these placeholders are replaced by relevant values directly in the SQL statement when SQL Collector is executed. 
   

   Placeholder	Returning value of	Usage example
   <LASTRUN>	UTC Timestamp of the last run of the Collector job (YYYYMMDDHHMMSS)	WHERE timestamp_from >= '<LASTRUN>'
   <TIMESTAMP>	UTC Timestamp of the current Collector job execution (YYYYMMDDHHMMSS)	WHERE timestamp_to <= '<TIMESTAMP>'
   <DATE_FROM>	Date of the last run of the Collector job (YYYYMMDD)	Low range: ( date > '<DATE_FROM>' OR ( date = '<DATE_FROM>' AND time > '<TIME_FROM>' ) )
   <TIME_FROM>	Time of the last run of the Collector job (HHMMSS)	Low range: ( date > '<DATE_FROM>' OR ( date = '<DATE_FROM>' AND time > '<TIME_FROM>' ) )
   <DATE_TO>	Date of the current Collector job execution (YYYYMMDD)	High range: AND ( date < '<DATE_TO>' OR ( date = '<DATE_TO>' AND time <= '<TIME_TO>' ) )
   <TIME_TO>	Time of the current Collector job execution (HHMMSS)	High range: AND ( date < '<DATE_TO>' OR ( date = '<DATE_TO>' AND time <= '<TIME_TO>' ) )
   <SID>	System ID of the monitored system	WHERE mon_sid_field = '<SID>'
   <FIRST_START>	Flag whether collector job executed for the first time	WHERE ib_first_start = 'X'
   <MANDT>	Client ID	WHERE sy-mandt = '<MANDT>'
   <SERVER>	Name of the server. Not allowed to use in SQL statement for detail table.	WHERE server_field = '<SERVER>'
   <HOST>	Host name. Not allowed to use in SQL statement for detail table	WHERE host_field = '<HOST>'

   Please note that placeholders <SERVER> and <HOST> are not allowed in SQL statements for detail tables. 

   Bind variables

   If supported on the database, you might enable the bind variables usage for SQL statements. More information can be found in the section (DI-2205) Collector for user defined SQL.

9. Save changes (CTRL + S).

Step 2: Assign the Detail table to a custom KPI

1. Select the created Detail table and double-click Assignment. (Go to Settings > Collector settings > Detail tables).
2. Select New Entries.
3. Fill in your custom KPI name, which you want to assign to the custom Detail table.
   
4. Save (CTRL + S).
   
   

The Detail table is now assigned to your custom KPI. For information on how to display Detail tables, see the chapter (DI-2205) KPI Details Displayed in a Detail Table.

The same procedure for the custom KPI assigned to a custom collector is described in the chapter Create a Custom Detail Table for a Custom Collector.