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