(DI-2105) How to Create a KPI Through Defining a SQL SELECT Statement
Step 1: Define a custom KPI
Define the custom KPI as is described in the chapter (DI-2105) Define a Custom KPI. After you created the KPI definition, return to this page to complete the creation of a custom KPI.
During the definition fill in the field KPI collector with the technical name of the SQL collector – /DVD/MON_CL_COL_SQL.
Step 2: Choose how data is collected into the custom KPI through a SQL SELECT statement
- From the main screen go to the Settings > Collector settings (or transaction /DVD/MON_COL).
- Select the collector /DVD/MON_CL_COL_SQL. (You may use for searching SHIFT+F7)
- Double-click Inputs & rules and select the Edit next to the Input table /DVD/MON_KPISQL1 (table for KPI).
- Click Edit (Ctrl + F1) and then Create new records (F6).
In the next dialog, enter the name of your custom KPI 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.Please note that one KPI can include only one SQL statement.
Placeholder usageIn your SQL statement, you may use a placeholder. The 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 executed.
Placeholder Returning value of <LASTRUN> Last run <TIMESTAMP> Timestamp <SID> System ID <FIRST_START> First start <SERVER> Server <HOST> Host
<DATE_FROM> Date from <TIME_FROM> Time from <DATE_TO> Date to <TIME_TO> Time to <MANDT> Client ID 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 Bind variables.
- Save the changes (CTRL + S)
The next step is to specify the monitoring profile of a KPI, as described in the chapter (DI-2005) Define a Monitoring Profile for a KPI.
For the Collector for user-defined SQL, it is possible to create a Detail table, which displays further KPI details. More information can be found in the chapter (DI-2005) Create a Detail Table for a Custom KPI Assigned to the Collector for a User Defined SQL.
Additionally, you may find an example in (DI-2005) Example: Number of Inactive Objects on the System.