Generic - Object relationship SQL extractor

This topic describes how to configure and use object relationship SQL extractor, that allows to describe custom relationships among entities in TrueSight Capacity Optimization.

Note

This ETL only works in the ASSERT mode. Running this ETL connector creates a hierarchy rule; for more information, see Adding-and-viewing-Hierarchy-rules.

Integration steps

To integrate TrueSight Capacity Optimization with the Object relationship SQL extractor, perform the following task:

  1. Navigate to Administration > ETL & SYSTEM TASKS > ETL tasks.
  2. In the ETL tasks page, click Add > Add ETL under the Last run tab.

  3. In the Add ETL page, set values for the following properties under each expandable tab.

    Note

    Basic properties are displayed by default in the Add ETL page. These are the most common properties that you can set for an ETL, and it is acceptable to leave the default selections for each as is.

    Basic properties

    Note

    To view or configure Advanced properties, click Advanced. You do not need to set or modify these properties unless you want to change the way the ETL works. These properties are for advanced users and scenarios only.

    Advanced properties

  4. Click Save.
    You return to the Last run tab under the ETL tasks page.
  5. Validate the results in simulation mode: In the ETL tasks table under ETL tasks > Last run, locate your ETL (ETL task name), click run_etl.png to run the ETL.
    After you run the ETL, the Last exit column in the ETL tasks table will display one of the following values:
    • OK: The ETL executed without any error in simulation mode.
    • WARNING: The ETL execution returned some warnings in simulation mode. Check the ETL log.
    • ERROR: The ETL execution returned errors and was unsuccessful. Edit the active Run configuration and try again.
  6. Switch the ETL to production mode: To do this, perform the following task:
    1. In the ETL tasks table under ETL tasks > Last run, click the ETL under the Name column.
    2. In the Run configurations table in the ETL details page, click edit icon.png to edit the active run configuration.
    3. In the Edit run configuration page, navigate to the Run configuration expandable tab and set Execute in simulation mode to No.
    4. Click Save.
  7. Locate the ETL in the ETL tasks table and click run_etl.png to Run it, or schedule an ETL run.
    After you run the ETL, or schedule the ETL for a run, it will extract the data form the source and transfer it to the TrueSight Capacity Optimization database.

Configuring the SQL query

The Entities query must list all entities involved in the CMDB transaction:

The Relationships query must provide all parent/child relationships among entities:

Examples

Sample SQL query for Entities and Relationships (without lookup fields)

Suppose we have two tables, one containing the entity details and other contains the relationships between those entities.

Table: Entities
CREATE TABLE ENTITIES (
ENTITY_NAME VARCHAR2(50) NOT NULL,
DS_ENTITY_NAME VARCHAR2(100) NOT NULL,
ENTITY_CATEGORY VARCHAR2(20) NOT NULL,
ENTITY_TYPE VARCHAR2(20) NOT NULL
);

gen_obj_sql_etl_entity_table_without_lookup.png

Query: Entities
Select ENTITY_NAME as ENTNM, DS_ENTITY_NAME as
DS_ENTNM, ENTITY_CATEGORY as ENTCATNM, ENTITY_TYPE as ENTTYPENM from ENTITIES
Table: Relationships
CREATE TABLE RELATIONSHIPS (
ENTITY_NAME VARCHAR2(50) NOT NULL,
DS_ENTITY_NAME VARCHAR2(100) NOT NULL,
ENTITY_CATEGORY VARCHAR2(50) NOT NULL,
PARENT_DS_ENTITY_NAME VARCHAR2(100) NOT NULL,
PARENT_ENTITY_CATEGORY VARCHAR2(20) NOT NULL
);

gen_obj_sql_etl_relationship_table_without_lookup.png

Query: Relationships
Select ENTITY_NAME as ENTNM, DS_ENTITY_NAME as DS_ENTNM, ENTITY_CATEGORY
as ENTCATNM, PARENT_DS_ENTITY_NAME as DS_ENTNMPARENT, PARENT_ENTITY_CATEGORY as ENTCATNMPARENT from RELATIONSHIPS

This will create hierarchy as follows:

gen_obj_sql_etl_hierarchy_without_lookup.png

The entities lookup will be as follows:

gen_obj_sql_etl_bs_without_lookup.png

gen_obj_sql_etl_app_without_lookup.png

gen_obj_sql_etl_server_01_without_lookup.png

gen_obj_sql_etl_server_02_without_lookup.png

Sample SQL query for Entities and Relationships (with lookup fields)

Suppose we have two tables, one containing the entity details and other contains the relationships between those entities.

Table: Entities
CREATE TABLE ENTITIES (
ENTITY_NAME VARCHAR2(50) NOT NULL,
DS_ENTITY_NAME VARCHAR2(100) NOT NULL,
ENTITY_CATEGORY VARCHAR2(20) NOT NULL,
ENTITY_TYPE VARCHAR2(20) NOT NULL,
STRONGLOOKUPFIELDS VARCHAR2(100) NOT NULL
);

gen_obj_sql_etl_entity_table_with_lookup.png

Query: Entities
Select ENTITY_NAME as ENTNM, DS_ENTITY_NAME as DS_ENTNM,
ENTITY_CATEGORY as ENTCATNM, ENTITY_TYPE as ENTTYPENM, STRONGLOOKUPFIELDS from ENTITIES
Table: Relationships
CREATE TABLE RELATIONSHIPS (
ENTITY_NAME VARCHAR2(50) NOT NULL,
DS_ENTITY_NAME VARCHAR2(100) NOT NULL,
ENTITY_CATEGORY VARCHAR2(50) NOT NULL,
PARENT_DS_ENTITY_NAME VARCHAR2(100) NOT NULL,
PARENT_ENTITY_CATEGORY VARCHAR2(20) NOT NULL,
STRONGLOOKUPFIELDS VARCHAR2(100) NOT NULL
);

gen_obj_sql_etl_relationship_table_with_lookup.png

Query: Relationships
Select ENTITY_NAME as ENTNM, DS_ENTITY_NAME as DS_ENTNM,
ENTITY_CATEGORY as ENTCATNM, PARENT_DS_ENTITY_NAME as DS_ENTNMPARENT, PARENT_ENTITY_CATEGORY as ENTCATNMPARENT from RELATIONSHIPS

This will create hierarchy as follows:

gen_obj_sql_etl_hierarchy_with_lookup.png

The entities lookup will be as follows:

gen_obj_sql_etl_bs_with_lookup.png

gen_obj_sql_etl_app_with_lookup.png

gen_obj_sql_etl_server_01_with_lookup.png

gen_obj_sql_etl_server_02_with_lookup.png


Related topics

Using-ETL-datasets

Generic-CSV-file-parser

Developing-custom-ETLs

Dataset-reference-for-ETL-tasks

Horizontal and Vertical datasets

Viewing-datasets-and-metrics-by-dataset-and-ETL-module


 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*