(DI-1808) Collector for user defined SQL

Collector name: /DVD/MON_CL_COL_SQL

This collector collects information according to the settings set by the user in SQL. There are 2 possible outputs:

• Value - displays it as one KPI value
  
• Table - displays data in a Detail table

To customize settings of this collector:

1. Go to Settings> Collector settings
   
2. Select /DVD/MON_CL_COL_SQL collector

In the following instructions you may choose between an output in a form of a value or a table. For an output in the form of a value see the next title Define your KPI.

For an output in the form of a table see below the title Define your Detail table.

• Define your KPI
  

To define your custom KPI that will collect data from the defined SQL, you must first create a KPI under the selected collector.

1. Double click KPI and click New Entries.
2. Fill all the information. For instructions how to define a KPI please see /wiki/spaces/ITOA/pages/552796294.
   Save the KPI (CTRL + S).
3. Double-click Inputs & rules and select Edit by the Input table /DVD/MON_KPISQL. In this table you define how the KPI values are collected.
4. Click Edit (Ctrl + F1) and then Create new records (F6).
5. In the next dialog enter a name of your custom KPI and fill the SQL stat.
   You must use the column Rule nbr. in the case of a longer select, which doesn't fit into one row and exceeds the length limit. In that case assign each row of the SQL statement a number, starting from 1.
   One KPI can have only one SQL statement. Save the changes.

Now the KPI should be ready to collect data, according to your SQL statement.

• Define your Detail table
  

1. In Collector settings go to Detail tables to define your table. Fill the Detail table name, description and Save.
2. If you want to assign a KPI to a Detail table, select the created Detail table and go to Assignment. If you assign the KPI to a Detail table, you can access this table from Insights cockpit, KPI charts tab, with a right click on the KPI and choosing Detail tables.
3. Go to Inputs & rules and click Edit by the /DVD/MON_KPISQL2 input table.

4. In the edit mode select Create new records to add the Detail table name and a SQL statement, based on which data is saved into the Detail table.

You must use the column Rule nbr. in the case of a longer select, which doesn't fit into one row and exceeds the length limit. Each row of the SQL statement is assigned then a number, starting from 1.
One Detail table can have only one SQL statement. Save the changes.

The structure of the Detail table is set dynamically according to fields that you selected in the SQL statement row. Description of the fields of the Detail table can be changed in Fields under Detail tables in the tree view.

Placeholders usage

In your SQL statement you may use a placeholder.

To complete the setup of data collection, you must create your own profile and assign KPIs. For more information see the chapter /wiki/spaces/ITOA/pages/464847061.