(DI-2205) How to Create a KPI Through Defining a SQL SELECT Statement

Owned by DVD ROBOT
May 02, 2022
2 min read

In this chapter, you can find instructions on how to create a KPI by defining a SQL SELECT statement. You might use a (DI-2205) Wizard for KPI Creation based on SQL Statement.

You may find an overview of the complete process of Custom KPI creation under The Complete Process of Custom KPI Creation and Data Collection.

To create your custom KPI that collects data according to the specified SQL statement, follow these steps:


Step 1: Define a custom KPI

Define the custom KPI as is described in the chapter (DI-2205) 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

  1. From the main screen go to the Settings > Collector settings (or transaction /DVD/MON_COL).
  2. Select the collector /DVD/MON_CL_COL_SQL. (You may use for searching SHIFT+F7)
  3. Double-click Inputs & rules and select the Edit next to the Input table /DVD/MON_KPISQL1 (table for KPI).

  4. Click Edit (Ctrl + F1) and then Create new records (F6). 

  5. In the next dialog, enter the name of your custom KPI and fill in the SQL statement.

    Please note that one KPI can include only one SQL statement. In the case the SQL statement is longer than 128 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.


    Placeholder usage

    In 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 is executed. 

    PlaceholderReturning 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 (DI-2205) Collector for user defined SQL.

  6. Save the changes (CTRL + S)

  7. The next step is to specify the monitoring profile of a KPI, as described in the chapter (DI-2205) 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-2205) 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-2205) Example: Number of Inactive Objects on the System.