ETL - Extract Transform Load

More about ETL Setting up an ETL configuration ETL examples, troubleshooting, and tips   One of the ways to integrate with external systems is using the Extract, Transform, Load (ETL) system. The ETL system creates databases that can be accessed directly by 3rd party tools and solutions. It also allows scheduled execution of transformation scripts on the IT Advisor server. Together this gives the ability to extract and enter data on the ITA server.   Based on the ETL system, it is possible to develop custom solutions, integrating ITA with a broad range of data sources.   ETL can be used in 2 ways: Importing Data in to and Exporting Data from IT Advisor.   ITA_ETL_intro_1_360007339757.png   Importing data   Importing data into IT Advisor is used whenever data must be provided to ITA. The data is being imported via the ETL Import Database.  There are 2 ways of importing data. One where "Data is Ready" and one where "Data needs change" before being used in ITA. Data is Ready When data is ready the only thing that must be done is setting up the integration between the external system providing data and ITA. This is done at database level. The integration is configured using the server configuration interface and can be found here Data needs change When data needs to be changed/modified before it is used inside ITA this can be done using a transformation or a set of transformations called a job. More information here Once transformation(s) are in place the integration is configured using the server configuration interface and can be found here   Exporting data   Exporting data from IT Advisor is used when ever data is used outside ITA. The data is exported using the ETL Export Database. The data exported from IT Advisor cannot be changed by transformations inside the ITA server. If transformation is needed this must be done outside the server in a separate setup. Setting up the export is done using the server configuration interface as described here   ITA_ETL_intro_2_360007339757.png   Upgrading ETL external system integration   No files (or databases) will be deleted when upgrading/restoring an ITA system. Upgrade of the ETL system is a part of the general ITA upgrade.   When upgrading an existing solution running ETL for importing and/or exporting data to ITA a couple of guidelines are:   Both import and export databases are viewed as "api-like" interface to the system. At any point in time the data in the import and/or export database represents the currently best known state of the system. The ETL databases do not contain the sole instance of any piece of data.   Database   This means that it is always safe to drop any ETL database and recreate it without the risk of losing data.   Since no data is stored in the ETL database, it is highly recommended to drop the databases and recreate them rather than moving them. This will allow databases to follow potential new database schema(s). To use the old databases, these must be moved manually which is not recommended.   Transformations   When upgrading from previous versions the old transformation files will be moved into the folder  /data/pentaho_backup . The transformation files must manually be moved from  /data/pentaho_backup  into a potential new folder structure (described below).   Restoring an old backup will place transformation files in the same structure as they were in the backed up system. Please note this might not be the correct folder structure since the structure has changed between versions.   Note: When moving/copying files into the new folder structure make sure to preserve the ownership of the folders in order for the future script to be added and executed.   The transformation files must be placed in their respective folders on the ITA server. The transformation files folder structure on the ITA server is  /data/pentaho/export  or  /data/pentaho/import . You can find more information about the ETL Transformation here.

