Help
  • Explore Community
  • Get Started
  • Ask the Community
  • How-To & Best Practices
  • Contact Support
Notifications
Login / Register
Community
Community
Notifications
close
  • Forums
  • Knowledge Center
  • Events & Webinars
  • Ideas
  • Blogs
Help
Help
  • Explore Community
  • Get Started
  • Ask the Community
  • How-To & Best Practices
  • Contact Support
Login / Register
Sustainability
Sustainability

Join our "Ask Me About" community webinar on May 20th at 9 AM CET and 5 PM CET to explore cybersecurity and monitoring for Data Center and edge IT. Learn about market trends, cutting-edge technologies, and best practices from industry experts.
Register and secure your Critical IT infrastructure

ETL export database

ETL - Extract Transform Load

EcoStruxure IT Advisor integrations for the advanced user

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
  • Home
  • Schneider Electric Community
  • EcoStruxure IT Help Center
  • EcoStruxure IT Help Center Categories
  • IT Advisor
  • ETL - Extract Transform Load
  • ETL - Extract Transform Load
  • ETL export database
Options
  • Subscribe to RSS Feed
  • Mark as New
  • Mark as Read
  • Bookmark
  • Subscribe
  • Email to a Friend
  • Printer Friendly Page
  • Report Inappropriate Content
Invite a Co-worker
Send a co-worker an invite to the portal.Just enter their email address and we'll connect them to register. After joining, they will belong to the same company.
You have entered an invalid email address. Please re-enter the email address.
This co-worker has already been invited to the Exchange portal. Please invite another co-worker.
Please enter email address
Send Invite Cancel
Invitation Sent
Your invitation was sent.Thanks for sharing Exchange with your co-worker.
Send New Invite Close

Related Forums

  • EcoStruxure IT forum

  • APC UPS Data Center & Enterprise Solutions Forum

Previous Next

Invite a Colleague

Found this content useful? Share it with a Colleague!

Invite a Colleague Invite

EcoStruxure IT Support

Submit a support request for additional assistance with EcoStruxure IT software.

Request Support
Back to ETL - Extract Transform Load
Options
  • Subscribe to RSS Feed
  • Mark as New
  • Mark as Read
  • Bookmark
  • Subscribe
  • Email to a Friend
  • Printer Friendly Page
  • Report Inappropriate Content
1 Like
2642 Views

Link copied. Please paste this link to share this article on your social media post.

Trying to translate this page to your language?
Select your language from the translate dropdown in the upper right. arrow
Translate to: English
  • (Français) French
  • (Deutsche) German
  • (Italiano) Italian
  • (Português) Portuguese
  • (Русский) Russian
  • (Español) Spanish

ETL export database

Picard EcoStruxureIT
‎2020-04-06 06:50 AM

Last Updated: Sisko JLehr Sisko ‎2024-03-20 06:17 PM

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
Attachments
Was this article helpful? Yes No
No ratings

Link copied. Please paste this link to share this article on your social media post.

Didn't find what you are looking for? Ask our Experts
To The Top!

Forums

  • APC UPS Data Center Backup Solutions
  • EcoStruxure IT
  • EcoStruxure Geo SCADA Expert
  • Metering & Power Quality
  • Schneider Electric Wiser

Knowledge Center

Events & webinars

Ideas

Blogs

Get Started

  • Ask the Community
  • Community Guidelines
  • Community User Guide
  • How-To & Best Practice
  • Experts Leaderboard
  • Contact Support
Brand-Logo
Subscribing is a smart move!
You can subscribe to this board after you log in or create your free account.
Forum-Icon

Create your free account or log in to subscribe to the board - and gain access to more than 10,000+ support articles along with insights from experts and peers.

Register today for FREE

Register Now

Already have an account? Login

Terms & Conditions Privacy Notice Change your Cookie Settings © 2025 Schneider Electric

This is a heading

With achievable small steps, users progress and continually feel satisfaction in task accomplishment.

Usetiful Onboarding Checklist remembers the progress of every user, allowing them to take bite-sized journeys and continue where they left.

of