Subscribing is a smart move!
You can subscribe to this board after you log in or create your free account.
We Value Your Feedback!
Could you please spare a few minutes to share your thoughts on Cloud Connected vs On-Premise Services. Your feedback can help us shape the future of services.
Learn more about the survey or Click here to Launch the survey
Schneider Electric Services Innovation Team!
ETL export database
ETL - Extract Transform Load
EcoStruxure IT Advisor integrations for the advanced user
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
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
EcoStruxure IT Support
Submit a support request for additional assistance with EcoStruxure IT software.
Options
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Email to a Friend
- Printer Friendly Page
- Report Inappropriate Content
2689
Views
Link copied. Please paste this link to share this article on your social media post.
Select your language from the translate dropdown in the upper right.
ETL export database
EcoStruxureIT
2020-04-06
06:50 AM
Last Updated:
JLehr
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: |
|
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: Added audit_log_* and audit_log_configuration_item tables |
| 1.1.5 |
ITA 9.1.6 |
Added columns to configuration_item_power_path table: 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" |
| timestamp | bigInt | Timestamp of the event - milliseconds from 1970 | 1642503089498 |
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!
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.
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