The ETL Export database is based on a predefined schema that is not to be changed but populated with data.   The schema is graphically represented in the ETL Export Database Schema (attached). Please make sure to understand the Database Information State for the ETL import database. The information state is important since it is used for "bookkeeping" of data in the data base.   The schema is graphically represented in the ETL Export Database Schema attached below.   Description of Tables, Relations and Classifications Table: SCHEMA_VERSION The SCHEMA_VERSION table contains only one record which is used to indicate the version of this database schema. The current schema-version is 1.1.10.   Version ITA version Description 1.0.0   Initial schema design for the ETL feature 1.1.3 ITA 9.0.0 Added columns to configuration_item table: - ci_width - ci_height - ci_depth - ci_weight - ci_design_limit   ITA 9.0.6 Added columns to configuration_item_power_path table: - pp_power_receptacle_type - pp_power_receptacle_type_description - pp_power_redundant_id character   ITA 9.1.1 Added job_status table 1.1.4 ITA 9.1.3 Shortened few columns in breaker_sensor_relation and relation_endpoint_property tables Added columns to configuration_item table: - ci_cage_label - ci_cage_uuid Added audit_log_* and audit_log_configuration_item tables 1.1.5 ITA 9.1.6 Added columns to configuration_item_power_path table: - pp_breaker_panel_occupied_positions - pp_breaker_panel_id Added breaker_panel table 1.1.6 ITA 9.1.8 Added columns to breaker_panel table: - panel_breaker_derating - panel_breaker_rating 1.1.7 ITA 9.1.9 Added column to work_order_network_connection_task table: - fan_out_outlet_number 1.1.8 ITA 9.2.1 Added columns to configuration_item table: - ci_external_bottom_distance - ci_external_bottom_u_positions - ci_external_top_distance - ci_external_top_u_positions - ci_external_overhead_mounting 1.1.9 ITA 9.2.3 Added columns to configuration_item table: - ci_cable_tray_color - ci_cable_tray_layer_name - ci_cable_tray_material - ci_cable_tray_max_cable_count - ci_cable_tray_max_weight_per_meter - ci_cable_tray_side_walls_height - ci_cable_tray_type - ci_cable_tray_width 1.1.10 ITA 9.3.0 Added columns to configuration_item table: - ci_rack_max_load_weight The schema version is used by the IT Advisor server for validation of the ETL setup.    Table: CONFIGURATION_ITEM The table will contain a complete list of equipment in the data center model. This represents IT Advisor's view of the data center.   CONFIGURATION_ITEM  Field Datatype Description Sampledata ci_id varchar(36) Unique identifier of item (primary key). This is a UUID. "550e8400-e29b-41d4-a716-446655440000" ci_type varchar(64) Specifies the type of this item in a readable form. Server ci_parent_id varchar(36) Reference to the item which is the parent of this item. Represents the physical relationship location-room-row-cage-rack-server.    ci_manufacturer varchar(255) Manufacturer of equipment as known by the IT Advisor model   ci_model_name varchar(255) The equipment model information/name   ci_part_number varchar(255) Part number of equipment   ci_name varchar(255) Name of equipment   ci_description varchar(255) Description for equipment   ci_barcode varchar(255) Single barcode for equipment   ci_install_date date Date of installation in the data center   ci_u_position int If the piece of equipment is rack mounted, this will contain the top U position of the equipment. 31 ci_u_height int If the equipment is mounted in a rack, this will contain the height of the equipment measured in (rack) units.   ci_mounting varchar(64) For rack mounted equipment, this will show whether the equipment is mounted in the front, back or sides of the rack   ci_width int Width of the equipment in millimeters. Available for free standing and rack-mounted equipment.   ci_height int Height of the equipment in millimeters. Available for free standing and rack-mounted equipment.   ci_depth int Depth of the equipment in millimeters. Available for free standing and rack-mounted equipment.   ci_weight double Weight of equipment in grams   ci_last_log_entry_time long Timestamp indicating when a change related to this configuration item has been recorded   ci_last_log_user_id varchar(64) User responsible for the last change recorded on this configuration item   ci_design_limit double Design limit of the equipment in watts if is set   ci_power_draw double If a power draw (Adjusted Nameplate) is known it will be stored here   ci_max_power_draw double If Manufacturer's Nameplate is set   ci_power_redundancy varchar(16) Any known redundancy information will be stored in this field   ci_status varchar(255) Indicate the status of an equipment in the change module. Possible values are "EXISTING", "PENDING DELETE", "PENDING MOVE", "PENDING ADD", "PLANNED"   ci_logic_name varchar(255) The logic name of the equipment   ci_rack_uuid varchar(36) If the equipment is placed in a rack, this field will contain the UUID of the rack "550e8400-e29b-41d4-a716-446655440000" ci_rack_label varchar(255) If the equipment is placed in a rack, this field will contain the name of the rack   ci_room_uuid varchar(36) This field will contain the UUID of the room in which the equipment is placed "550e8400-e29b-41d4-a716-446655440000" ci_room_label varchar(255) Contains the name of the room where the equipment is placed.   ci_cage_uuid varchar(36) If the equipment is placed in a cage, this field will contain the UUID of the cage "550e8400-e29b-41d4-a716-446655440000" ci_cage_label varchar(255) If the equipment is placed in a cage, this field will contain the name of the cage   ci_row_label varchar(255) Contains the label of the row in which the equipment is placed – if any   ci_location varchar(255) The "complete" location string that indicates the placement of the equipment.   ci_serial_number varchar(255) If a Serial number is provided on the device in the properties section   ci_floor_coordinate_x varchar(255) The equipment floor tile X coordinate A ci_floor_coordinate_y varchar(255) The equipment floor tile Y coordinate 1 ci_subtype varchar(255) The equipments subtype Server ci_external_bottom_distance integer If rack contains bottom-mounted external U-positions, this column will contain distance (in millimeters) from bottom of the rack to external enclosure 500 ci_external_bottom_u_positions integer If rack contains bottom-mounted external U-positions, this column will contain number of U-positions 3 ci_external_top_distance integer If rack contains top-mounted external U-positions, this column will contain distance from rack to external enclosure or from ceiling to external enclosure (see ci_external_overhead_mounting) 200 ci_external_top_u_positions integer If rack contains top-mounted external U-positions, this column will contain number of U-positions 4 ci_external_overhead_mounting integer If rack contains top-mounted external U-positions, this column will inform where external enclosure is mounted 1 - enclosure is mounted above the rack, or 0 - enclosure is mounted under ceiling ci_cable_tray_color varchar(255) CableTraySection or VerticalCableTraySection only: color of the section "RGB {255, 64, 255}" ci_cable_tray_layer_name varchar(255) CableTraySection or VerticalCableTraySection only: name of the layer the section belongs to "Primary network" ci_cable_tray_material varchar(255) CableTraySection or VerticalCableTraySection only: material the section is made of "METAL" "PLASTIC" ci_cable_tray_max_cable_count integer CableTraySection or VerticalCableTraySection only: maximum number of cables that the section can hold 50 ci_cable_tray_max_weight_per_meter double CableTraySection or VerticalCableTraySection only: maximum weight (kilograms/meter) that the section can support 100 ci_cable_tray_side_walls_height double CableTraySection or VerticalCableTraySection only: height of the section's side walls (in millimeters) 20 ci_cable_tray_type varchar(255) CableTraySection or VerticalCableTraySection only: type of the section "LADDER" "SOLID" "PERFORATED" ci_cable_tray_width double CableTraySection or VerticalCableTraySection only: width of the section (in millimeters) 600 ci_rack_max_load_weight double     state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.   Table: CONFIGURATION_ITEM_PROPERTY In the IT Advisor data model, a DataCenterItem can have DataCenterProperties associated. These are listed in the CONFIGURATION_ITEM_PROPERTY table.   CONFIGURATION_ITEM_PROPERTY Field Datatype Description Sampledata ci_id varchar(36) Identifier of item. The ci_id field is a foreign key to CONFIGURATION_ITEM  "550e8400-e29b-41d4-a716-446655440000" cip_name varchar(255) The name of the property. "servicetag" cip_value varchar(255) Value of property. "KN20623BY9" cip_type varchar(255) Specifies the type of this property.   cip_class varchar(64) Specifies the class of this property.   state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.     Table: CONFIGURATION_ITEM_TAG Any tags associated with the items in CONFIGURATION_ITEM, are exported to CONFIGURATION_ITEM_TAG. No information about relationship between tags (grouping) is exported .  CONFIGURATION_ITEM_TAG       Field Datatype Description Sampledata ci_id varchar(36) Identifier of item. The ci_id field is a foreign key to CONFIGURATION_ITEM  "550e8400-e29b-41d4-a716-446655440000" cit_tag varchar(255) The name of the tag. "sales" cit_description varchar(255) Descriptionof property. "Equipment supporting the sales team" state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.     Table: CONFIGURATION_ITEM_POWER_PATH Any power path associated with the items in CONFIGURATION_ITEM, are exported to CONFIGURATION_ITEM_POWER_PATH.   CONFIGURATION_ITEM_POWER_PATH Field Datatype Description Sampledata pp_id varchar(36)   "09899e3400-e29b-41d4-a716-89373538282" pp_average_power varchar(255)   "#L1:103.92304845413264 #L2:NaN #L3:72.74613391789285," pp_breaker_derating double   "0.8" pp_breaker_label varchar(36)   "label-24" pp_breaker_module_number Integer     pp_breaker_module_size Integer     pp_breaker_panel_circuit_number Integer   "12" pp_breaker_size Integer   "3" pp_breaker_poles Integer   "3" pp_breaker_power_phase varchar(16)   "L1_L2_L3" pp_breaker_rating double   "30.0" pp_breaker_panel_label varchar(255)   "Panel A" pp_breaker_panel_id varchar(36)     pp_breaker_panel_occupied_positions varchar(255)     pp_power_receptacle_type varchar(255)     pp_power_receptacle_type_description varchar(255)     pp_power_redundant_id varchar(36)     ci_id varchar(36) Identifier of item. The ci_id field is a foreign key to CONFIGURATION_ITEM  "550e8400-e29b-41d4-a716-446655440000" ci_name varchar(255) Name of the configuration_item "PDU 2" ci_type varchar(64) Type of configuration_item "RackMountPdu" ci_parent_X_id varchar(36) Identifier of the X level item in power path. X goes from 1 to max. 11 (depending on what number the chosen item is in the power path) "550e8400-e29b-41d4-a716-446655445090" ci_parent_X_name varchar(255) Name of the X level item in power path. X goes from 1 to max. 11 (depending on what number the chosen item is in the power path) "UPS-1" ci_parent_X_type varchar(64) Type of the X level item in power path. X goes from 1 to max. 11 (depending on what number the chosen item is in the power path) "Ups" pp_peak_power varchar(255) Peak power of the item in the power path "#L1:103.92304845413264 #L2:NaN #L3:72.74613391789285," pp_redundancy varchar(16)     pp_predicted_peak_power varchar(255) Predicted peak power of the item in the power path "#L1:110.76534433413267 #L2:NaN #L3:82.74613391789285," pp_peak_amps varchar(255) Measured peak amps of the item in the power path "#L1:10.392304845413264 #L2:NaN #L3:7.274613391789285," state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.     Table: BREAKER_PANEL   CUSTOM_PROPERTY       Field Datatype Description Sampledata panel_id varchar(36)   "16c2f1f9-6db3-481a-890a-c61a7ec7db84" parent_id varchar(36)   "fd764575-3091-4a73-a8c4-22c0b427f708" panel_label varchar(255)   "Breaker Panel:1" panel_size integer   24 panel_type varchar(255)   "Generic" panel_layout varchar(255)   "Phase grouping:no; Two rails" panel_used_positions integer   15 panel_contiguous_free_positions integer   6 panel_breaker_rating double   400 panel_breaker_derating double   0.8 state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.     Table: CUSTOM_PROPERTY Any CustomProperties associated with the equipment in the CONFIGURATIONS_ITEM table, are stored in the CUSTOM_PROPERTY table.    CUSTOM_PROPERTY       Field Datatype Description Sampledata ci_id varchar(36) Identifier of item. The ci_id field is a foreign key to CONFIGURATION_ITEM   "550e8400-e29b-41d4-a716-446655440000" cp_name varchar(255) The name of the property. "servicetag" cp_value text Value of property. "KN20623BY9" cp_type varchar(255) This value indicates the type of the property value   state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.   Table: SWO_KPI IT Advisor creates many different KPIs (key performance indicators) for the datacenter when running. The newest KPIs are exported to the SWO_KPI table.    SWO_KPI Field Datatype Description Sampledata kpi_id varchar(255) Identifier of KPI (primary key). "550e8400-e29b-41d4-a716-446655440000" ci_id varchar(36) Identifier of item. The ci_id field is a foreign key to CONFIGURATION_ITEM   "550e8400-e29b-41d4-a716-446655440000" kpi_serie_type varchar(64) Indication of the type of KPI (ETL Export Database) "total_free_u_positions" kpi_value_type varchar(64) Type of the value for the KPI 217 kpi_value varchar(255) The measured/calculated value   state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.   Inside IT Advisor, KPI have a history – this history is NOT reflected in the export database – only the newest known values are currently available.  Table: EXTERNAL_SYSTEM_RELATION If a DataCenterItem is associated with a physical (or virtual) device through an external system, the link is represented by a record in this table.    EXTERNAL_SYSTEM_RELATION       Field Datatype Description Sampledata ci_id varchar(36) Identifier of item. The ci_id field is a foreign key to CONFIGURATION_ITEM  "550e8400-e29b-41d4-a716-446655440000" esr_system_name varchar(300) The name of the external system through which this relation is made. "ETL Excel Import" esr_system_type varchar(63) The type of the external system. "ETLImport" esr_item_id varchar(255) The ID of the external item that represents the foreign end of the relation(association). "KN20623BY9" state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.     Table: CONFIGURATION_ITEM_RELATION Relationship between configuration items are modeled in the configuration_item_relation table. One example of a relations is power cables.    CONFIGURATION_ITEM_RELATION       Field Datatype Description Sampledata cir_id varchar(36) Unique id of relation "550e8400-e29b-41d4-a716-446655440000" ci_id_a varchar(36) Id of first endpoint of the relation "31925392-e29b-41d4-a716-446655440000" ci_id_b varchar(36) Id of second endpoint of the relation "22909021-e29b-41d4-a716-446655440000" cir_type varchar(64) Type of the relation a_connected_to_b state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.   The type of relation is not classifies.  Table: CONFIGURATION_ITEM_RELATION_CUSTOM_PROPERTY Any CustomProperties associated with the relation in CONFIGURATION_ITEM_RELATION table are stored in the CONFIGURATION_ITEM_RELATION_CUSTOM_PROPERTY table.    CONFIGURATION_ITEM_RELATION       Field Datatype Description Sampledata ci_id varchar(36) Unique id to retrieve the custom property name and value in CUSTOM_PROPERTY table   cir_id varchar(36) Unique id of relation "550e8400-e29b-41d4-a716-446655440000" state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.     Table: RELATION_PROPERTY Each relation between configuration items may have a number of associated properties containing information specific for that relation. One example is the power and phase information for power-cables connecting equipment.    RELATION_PROPERTY       Field Datatype Description Sampledata cir_id varchar(36) Unique id of relation "550e8400-e29b-41d4-a716-446655440000" rp_name varchar(255) Name of property "powerPhase" rp_value varchar(255) Value of property "L1-L2" rp_type varchar(16) Type of property "powerPhase" state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.   The relation property type (rp_type) is not classified.   Table: RELATION_ENDPOINT_PROPERTY If a relation has special information associated with the "endpoints" of the relations (this is the case for network cables), the endpoint related information is exported to the relation_endpoint_property table.    CONFIGURATION_ITEM_RELATION       Field Datatype Description Sampledata cir_id varchar(255) Unique id of the relation "0583d972-2ce5-499a-9a61-1ec27798925e##10#RJ45_REGULAR#true" ci_id varchar(36) Unique id of the endpoint that this property is related to "31925392-e29b-41d4-a716-446655440000" cir_endpoint varchar(1) Indentifies the endpoint "A" and "B" for properties of network connection endpoints rep_name varchar(21) Name of the property "portSpeed" rep_value varchar(255) Value of the property "1Gbps" rep_type varchar(64) Type of the property "networkSpeed" state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.     The relation endpoint property type is not classified.  Table: PORT_CONFIGURATION   PORT_CONFIGURATION Field Datatype Description Sampledata ci_id varchar(255) Identifier of item. The ci_id field is a foreign key to CONFIE|RATION_ITEM "123882d5-cebe-4563-a1fc-b7855c7e423e" connector_type varchar(255) Connector type for the port configuration "RJ45_REGULAR" module_name varchar(255) Module name "Module A" start_port integer Start port number 1 port_count integer Number of ports in the configuration 48 port_speed varchar(255) Transmission speed for the port configuration "1 Gbps" state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.     Cages and Rows In the internal IT Advisor model, neither rows nor cages are part of the equipment parent-child hierarchy.  When exporting configuration items from ITA, this is hidden from the user, and the natural parent-child hierarchy is build. The relation is realized by having each configuration item reference its parent through the parent_id field.  The area of cages are fetched from the ITA web services and associated with the configuration items representing the individual cages, in the table configuration_item_property. The area-property is named CageArea, and the area is expressed in whatever unit the web service returns.   Table: FREE_SPACE NOTE: FREE_SPACE is currently not being used. It is here for possible future usage.   FREE_SPACE Field Datatype Description Sampledata ci_id varchar(255) Identifier of item. The ci_id field is a foreign key to CONFIGURATION_ITEM  "550e8400-e29b-41d4-a716-446655440000" free_space varchar(16) Free space calculated   ci_name varchar(255) Name of item   ci_type varchar(64) Type of item   state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.     Table: CUSTOMER_DATA   CUSTOMER_DATA Field Datatype Description Sampledata customer_id varchar(255) Unique id of the customer "000654" customer_company varchar(255) Company of the customer "Schneider Electric" customer_contact varchar(255) Main contact of customer "Mr Black" customer_type varchar(36) Type of customer   customer_department varchar(255) Department where customer contact is registered "000654" customer_phonenumber varchar(36) Phone number of customer "111-111-1111" state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.     Table: CUSTOMER_RELATION   CUSTOMER_RELATION Field Datatype Description Sampledata ci_id varchar(36) Unique id of the relation "550e8400-e29b-41d4-a716-446655440000" customer_id varchar(36) Id of the customer "000654" power_sold double Power in kW that is sold by contract to the customer "4.2" reserved_for varchar(255) Reservation of area made for a customer "Future customer" state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.     Table: WORK_ORDER   WORK_ORDER Field Datatype Description Sampledata wo_id varchar(40) Unique id of the work order "029882d9-ccbe-4233-a1fc-b8a94050e3bb" assigned_to varchar(64) Name of assignee of the work order "Mr Black" comment varchar(3000) Comment made on work order "This must be done!" completed_date bigInt Date in epoch format "1362608169502" created_date bigInt Date in epoch format "1362608169502" needed_by_date bigInt Date in epoch format "1362608169502" order_number Integer Number of work order "54" priority Integer Priority of work order "2" project_code varchar(255) Project code name that work order is part of "DC improvement" status varchar(255) Status of work order "COMPLETE" summary varchar(255) Summary title of work order "New Work Order" state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.     Table: WORK_ORDER_REMOVE_TASK   WORK_ORDER_REMOVE_TASK Field Datatype Description Sampledata id varchar(40) Unique id of the task "123882d5-cebe-4563-a1fc-b7855c7e423e" wo_id varchar(40) Unique id of the work order "029882d9-ccbe-4233-a1fc-b8a94050e3bb" ci_id varchar(255) Unique id of the configuration_item "550e8400-e29b-41d4-a716-446655440000" assigned_to varchar(255) Name of assignee of the task "Mr Black" created_date bigInt Date in epoch format "1362608169502" completed_date bigInt Date in epoch format "1362608169502" needed_by_date bigInt Date in epoch format "1362608169502" note varchar(3000) Note made on the task "This must be done!" order_number Integer Number of work order that task is part of "54" status varchar(255) Status of work order "COMPLETE" state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.     Table: WORK_ORDER_MOVE_TASK    WORK_ORDER_MOVE_TASK Field Datatype Description Sampledata id varchar(40) Unique id of the task "123882d5-cebe-4563-a1fc-b7855c7e423e" wo_id varchar(40) Unique id of the work order "029882d9-ccbe-4233-a1fc-b8a94050e3bb" source_ci_id varchar(255) Unique id of the source configuration_item "550e8400-e29b-41d4-a716-446655440045" target_ci_id varchar(255) Unique id of the target configuration_item "550e8400-e29b-41d4-a716-446655440046" assigned_to varchar(255) Name of assignee of the task "Mr Black" created_date bigInt Date in epoch format "1362608169502" completed_date bigInt Date in epoch format "1362608169502" needed_by_date bigInt Date in epoch format "1362608169502" note varchar(3000) Note made on the task "This must be done!" order_number Integer Number of work order that task is part of "54" status varchar(255) Status of work order "COMPLETE" state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.     Table: WORK_ORDER_CUSTOM_TASK   WORK_ORDER_CUSTOM_TASK Field Datatype Description Sampledata id varchar(40) Unique id of the task "123882d5-cebe-4563-a1fc-b7855c7e423e" wo_id varchar(40) Unique id of the work order "029882d9-ccbe-4233-a1fc-b8a94050e3bb" assigned_to varchar(255) Name of assignee of the task "Mr Black" created_date bigInt Date in epoch format "1362608169502" completed_date bigInt Date in epoch format "1362608169502" needed_by_date bigInt Date in epoch format "1362608169502" note varchar(3000) Note made on the task "This must be done!" order_number Integer Number of work order that task is part of "54" status varchar(255) Status of work order "COMPLETE" description varchar(3000) Note made on the task "This must be done!" state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.     Table: WORK_ORDER_CUSTOM_EQUIPMENT_TASK    WORK_ORDER_CUSTOM_EQUIPMENT_TASK Field Datatype Description Sampledata id varchar(40) Unique id of the task "123882d5-cebe-4563-a1fc-b7855c7e423e" wo_id varchar(40) Unique id of the work order "029882d9-ccbe-4233-a1fc-b8a94050e3bb" ci_id varchar(255) Unique id of the configuration_item "550e8400-e29b-41d4-a716-446655440000" assigned_to varchar(255) Name of assignee of the task "Mr Black" created_date bigInt Date in epoch format "1362608169502" completed_date bigInt Date in epoch format "1362608169502" needed_by_date bigInt Date in epoch format "1362608169502" note varchar(3000) Note made on the task "This must be done!" order_number Integer Number of work order that task is part of "54" status varchar(255) Status of work order "COMPLETE" description varchar(3000) Note made on the task "This must be done!" state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.     Table: WORK_ORDER_CONNECT_CABLE_TASK      WORK_ORDER_CONNECT_CABLE_TASK Field Datatype Description Sampledata id varchar(40) Unique id of the task "123882d5-cebe-4563-a1fc-b7855c7e423e" wo_id varchar(40) Unique id of the work order "029882d9-ccbe-4233-a1fc-b8a94050e3bb" source_ci_id varchar(255) Unique id of the source configuration_item "550e8400-e29b-41d4-a716-446655440045" target_ci_id varchar(255) Unique id of the target configuration_item "550e8400-e29b-41d4-a716-446655440046" assigned_to varchar(255) Name of assignee of the task "Mr Black" created_date bigInt Date in epoch format "1362608169502" completed_date bigInt Date in epoch format "1362608169502" needed_by_date bigInt Date in epoch format "1362608169502" note varchar(3000) Note made on the task "This must be done!" order_number Integer Number of work order that task is part of "54" status varchar(255) Status of work order "COMPLETE" state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.     Table: WORK_ORDER_ADD_TASK   WORK_ORDER_ADD_TASK Field Datatype Description Sampledata id varchar(40) Unique id of the task "123882d5-cebe-4563-a1fc-b7855c7e423e" wo_id varchar(40) Unique id of the work order "029882d9-ccbe-4233-a1fc-b8a94050e3bb" ci_id varchar(255) Unique id of the configuration_item "550e8400-e29b-41d4-a716-446655440000" assigned_to varchar(255) Name of assignee of the task "Mr Black" created_date bigInt Date in epoch format "1362608169502" completed_date bigInt Date in epoch format "1362608169502" needed_by_date bigInt Date in epoch format "1362608169502" note varchar(3000) Note made on the task "This must be done!" order_number Integer Number of work order that task is part of "54" status varchar(255) Status of work order "COMPLETE" state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.     Table: WORK_ORDER_CHANGE_TICKET    WORK_ORDER_CHANGE_TICKET Field Datatype Description Sampledata work_order_wo_id varchar(40) Unique id of the work order "123882d5-cebe-4563-a1fc-b7855c7e423e" links_change_ticket_id varchar(32) Unique id of the change ticket "029882d9-ccbe-4233-a1fc-b8a94050e3bb" links_external_system_id varchar(36) Unique id of the external system "550e8400-e29b-41d4-a716-446655440000"   Table: WORK_ORDER_NETWORK_CONNECTION_TASK    WORK_ORDER_NETWORK_CONNECTION_TASK Field Datatype Description Sampledata id varchar(40) Unique id of the task "123882d5-cebe-4563-a1fc-b7855c7e423e" assigned_to varchar(255) Name of assignee of the task "Mr Black" completed_date bigInt Date in epoch format "1362608169502" created_date bigInt Date in epoch format "1362608169502" needed_by_date bigInt Date in epoch format "1362608169502" note varchar(3000) Note made on the task "This must be done!" order_number Integer Number of work order that task is part of "54" wo_id varchar(40) Unique id of the work order "029882d9-ccbe-4233-a1fc-b8a94050e3bb" disconnect Integer Boolean telling if connection is disconnected or connected 1 = disconnected, 0 = connected from_connector_type varchar(255) Port type used in connection starting device "RJ45" from_ci_id varchar(255) Unique id of the configuration_item "550e8400-e29b-41d4-a716-446655440000" from_module_name varchar(255) Name of module containing port in connection starting device "Module Name" from_patch_panel_rear Integer Boolean telling if starting patch panel is connected in front or rear 1 = rear, 0 = front from_port_number Integer Port number of connection on starting device 1 port_count Integer Number of contiguous ports to be connected/disconnected 8 fan_out_outlet_number Integer Number (ordinal) of fanout outlet 3 to_connector_type varchar(255) Port type used in connection ending device "RJ45" to_ci_id varchar(255) Unique id of the configuration_item "550e8400-e29b-41d4-a716-446655440000" to_module_name varchar(255) Name of module containing port in connection ending device "Module Name 2" to_patch_panel_rear Integer Boolean telling if starting patch panel is connected in front or rear 1 = rear, 0 = front to_port_number Integer Port number of connection on starting device 1 connection_usage varchar(255) Description of connection "Internet provider 2" status varchar(255) Status of work order "COMPLETE" state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.     Table: CHANGE_TICKET   CHANGE_TICKET Field Datatype Description Sampledata change_ticket_id varchar(32) Unique id of the change ticket "123882d5-cebe-4563-a1fc-b7855c7e423e" external_system_id varchar(36) Unique id of the external system "550e8400-e29b-41d4-a716-44665544030 change_ticket_status varchar(26) Status of the change ticket "COMPLETE" name varchar(255) Name of the change ticket "Move server" state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.     Tables: AUDIT_LOG_* There are 11 tables audit_log_* tables - one for each supported language: en, de, es, fr, it, ja, ko, pt_br, ru, zh, zh_tw. Tables are not populated unless the language was added to transformation configuration.   AUDIT_LOG_* Field Datatype Description Sampledata event_id varchar(50) ID of the action "927" event_time timestamp without time zone Time of the action 2021-10-12 14:16:16 event_type varchar(8) Internal type of the action COR0007d event_username varchar(255) Name of the user that performed action "apc" event_message varchar(2000) Description of the action with all the details. "Added Patch Panel 'pp2' to 'U-36/R2/JF/'." state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.     Table: AUDIT_LOG_CONFIGURATION_ITEM Table that maps audit log actions to equipment.   AUDIT_LOG_CONFIGURATION_ITEM Field Datatype Description Sampledata event_id varchar(50) ID of the action from AUDIT_LOG_*  tables "927" ci_id timestamp without time zone ID of the equipment from CONFIGURATION_ITEM  "550e8400-e29b-41d4-a716-446655440000" state_flag varchar(16) Indicates the state of the information. See section on information state above new, changed, deleted state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.   state_last_synchronized long Timestamp used by IT Advisor to indicate when the row was last successfully read and synchronized.     Table: SYNCHRONIZATION_STATUS In ETL each object class (tags, configuration-items etc.) have specialized synchronizers. During synchronization track is kept with the number of new, updated and deleted ETL entities (rows in the ETL import tables) After synchronization this information is stored in the SYNCHRONIZATION_STATUS table.   SYNCHRONIZATION_STATUS Field Datatype Description Sampledata configuration_name varchar(255) For future use   synchronizer_name varchar(255) For future use   timestamp bigInt For future use   deleted_objects bigInt For future use   new_objects bigInt For future use   updated_objects bigInt For future use     Table: PERFORMANCE_DATA_POINT    PERFORMANCE_DATA_POINT Field Datatype Description Sampledata conf_id varchar(255) For Schneider Electric use only, needs developer specific agent to be populated with data   step_id varchar(255) For Schneider Electric use only, needs developer specific agent to be populated with data   timestamp bigInt For Schneider Electric use only, needs developer specific agent to be populated with data   conf_name varchar(255) For Schneider Electric use only, needs developer specific agent to be populated with data   duration_ns bigInt For Schneider Electric use only, needs developer specific agent to be populated with data   occurences Integer For Schneider Electric use only, needs developer specific agent to be populated with data     Table: JOB_STATUS Table that provides information about export job refreshing data and this export database. Can be used to determine: if export job is currently running (last status is "STARTED") if there were any failures how often it runs and when it did run last time   JOB_STATUS Field Datatype Description Sampledata id uuid Random ID of the event "a1bf2811-5388-402e-8509-a4e7ea8c7edc" status varchar(255) Status of the job "STARTED" "SUCCEEDED" "FAILED" timestamp bigInt Timestamp of the event - milliseconds from 1970 1642503089498

The ETL import database is by its predefined schema providing an API to populated EcoStruxure IT Advisor with data.   The schema is graphically represented in the ETL Import Database Schema attached below.   Importing large amounts of data   When importing data, the affected rooms are locked and other changes cannot be done at the same time. So if you are importing large amounts of data, please consider doing so when it affects as few users as possible.   From version 8.0.2 we have tried to minimize the effects of locking the affected rooms by locking less and for a shorter amount of time. There is a lock in the staging database that you can set while performing changes. As long as the lock is set, IT Advisor will wait before synchronizing the data from the staging database.   Description of tables, relations and classifications    In the following section there is a distinction between tables and classifications.  Technically both are tables in the database, but conceptually there is a big difference.    Please make sure to understand the Database Information State for the ETL import database. The information state is important since it is used for "bookkeeping" of data in the database.    Tables are used to store information, whereas the classifications are used to classify the information which enables ITA to interpret and handle the information. Classifications are "read-only" since they a closely coupled with a specific revision of IT Advisor.  Relations describe the way information is related.    Table: SCHEMA_VERSION The SCHEMA_VERSION table contains only one record which is used to indicate the version of this database schema. The current schema-version is 1.2.0, as can be seen in the overview found below.    Version ITA Version Description 1.0.0   Initial schema design for the ETL feature 1.2.0 ITA 9.0.5 Added asset table and support for asset network ports and connections   Table: CONFIGURATION_ITEM The configuration item is the central entity in the ETL data model. Therefore the central table of the staging-database is the CONFIGURATION_ITEM table. The table is expected to hold every piece of datacenter equipment/entity, both physical or virtual, that can provide information or data to IT Advisor. Examples include, but is not limited to servers, temperature sensors,...  The last three fields are used for bookkeeping as described here.    CONFIGURATION_ITEM   Field Data type Description Required Sample data ci_id varchar(255) Unique identifier of item (primary key). Mandatory "server_001" cit_id BigInt Specifies the type of this item. The value is a foreign key to values in the CONFIGURATION_ITEM_TYPE table. Mandatory 1 ci_name varchar(255) Name of the item. Mandatory "Server XYZ" ci_barcode varchar(255) Barcode of the item. Optional "IT1234567890" ci_location varchar(255) Location of item. Optional "Behind Rack A in aisle B" ci_model_number varchar(255) Item model number. Optional "DL360" ci_model_name varchar(255) Item model name. Optional "HP Proliant" ci_serial_number varchar(255) Item serial number. Optional "KN20623BY9" ci_manufacturer varchar(255) Manufacturer of item. Optional "HP" ci_ip_address varchar(255) Main ip-address of item. Optional "192.168.1.75:8080/adminConsole.cgi" ci_description varchar(255) Description of item. Optional "Honeypot in DMZ" ci_fingerprint varchar(255) Some unique "fingerprint" of the item, that may make it easier to match with items in the ITA model. Optional   state_flag varchar(16) Indicates the state of the information. See section on information state above Mandatory new, changed, deleted, synced state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.     state_last_synchronized long Timestamp used by SWO to indicate when the row was last successfully read and synchronized.       When modeling a data center, configuration items often have a natural parent-child relationship with each other. As an example servers are placed in racks which are placed in rows in a room. These relation-ships are captured in a relation-table instead of directly on the configuration. This allows the configuration items to participate in multiple relations, and to model different type of relations.  Classification: CONFIGURATION_ITEM_TYPE The term configuration item is very broad and can be used to describe just about anything in the data center. To make it possible to distinguish between different types of configuration items, they are classified with a configuration_item_type. The configuration_item_type information is used in IT Advisor to decide how to store, represent and utilize the configuration item. The currently supported types are found in the table below.   CONFIGURATION_ITEM_TYPE ID Name Description 0 Unspecified Type used if no other type matches. 1 SERVER This type indicates, that the item should be handled as a server. 2 SENSOR This type indicates, that the item should be handled as a sensor. 3 AC   4 BATTERY_MANAGER   5 BRANCH-CIRCUIT_MONITOR   6 CAMERA_POD   7 CCTV_POD   8 CHILLER   9 CONSOLE_PORT_SERVER   10 DC_RECTIFIER   11 ENVIRONMENT   12 GENERATOR   13 INFRASTRUXURE   14 KVM_SWITCH   15 NETBOTZ_WALL   16 NETBOTZ_RACK   17 NETBOTZ_ENCLOSURE   18 NETWORK_DEVICE   19 POWER_DISTRIBUTION   20 POWER_METER   21 RACK_ACCESS_DEVICE   22 RACK_MANAGER   23 RACK_POWER_DISTRIBUTION   24 SENSOR_POD   25 SHARED_DEVICE   26 SNMP_DEVICE   27 STATIC_TRANSFER_SWITCH   28 TRANSFER_SWITCH   29 UPS   30 FAN_MODULE   31 IO_CARD   32 PSU   33 UCS_CHASSIS   34 UCS_MANAGER   35 UCS_SERVER   36 BLADE   37 VIRTUAL_MACHINE   38 DESKTOP_COMPUTER   39 NETWORK_SWITCH     Table: CONFIGURATION_ITEM_PROPERTY The configuration item has a basic set of properties that are used for genome matching and identification in the UI. If other properties needs to be handled, they are stored in the CONFIGURATION_ITEM_PROPERTY table together with a reference to the configuration item. Each configuration_item_property has a type to classify the property.    CONFIGURATION_ITEM_PROPERTY Field Data type Description Required Sample data ci_id varchar(255) Unique identifier of item (foreignkey) Mandatory "server_001" cip_name varchar(255) The name of the property Mandatory "servicetag" cip_value varchar(255) Value of property Mandatory "KN20623BY9" pt_id BigInt Specifies the type of this property. The value is a foreign key to values in the PROPERTY_TYPE table Mandatory 1 state_flag varchar(16) Indicates the state of the information. See section on information state above Mandatory new, changed, deleted, synced state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.     state_last_synchronized long Timestamp used by SWO to indicate when the row was last successfully read and synchronized.     Note: this table has a complex unique primary key made up of (ci_id, cip_name)  Classification: PROPERTY_TYPE The different types of configuration_item_property is classified using the values in the table below.    PROPERTY_TYPE ID Name Description 0 Unspecified Properties of this type are not handled by the system 1 TAG This type indicates, that the property should be handled as a Tag 2 PROPERTY This type indicates, that the property should be handled as a CustomProperty 3 GENOME Not used. Reserved for future development. 4 ASSETPROPERTY Use as a simple pass-through mechanism allowing for dynamic properties on configuration items   Depending on type, the properties are handled in slightly different ways. Properties of type TAG and PROPERTY are interpreted as Tags and Custom Properties and are automatically assigned to items in the ITA Data Model when configuration items are associated, they will not show up for configuration items that are not associated with items in the model.   Note For tags: Currently it is not possible to create a new tag through ETL, the tags should be known by Operation before synchronizing with the staging database. Properties of type ASSETPROPERTY are set directly on the configuration items. They are passed through the synchronization system without interpretation, and show up on the configurations regardless of association.    Table: CONFIGURATION_ITEM_MEASUREMENT Datapoints and Measurements are stored in the CONFIGURATION_ITEM_MEASUREMENT table with a reference to the configuration_item and the class of measurement.  Besides this, the measurements are further classified using unit and type. The measurement unit classification is used to ensure the correct magnitude of the measurement.   The class of a measurement specifies the overall type of measurement like, CPU utilization, voltage, current, temperature... Measurements type is needed together with the interval_start and interval_end fields to distinguish between single measurements in space and time, minimum, average and maximum values over a time period.   Power and current values For power measurements you have to provide both a peak power measurement and an average power measurement. For current measurements you can only use peak measurement.   CONFIGURATION_ITEM_MEASUREMENT Field Data type Description Required Sample data cim_id varchar(255) Unique identifier of item (primary key). Mandatory "measurement_001" ci_id varchar(255) Specifies the item that this measurement is associated with. The value is a foreign key to values in the CONFIGURATION_ITEM table. Mandatory "server_1" cim_subid varchar(255) A subid for the measurement which is used with branch circuit monitoring to distinguish between submodules. Optional "breaker_01" cim_value float The "measured" value. Optional 7.35 mu_id BigInt Specifies the unit of this measurement. The value is a foreign key to values in the MEASUREMENT_UNIT classification. Optional 1 mc_id BigInt Specifies the class of this measurement. The value is a foreign key to values in the MEASUREMENT_CLASS  classification. Optional 3 mt_id BigInt Specifies the type of this measurement. The value is a foreign key to values in the MEASUREMENT_TYPE classification. Optional 2 cim_interval_starttime long Start time for the measurement Optional 123400 cim_interval_endtime long End time for the measurement Optional 123500 state_flag varchar(16) Indicates the state of the information. See section on information state above Mandatory new, changed, deleted, synced state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.     state_last_synchronized long Timestamp used by SWO to indicate when the row was last successfully read and synchronized.       Classification: MEASUREMENT_UNIT This classification is used to ensure the correct labels and magnitudes of measurement when displayed and used in calculations inside IT Advisor. MEASUREMENT_UNIT expresses the measurement unit of a measurement.    MEASUREMENT_UNIT ID Name Description 0 Unknown Unit less measurement or unit not known. 1 W Watt 2 kW Kilo Watt (1kW = 1000W) 3 MW Mega Watt (1MW = 1000000W) 4 PCT per cent 5 PML per mille 6 B Byte 7 KB Kilobyte (1kB = 1024B) 8 MB Megabyte (1Mb = 1024kB) 9 GB Gigabyte (1GB = 1024MB) 10 TB Terabyte (1TB = 1024GB) 11 PB Petabyte (1PB = 1024TB) 12 EB Exabyte (1EB = 1024PB) 13 K Kelvin 14 F Fahrenheit 15 C Celsius 16 RPM Revolutions per minute 17 MPS Meter per second 18 Bar Bar 19 Psi Psi (pounds per square inch) 20 S Second 21 Ms Millisecond 22 Mys Microsecond 23 Ns Nanosecond 24 Bps Bytes per second 25 Kbps Kilobytes per second 26 Mbps Megabytes per second 27 GBPS Gigabytes per second 28 TBPS Terabytes per second 29 V Volt 30 KV Kilo volt 31 A Ampere 32 MA Milliampere 33 Cfm Cubic feet per minute 34 Gkg Gram per kg 35 RH Relative humidity 36 VA Volt-ampere 37 Wh Watt-hour – used for energy measurement (1000Wh = 3.6MJ) 38 kWh Kilo Watt-hour = 1000Wh 39 MWh Mega Watt-hour =1000kWh 40 VAh Volt-ampere-hour – unit for apparent energy 41 kVAh Kilo Volt-ampere-hour = 1000VAh 42 MVAh Mega volt-ampere-hour = 1000kVAh 43 J Joule 44 Unitless Unitless measurement like factors – this is NOT the same as unknown   Classification: MEASUREMENT_CLASS The MEASUREMENT_CLASS classification is used to indicate what is measured. The table below lists the different classes of measurements that is of interest to ITA.   Note: Most of these are included for future usage and are not used/interpreted in the current version of ETL Integration. The currently supported classes are highlighted.   MEASUREMENT_CLASS ID Name Description 0 unspecified Indicated that nothing is known about the measurement. 1 cpu_utilization   2 cpu_idle   3 cpu_wait   4 memory_usage   5 memory_swap   6 disk_io   7 disk_latency   8 net_io   9 power You have to provide both a peak power measurement and an average power measurement. 10 power_single_phase 11 power_L1 12 power_L2 13 power_L3 14 current Only PEAK_DAY and MAXIMUM measurement types are supported. 15 current_single_phase 16 current_L1 17 current_L2 18 current_L3 19 voltage   20 voltage_single_phase   21 voltage_L1   22 voltage_L2   23 voltage_L3   24 temperature_inlet   25 temperature_outlet   26 temperature_ambient   27 humidity_absolute   28 humidity_relative   29 pressure   30 velocity   31 time   32 time_elapsed   33 time_remaining   34 frequency   35 flow   36 volume   37 apparent power Note: The value is imported as a sensor, and is not used in any calculations, tooltips, etc. 38 active power Note: The value is imported as a sensor, and is not used in any calculations, tooltips, etc. 39 power factor   40 temperature delta     Classification: MEASUREMENT_TYPE The MEASUREMENT_TYPE classification is used to indicate how a measurement was made, with regards to the factor of time. MINIMUM, MAXIMUM and AVERAGE classes are used to indicate that the measured value is aggregated over a period of time, whereas POINT classes are single measurements at a given point in time. The time interval or point in time is specified in the CONFIGURATION_ITEM_MEASUREMENT table as cim_interval_starttime and cim_interval_endtime. Note: Not all measurement types are supported for all measurement classes. The currently supported classes are highlighted.     MEASUREMENT_TYPE ID Name Description 0 Unspecified   1 MINIMUM Indicates that the measurement is the smallest observed value in the given time interval 2 MAXIMUM Indicates that the measurement is the largest observed value in the given time interval 3 AVERAGE Indicates that the measurement is the average value over the given time interval 4 POINT Indicates that the measurement is a point measurement at a specific point in time 5 AVERAGE_DAY Indicates that the measurement is the average value for the day. This value is read by ITA in the same way as DCE data and is more useful to the ITA server than AVERAGE 6 PEAK_DAY Indicates that the measurement is the largest observed value for the day. This value is read by ITA in the same way as DCE data and is more useful to the ITA server than MAXIMUM   Table: BREAKER_PANEL_MEASUREMENT The breaker_panel_measurement table can be used for integrating power measurement from a branch-circuit monitoring system. You only need to specify either power or watts, but please note that there is no automatic conversion between watts and amps. In most cases if you have both the best option is to specify both. Learn more about importing manual measurements   BREAKER_PANEL_MEASUREMENT Field Data type Description Match type Sample data id varchar(255) Unique identifier of the measurement (primary key) Mandatory "m_001" ci_id varchar(255) Unique identifier of the item (which must have breaker panels) that this measurement originated on (foreignkey) Mandatory "pde_001" panel_id varchar(255) Name of the breaker panel that have the measurement, this must match the panel in ITA Mandatory "Panel A" circuit_number INTEGER Position on the breaker panel, this must match a circuit number on the given panel in ITA Mandatory 5 power_phase varchar(2) Power phase of the measurement, only needed if multiple positions on the panel have the same circuit number Optional "L1", "L2" or "L3" time timestamp When was the measurement made Mandatory  1476057600000 amps double precision The measurement in amps Optional  7.8 watts double precision The measurement in watts Optional 1250.0 state_flag varchar(16) Indicates the state of the information. See section on information state above Mandatory new, changed, deleted, synced state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.     state_last_synchronized long Timestamp used by SWO to indicate when the row was last successfully read and synchronized.       Table: ASSET  Introduced in 9.0.5. Rows in this table represent rack-mounted equipment in the model directly, as opposed to CONFIGURATION_ITEMs representing external system Devices. This allows to add, update and delete equipment items.   ASSET Field Data type Description Match type Sample data asset_id varchar(255) Unique identifier of the asset (primary key) Mandatory "asset_01" item_uuid varchar(36) Internal unique identifier of the asset in the model, assigned automatically by import logic or used to set up reference to existing equipment Optional "1a3ff992-a505-48fd-89bd-44c221331dc6" asset_name varchar(255) Name of the asset Optional "Server A" rack_name varchar(255) Name of the rack the asset should be added or looked for; rack name should be unique in the room Mandatory "Rack 1" room_name varchar(255) Name of the room the asset belongs to; should be unique within the solution, or room_parent_location should be used to identify the correct room; as of now, storages are not supported Mandatory "Room 1" room_parent_location varchar(255) Used to find the correct room; if room_name is unique within the solution, this field is optional Optional "Location 1" enclosure_name varchar(255) Name of the enclosure, if the row represents switch or blade with it Optional "Enclosure 1" enclosure_slot Bigint Slot within the enclosure where switch or blade should be located; counted from left to right then rows from top to bottom Optional 3 rack_mounting_position Bigint U-position or VU-position of the asset mounted in the rack; optional for switches and blades mounted in enclosures or if rack_mounting_type is one of rear_left or rear_right Optional 1 rack_mounting_type varchar(255) How asset is mounted in the rack, see details below; ignored if the asset is mounted in enclosure, otherwise mandatory Optional "FRONT" manufacturer varchar(255) Manufacturer used to lookup for correct model in Custom Catalog or Genome Library; optional if it is possible to find unique model by part_number and/or model_name Optional "HP" model_name varchar(255) Model name that matches to Genome from Custom Catalog or Genome Library; optional if it is possible to find unique model by part_number Optional "HP Proliant" part_number varchar(255) Part number that matches to Genome from Custom Catalog or Genome Library; optional if it is possible to find unique model by model_name Optional "DL360" description varchar(255) Description of the asset Optional "Honeypot in DMZ" barcode varchar(255) Barcode, must be unique within the solution Optional "IT0123456789" serial_number varchar(255) Serial number of the asset Optional "ABC" state_flag varchar(16) Indicates the state of the information. See section on information state above Mandatory new, changed, deleted, synced state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed     state_last_synchronized long Timestamp used by SWO to indicate when the row was last successfully read and synchronized     The rack_mounting_type is matched against the following values (ignoring case)    rack_mounting_type Name Description FRONT   REAR   REAR_LEFT Rack PDUs and ATSes only. REAR_RIGHT Rack PDUs and ATSes only. FRONT_LEFT Only allowed in Racks with vertical u-positions. FRONT_RIGHT Only allowed in Racks with vertical u-positions. TOP Only allowed in Racks with external top u-positions. Available since ITA 9.2.1 BOTTOM Only allowed in Racks with external bottom u-positions. Available since ITA 9.2.1   Table: ALARM The ETL integration is not well suited to handling alarms due to the inherited latency in information-propagation through the system. Nevertheless, it is possible to raise alarms on configuration items by adding them to the ALARM table.    ALARM Field Data type Description Match type Sample data alarm_id varchar(255) Unique identifier of alarm (primary key) Mandatory "alarm_01" ci_id varchar(255) Unique identifier of the item that this alarm concerns (foreignkey) Mandatory "server_001" alarm_severity varchar(64) Severity of alarm. Recognized values are critical, failure, error and warning. Not case sensitive Optional "Error" alarm_state varchar(64) What is the state of the alarm. Recognized values are active and resolved . Not case sensitive Optional "Active" alarm_message varchar(255) Text describing the problem. The text will be show as is, inside ITA Optional "Chiller malfunction – refrigerant pressure too high" alarm_occured timestamp When was to alarm observed by the external system Optional   alarm_resolved timestamp When was the alarm resolved on the external system Optional   state_flag varchar(16) Indicates the state of the information. See section on information state above Mandatory new, changed, deleted, synced state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.     state_last_synchronized long Timestamp used by SWO to indicate when the row was last successfully read and synchronized.     The alarm_severity is matched against the following values (ignoring case)    alarm_severity Name Description critical Critical alarm failure Incident in alarming system   If the severity does not match one of these values, the alarm is assumed to be pure informational.  Note: There is no way of resolving the alarms directly from IT Advisor.  The alarm_state is matched against the following values (ignoring case)    alarm_state Name Description Active Alarm is active Resolved Alarm is inactive   Table: CONFIGURATION_ITEM_RELATION Relations between items are modeled using the CONFIGURATION_ITEM_RELATION table. Currently this is reserved for future use. To model a relation, both ends are referenced together with a classification of the relation.    CONFIGURATION_ITEM_RELATION Field Date type Description Match Type Sample data cir_id varchar(255) Unique identifier of relation (primary key) Mandatory "cable_001" ci_id_a varchar(255) Id of start point of this relation. This is a foreign key to the CONFIGURATION_ITEM table Optional "server_1" ci_id_b varchar(255) Id of end point of this relation. This is a foreign key to the CONFIGURATION_ITEM table Optional "server_2" rt_id BigInt Used to indicate the type of this relation. The value is a foreign key in the RELATION_TYPE classification. Optional 0 state_flag varchar(16) Indicates the state of the information. See section on information state above   new, changed, deleted, synced state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.     state_last_synchronized long Timestamp used by SWO to indicate when the row was last successfully read and synchronized.       Classification: RELATION_TYPE This classification is used to specify the kind of relation being modeled between two items.    RELATION_TYPE ID Name Description 0 unspecified Indicated that nothing is known about the relation. 1 a_inside_b Reserved for future usage 2 b_inside a Reserved for future usage 3 a_powered_by_b Reserved for future usage 4 b_powered_by_a Reserved for future usage 5 a_connected_to_b Reserved for future usage 6 a_placed_left_of_b Reserved for future usage 7 a_placed_right_of_b Reserved for future usage 8 a_placed_in_front_of_b Reserved for future usage 9 a_placed_behind_b Reserved for future usage 10 a_placed_under_b Reserved for future usage 11 a_placed_above_b Reserved for future usage 12 a_vm_inside_b Reserved for future usage   Table: RELATION_PROPERTY Information, besides the actual type of a relation, can be associated with the relation through RELATION_PROPERTY.    RELATION_PROPERTY Field Date type Description Match Type Sample data cir_id varchar(255) Reference to the CONFIGURATION_ITEM_RELATION  Mandatory "cable_001" rp_name varchar(255) The name of this property Mandatory "cable length" rp_value varchar(255) The value of this property. The value is shown as is, and will not be interpreted or used in calculations. Optional "50m" rpt_id BigInt Used to indicate the type of this relation property. The value is a foreign key in the RELATION_PROPERTY_TYPE classification. Optional 0 state_flag varchar(16) Indicates the state of the information. See section on information state above   new, changed, deleted, synced state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.     state_last_synchronized long Timestamp used by SWO to indicate when the row was last successfully read and synchronized.     Note: this table has a complex unique primary key made up of (cir_id, rp_name)  As an example a simple parent-child relationship between a blade chassis and a blade may require a "placement"-property, where as a cable between two servers may need properties like cable-length, type, speed, etc. As with the other entities in the staging-database, a RELATION_PROPERTY is classified by a type (rpt_id)    Classification: RELATION_PROPERTY_TYPE Classification used for the type of relation-property.    RELATION_TYPE ID Name Description 0 unspecified Indicated that nothing is known about the relation property. 1 cable_routing Used to classify cable routing information. 2 software_environment Used to specify the software environment of the relation Note that the b_inside_a implies a child-parent relationship without the need of a specific relation-property. Therefore there is currently no explicit relation property type to capture information for this relation.    Table: RELATION_ENDPOINT_PROPERTY In some cases, extra information about the relation endpoints are needed. One example is for network-connections where the endpoint properties are used to specify network port information.    RELATION_ENDPOINT_PROPERTY Field Date type Description Match Type Sample data cir_id varchar(255) Reference to a CONFIGURATION_ITEM_RELATION  Mandatory "cable_001" ci_id varchar(255) Reference to a CONFIGURATION_ITEM  Mandatory "server_1" rep_name varchar(255) The name of this property Mandatory "port speed" rep_value varchar(255) The value of this property. The value is shown as is, and will not be interpreted or used in calculations. Optional "1Gbps" rept_id BigInt Used to indicate the type of this relation endpoint property. The value is a foreign key in the RELATION_ENDPOINT_PROPERTY_TYPE classification. Optional 4 state_flag varchar(16) Indicates the state of the information. See section on information state above   new, changed, deleted, synced state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.     state_last_synchronized long Timestamp used by SWO to indicate when the row was last successfully read and synchronized.     Note: this table has a complex unique primary key made up of (cir_id, ci_id, rep_name)    Classification: RELATION_ENDPOINT_PROPERTY_TYPE Used for classification of the endpoint properties. Currently the classification is not used, but prepared for future usage.    RELATION_ENDPOINT_PROPERTY_TYPE ID Name Description 0 unspecified Indicated that nothing is known about the relation. 1 network_port_number Not used 2 network_port_type Not used 3 network_module_name Not used 4 network_port_speed Not used 5 rear_connected_port Not used   Table: CHANGE_TICKET Information about change tickets or work orders that should be imported are placed in this table. Note that the specified priority and status should be references to the CHANGE_TICKET_PRIORITY and CHANGE_TICKET_STATUS tables.   CHANGE_TICKET Field Data type Description Required Sample data ticket_id varchar(32) Unique identifier of item (primary key). Mandatory "ticket_001" assignee varchar(64) Specifies who is assigned to the work order change ticket Optional "Mr Black" comment varchar(3000) Used for comments on the work order change ticket   Optional "Install OS" due_date BigInt Specifies the due date of the work order change ticket Optional   summary varchar(128) Used for summary of the work order change ticket   Optional   priority Integer Specifies the priority of the change ticket The value is a foreign key in the CHANGE_TICKET_PRIORITY  Mandatory   status_order Integer  Specifies the status of the change ticket The value is a foreign key in the CHANGE_TICKET_STATUS   Mandatory   state_flag varchar(16) Indicates the state of the information. See section on information state above Mandatory new, changed, deleted, synced state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.     state_last_synchronized long Timestamp used by SWO to indicate when the row was last successfully read and synchronized.       Table: CHANGE_TICKET_PRIORITY The possible priority classifications for change tickets and work orders.   CHANGE_TICKET_PRIORITY Field Data type Description Required Sample data priority Integer Specifies the priority of the change ticket   Mandatory   priority_message varchar(32)   Mandatory   state_flag varchar(16) Indicates the state of the information. See section on information state above Mandatory new, changed, deleted, synced state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.     state_last_synchronized long Timestamp used by SWO to indicate when the row was last successfully read and synchronized.       Table: CHANGE_TICKET_STATUS The possible status classifications for change tickets and work orders.   CHANGE_TICKET_STATUS Field Data type Description Required Sample data status_order Integer Specifies the status of the change ticket   Mandatory   message varchar(32)   Mandatory   state_flag varchar(16) Indicates the state of the information. See section on information state above Mandatory new, changed, deleted, synced state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.     state_last_synchronized long Timestamp used by SWO to indicate when the row was last successfully read and synchronized.       Table: PORT_CONFIGURATION Specifies network ports on equipment. The ports cannot overlap each other or with port configurations created in Operations client. The same rules as for the client apply. For network speed the following units are supported: kbps, mbps, gbps, tbps, if no unit is specified it defaults to kbps, example speeds could be "100 kbps", "100 mbps" or "10 gbps".   PORT_CONFIGURATION Field Data type Description Required Sample data ci_id varchar(255) Reference to a CONFIGURATION_ITEM Mandatory "switch 001" connector_type varchar(255) Connector type for the network port Mandatory "RJ45_REGULAR" module_name varchar(255) Module name for the ports Mandatory "Module A" start_port Integer Starting port number for the ports Mandatory  0 port_count Integer Number of ports in the configuration Mandatory  24 port_speed varchar(255) Network speed for the port configuration Mandatory "100 mbps" state_flag varchar(16) Indicates the state of the information. See section on information state above Mandatory new, changed, deleted, synced state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.     state_last_synchronized long Timestamp used by SWO to indicate when the row was last successfully read and synchronized.       Table: ASSET_PORT_CONFIGURATION Introduced in 9.0.5. Specifies network ports on equipment. This table is similar to PORT_CONFIGURATION and should be used to manage ports on equipment looked up using asset uuid or by names: asset name, enclosure, rack, room and location.   The ports cannot overlap each other or with port configurations created in Operations client. The same rules as for the client apply. For network speed the following units are supported: kbps, mbps, gbps, tbps, if no unit is specified it defaults to kbps, example speeds could be "100 kbps", "100 mbps" or "10 gbps".   ASSET_PORT_CONFIGURATION Field Data type Description Required Sample data id varchar(255) Identifier of port configuration Mandatory "port_001" asset_item_uuid varchar(36) Internal unique identifier of the asset in the model Optional "1a3ff992-a505-48fd-89bd-44c221331dc6" asset_name varchar(255) Asset name; mandatory if asset by-name lookup is used Optional "Server 1" asset_enclosure varchar(255) Asset enclosure name Optional "Enclosure 1" asset_rack varchar(255) Asset rack name; mandatory if asset by-name lookup is used Optional "Rack 1" asset_room varchar(255) Asset room name; mandatory if asset by-name lookup is used Optional "Room 1" asset_location varchar(255) Room parent location name; mandatory if asset by-name lookup is used and room name is not unique Optional "Floor plan 1" connector_type varchar(255) Connector type for the network port Mandatory "RJ45_REGULAR" module_name varchar(255) Module name for the ports Mandatory "Module A" start_port Integer Starting port number for the ports Mandatory  0 port_count Integer Number of ports in the configuration Mandatory  24 port_speed varchar(255) Network speed for the port configuration Mandatory "100 mbps" state_flag varchar(16) Indicates the state of the information. See section on information state above Mandatory new, changed, deleted, synced state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.     state_last_synchronized long Timestamp used by SWO to indicate when the row was last successfully read and synchronized.         The connector_type is matched against the following values (ignoring case)   connector_type Name Description FIBER_REGULAR   FIBER_UPLINK   RJ45_REGULAR   RJ45_UPLINK   APC_LAN_REGULAR   APC_LAN_UPLINK   IP_KVM_UPLINK   IP_KVM_REGULAR   FIBER_SC_REGULAR   FIBER_SC_UPLINK   FIBER_LC_REGULAR   FIBER_LC_UPLINK   FIBER_ST_REGULAR   FIBER_ST_UPLINK   FIBER_MTRJ_REGULAR   FIBER_MTRJ_UPLINK   FIBER_FC_REGULAR   FIBER_FC_UPLINK   FIBER_MPO_MTP_REGULAR   FIBER_MPO_MTP_UPLINK   COAX_REGULAR   COAX_UPLINK   TERA_REGULAR   TERA_UPLINK   GG45_REGULAR   GG45_UPLINK   ARJ45_REGULAR   ARJ45_UPLINK   OTHER_REGULAR   OTHER_UPLINK     Table: NETWORK_CONNECTION Network connections between both existing ports and ports that are just being created are specified here. The connections is not limited to only those created with ETL, but can also be to ports created in Operations using the client.   Since 9.0.5 it is possible to manage network connections using reference to ASSETs, so reference to CONFIGURATION_ITEMs is optional. Asset is looked up by using either from_asset_item_uuid or by finding equipment by names of: location, room, rack, enclosure (for blades and switches) and the asset name itself.   NETWORK_CONNECTION Field Data type Description Required Sample data id varchar(255) Unique identifier of the network connection (primary key) Mandatory "connection001" from_id varchar(255) Reference to a CONFIGURATION_ITEM; mandatory if configuration_item lookup is used Optional "switch002" from_asset_item_uuid varchar(36) Internal unique identifier of the asset in the model Optional "1a3ff992-a505-48fd-89bd-44c221331dc6" from_asset_name varchar(255) Asset name; mandatory if asset by-name lookup is used Optional "Server 1" from_asset_enclosure_name varchar(255) Asset enclosure name Optional "Enclosure 1" from_asset_rack varchar(255) Asset rack name; mandatory if asset by-name lookup is used Optional "Rack 1" from_asset_room varchar(255) Asset room name; mandatory if asset by-name lookup is used Optional "Room 1" from_asset_location varchar(255) Room parent location name; mandatory if asset by-name lookup is used and room name is not unique Optional "Floor plan 1" from_connector_type varchar(255) Connection type for the from item Mandatory "RJ45_REGULAR" from_module_name varchar(255) Module name for the from item Mandatory "Module A" from_port_number integer Port number for the from item Optional 17 is_from_patch_panel_rear integer Is the connection from the rear of a patch panel Optional 0 to_id varchar(255) Reference to a CONFIGURATION_ITEM; mandatory if configuration_item lookup is used Optional "server007" to_asset_item_uuid varchar(36) Internal unique identifier of the asset in the model Optional "1a3ff992-a505-48fd-89bd-44c221331dc6" to_asset_name varchar(255) Asset name; mandatory if asset by-name lookup is used Optional "Server 1" to_asset_enclosure_name varchar(255) Asset enclosure name Optional "Enclosure 1" to_asset_rack varchar(255) Asset rack name; mandatory if asset by-name lookup is used Optional "Rack 1" to_asset_room varchar(255) Asset room name; mandatory if asset by-name lookup is used Optional "Room 1" to_asset_location varchar(255) Room parent location name; mandatory if asset by-name lookup is used and room name is not unique Optional "Floor plan 1" to_connector_type varchar(255) Connection type for the to item Mandatory "RJ45_REGULAR" to_module_name varchar(255) Module name for the to item Mandatory "Module A" to_port_number integer Port number for the to item Optional 1 is_to_patch_panel_rear integer Is the connection to the rear of a patch panel Optional 0 barcode varchar(255) Barcode for the connection Optional "87654321" description varchar(255) Description for the connection Optional "Description for the connection" install_date timestamp Installation date for the connection Optional "2015-01-31" length double precision Length of the network cable Optional 100.0 manufacturer varchar(255) Manufacturer of the network cable Optional "Network Cable Manufacturer" model_name varchar(255) Model name for the network cable Optional   name varchar (255) Name for the network cable (if relevant) Optional   part_number varchar (255) Part number for the network cable Optional   serial_number varchar (255) Serial number for the network cable Optional "12345678" state_flag varchar(16) Indicates the state of the information. See section on information state above Mandatory new, changed, deleted, synced state_last_updated long Timestamp set by the external system whenever the information in the row/record is changed.     state_last_synchronized long Timestamp used by SWO to indicate when the row was last successfully read and synchronized.       Table: CABLE_TYPE When importing network connection you can specify cable types for the connections. The cable type should match a cable type already specified in ITA.   CABLE_TYPE Field Data type Description Required Sample data id varchar(255) Unique identifier for the cable type (primary key). Mandatory "cable_type_001" description varchar(255) Description of the the type of cable.   "KVM cable" blue SMALLINT The blue part of the RGB color for the cable type.   255 green SMALLINT The green part of the RGB color for the cable type.   255 red SMALLINT The red part of the RGB color for the cable type.   255   Table: SYSTEM_LOCK Holds a single row with value SYSTEM_LOCK for column lock_name. The ETL jobs lock on this rows when an import starts. You can create an exclusive lock on the row to prevent import from running while staging database is being updated.      

If transformation(s) of data going into EcoStruxure IT Advisor are needed this can be provide by our Professional Service Team. The transformation(s) are executed using the Pentaho Data Integration (Kettle) engine. The transformations can be created and edited using the Spoon Tool Once transformations are in place these are being executed by the Pentaho Data Integration (Kettle) Engine that are build into the IT Advisor server. This requires the transformation file(s) to be transferred to the ITA server where they will automatically will be executed when located in the right folder on the server and the external system configuration is done in the ITA client. The import files must be located in "data/pentaho/import" in order for ITA to execute the files/transformation(s). Export transformation files must be located in "data/pentaho/export".