ETL import database

[EcoStruxureIT]

The ETL import database is by its predefined schema providing an API to populated EcoStruxure IT Advisor with data.

The schema is graphically represented in the ETL Import Database Schema attached below.

Importing large amounts of data

When importing data, the affected rooms are locked and other changes cannot be done at the same

time. So if you are importing large amounts of data, please consider doing so when it affects as few users as possible.

From version 8.0.2 we have tried to minimize the effects of locking the affected rooms by locking less and for a shorter amount of time.

There is a lock in the staging database that you can set while performing changes. As long as the lock is set, IT Advisor will wait before synchronizing the data from the staging database.

Description of tables, relations and classifications 

In the following section there is a distinction between tables and classifications.  Technically both are tables in the database, but conceptually there is a big difference. 

Please make sure to understand the Database Information State for the ETL import database. The information state is important since it is used for "bookkeeping" of data in the database. 

Tables are used to store information, whereas the classifications are used to classify the information which enables ITA to interpret and handle the information.

Classifications are "read-only" since they a closely coupled with a specific revision of IT Advisor. 

Relations describe the way information is related. 

Table: SCHEMA_VERSION

The SCHEMA_VERSION table contains only one record which is used to indicate the version of this database schema.
The current schema-version is 1.2.0, as can be seen in the overview found below. 

Version	ITA Version	Description
1.0.0		Initial schema design for the ETL feature
1.2.0	ITA 9.0.5	Added asset table and support for asset network ports and connections

 

Table: CONFIGURATION_ITEM

The configuration item is the central entity in the ETL data model. Therefore the central table of the staging-database is the CONFIGURATION_ITEM table. The table is expected to hold every piece of datacenter equipment/entity, both physical or virtual, that can provide information or data to IT Advisor. Examples include, but is not limited to servers, temperature sensors,... 

The last three fields are used for bookkeeping as described here. 

CONFIGURATION_ITEM
Field	Data type	Description	Required	Sample data
ci_id	varchar(255)	Unique identifier of item (primary key).	Mandatory	"server_001"
cit_id	BigInt	Specifies the type of this item. The value is a foreign key to values in the CONFIGURATION_ITEM_TYPE table.	Mandatory	1
ci_name	varchar(255)	Name of the item.	Mandatory	"Server XYZ"
ci_barcode	varchar(255)	Barcode of the item.	Optional	"IT1234567890"
ci_location	varchar(255)	Location of item.	Optional	"Behind Rack A in aisle B"
ci_model_number	varchar(255)	Item model number.	Optional	"DL360"
ci_model_name	varchar(255)	Item model name.	Optional	"HP Proliant"
ci_serial_number	varchar(255)	Item serial number.	Optional	"KN20623BY9"
ci_manufacturer	varchar(255)	Manufacturer of item.	Optional	"HP"
ci_ip_address	varchar(255)	Main ip-address of item.	Optional	"192.168.1.75:8080/adminConsole.cgi"
ci_description	varchar(255)	Description of item.	Optional	"Honeypot in DMZ"
ci_fingerprint	varchar(255)	Some unique "fingerprint" of the item, that may make it easier to match with items in the ITA model.	Optional
state_flag	varchar(16)	Indicates the state of the information. See section on information state above	Mandatory	new, changed, deleted, synced
state_last_updated	long	Timestamp set by the external system whenever the information in the row/record is changed.
state_last_synchronized	long	Timestamp used by SWO to indicate when the row was last successfully read and synchronized.

When modeling a data center, configuration items often have a natural parent-child relationship with each other. As an example servers are placed in racks which are placed in rows in a room. These relation-ships are captured in a relation-table instead of directly on the configuration. This allows the configuration items to participate in multiple relations, and to model different type of relations. 

Classification: CONFIGURATION_ITEM_TYPE

The term configuration item is very broad and can be used to describe just about anything in the data center. To make it possible to distinguish between different types of configuration items, they are classified with a configuration_item_type.

The configuration_item_type information is used in IT Advisor to decide how to store, represent and utilize the configuration item. The currently supported types are found in the table below.

CONFIGURATION_ITEM_TYPE
ID	Name	Description
0	Unspecified	Type used if no other type matches.
1	SERVER	This type indicates, that the item should be handled as a server.
2	SENSOR	This type indicates, that the item should be handled as a sensor.
3	AC
4	BATTERY_MANAGER
5	BRANCH-CIRCUIT_MONITOR
6	CAMERA_POD
7	CCTV_POD
8	CHILLER
9	CONSOLE_PORT_SERVER
10	DC_RECTIFIER
11	ENVIRONMENT
12	GENERATOR
13	INFRASTRUXURE
14	KVM_SWITCH
15	NETBOTZ_WALL
16	NETBOTZ_RACK
17	NETBOTZ_ENCLOSURE
18	NETWORK_DEVICE
19	POWER_DISTRIBUTION
20	POWER_METER
21	RACK_ACCESS_DEVICE
22	RACK_MANAGER
23	RACK_POWER_DISTRIBUTION
24	SENSOR_POD
25	SHARED_DEVICE
26	SNMP_DEVICE
27	STATIC_TRANSFER_SWITCH
28	TRANSFER_SWITCH
29	UPS
30	FAN_MODULE
31	IO_CARD
32	PSU
33	UCS_CHASSIS
34	UCS_MANAGER
35	UCS_SERVER
36	BLADE
37	VIRTUAL_MACHINE
38	DESKTOP_COMPUTER
39	NETWORK_SWITCH

Table: CONFIGURATION_ITEM_PROPERTY

The configuration item has a basic set of properties that are used for genome matching and identification in the UI. If other properties needs to be handled, they are stored in the CONFIGURATION_ITEM_PROPERTY table together with a reference to the configuration item. Each configuration_item_property has a type to classify the property. 

CONFIGURATION_ITEM_PROPERTY
Field	Data type	Description	Required	Sample data
ci_id	varchar(255)	Unique identifier of item (foreignkey)	Mandatory	"server_001"
cip_name	varchar(255)	The name of the property	Mandatory	"servicetag"
cip_value	varchar(255)	Value of property	Mandatory	"KN20623BY9"
pt_id	BigInt	Specifies the type of this property. The value is a foreign key to values in the PROPERTY_TYPE table	Mandatory	1
state_flag	varchar(16)	Indicates the state of the information. See section on information state above	Mandatory	new, changed, deleted, synced
state_last_updated	long	Timestamp set by the external system whenever the information in the row/record is changed.
state_last_synchronized	long	Timestamp used by SWO to indicate when the row was last successfully read and synchronized.

Note: this table has a complex unique primary key made up of (ci_id, cip_name) 

Classification: PROPERTY_TYPE

The different types of configuration_item_property is classified using the values in the table below. 

PROPERTY_TYPE
ID	Name	Description
0	Unspecified	Properties of this type are not handled by the system
1	TAG	This type indicates, that the property should be handled as a Tag
2	PROPERTY	This type indicates, that the property should be handled as a CustomProperty
3	GENOME	Not used. Reserved for future development.
4	ASSETPROPERTY	Use as a simple pass-through mechanism allowing for dynamic properties on configuration items

Depending on type, the properties are handled in slightly different ways. Properties of type TAG and PROPERTY are interpreted as Tags and Custom Properties and are automatically assigned to items in the ITA Data Model when configuration items are associated, they will not show up for configuration items that are not associated with items in the model.

Note

For tags: Currently it is not possible to create a new tag through ETL, the tags should be known by Operation before synchronizing with the staging database. Properties of type ASSETPROPERTY are set directly on the configuration items. They are passed through the synchronization system without interpretation, and show up on the configurations regardless of association. 

Table: CONFIGURATION_ITEM_MEASUREMENT

Datapoints and Measurements are stored in the CONFIGURATION_ITEM_MEASUREMENT table with a reference to the configuration_item and the class of measurement. 

Besides this, the measurements are further classified using unit and type. The measurement unit classification is used to ensure the correct magnitude of the measurement.

The class of a measurement specifies the overall type of measurement like, CPU utilization, voltage, current, temperature...

Measurements type is needed together with the interval_start and interval_end fields to distinguish between single measurements in space and time, minimum, average and maximum values over a time period.

Power and current values

For power measurements you have to provide both a peak power measurement and an average power measurement.

For current measurements you can only use peak measurement.

CONFIGURATION_ITEM_MEASUREMENT
Field	Data type	Description	Required	Sample data
cim_id	varchar(255)	Unique identifier of item (primary key).	Mandatory	"measurement_001"
ci_id	varchar(255)	Specifies the item that this measurement is associated with. The value is a foreign key to values in the CONFIGURATION_ITEM table.	Mandatory	"server_1"
cim_subid	varchar(255)	A subid for the measurement which is used with branch circuit monitoring to distinguish between submodules.	Optional	"breaker_01"
cim_value	float	The "measured" value.	Optional	7.35
mu_id	BigInt	Specifies the unit of this measurement. The value is a foreign key to values in the MEASUREMENT_UNIT classification.	Optional	1
mc_id	BigInt	Specifies the class of this measurement. The value is a foreign key to values in the MEASUREMENT_CLASS  classification.	Optional	3
mt_id	BigInt	Specifies the type of this measurement. The value is a foreign key to values in the MEASUREMENT_TYPE classification.	Optional	2
cim_interval_starttime	long	Start time for the measurement	Optional	123400
cim_interval_endtime	long	End time for the measurement	Optional	123500
state_flag	varchar(16)	Indicates the state of the information. See section on information state above	Mandatory	new, changed, deleted, synced
state_last_updated	long	Timestamp set by the external system whenever the information in the row/record is changed.
state_last_synchronized	long	Timestamp used by SWO to indicate when the row was last successfully read and synchronized.

Classification: MEASUREMENT_UNIT

This classification is used to ensure the correct labels and magnitudes of measurement when displayed and used in calculations inside IT Advisor.
MEASUREMENT_UNIT expresses the measurement unit of a measurement. 

MEASUREMENT_UNIT
ID	Name	Description
0	Unknown	Unit less measurement or unit not known.
1	W	Watt
2	kW	Kilo Watt (1kW = 1000W)
3	MW	Mega Watt (1MW = 1000000W)
4	PCT	per cent
5	PML	per mille
6	B	Byte
7	KB	Kilobyte (1kB = 1024B)
8	MB	Megabyte (1Mb = 1024kB)
9	GB	Gigabyte (1GB = 1024MB)
10	TB	Terabyte (1TB = 1024GB)
11	PB	Petabyte (1PB = 1024TB)
12	EB	Exabyte (1EB = 1024PB)
13	K	Kelvin
14	F	Fahrenheit
15	C	Celsius
16	RPM	Revolutions per minute
17	MPS	Meter per second
18	Bar	Bar
19	Psi	Psi (pounds per square inch)
20	S	Second
21	Ms	Millisecond
22	Mys	Microsecond
23	Ns	Nanosecond
24	Bps	Bytes per second
25	Kbps	Kilobytes per second
26	Mbps	Megabytes per second
27	GBPS	Gigabytes per second
28	TBPS	Terabytes per second
29	V	Volt
30	KV	Kilo volt
31	A	Ampere
32	MA	Milliampere
33	Cfm	Cubic feet per minute
34	Gkg	Gram per kg
35	RH	Relative humidity
36	VA	Volt-ampere
37	Wh	Watt-hour – used for energy measurement (1000Wh = 3.6MJ)
38	kWh	Kilo Watt-hour = 1000Wh
39	MWh	Mega Watt-hour =1000kWh
40	VAh	Volt-ampere-hour – unit for apparent energy
41	kVAh	Kilo Volt-ampere-hour = 1000VAh
42	MVAh	Mega volt-ampere-hour = 1000kVAh
43	J	Joule
44	Unitless	Unitless measurement like factors – this is NOT the same as unknown

Classification: MEASUREMENT_CLASS

The MEASUREMENT_CLASS classification is used to indicate what is measured. The table below lists the different classes of measurements that is of interest to ITA.

Note: Most of these are included for future usage and are not used/interpreted in the current version of ETL Integration. The currently supported classes are highlighted.

MEASUREMENT_CLASS
ID	Name	Description
0	unspecified	Indicated that nothing is known about the measurement.
1	cpu_utilization
2	cpu_idle
3	cpu_wait
4	memory_usage
5	memory_swap
6	disk_io
7	disk_latency
8	net_io
9	power	You have to provide both a peak power measurement and an average power measurement.
10	power_single_phase
11	power_L1
12	power_L2
13	power_L3
14	current	Only PEAK_DAY and MAXIMUM measurement types are supported.
15	current_single_phase
16	current_L1
17	current_L2
18	current_L3
19	voltage
20	voltage_single_phase
21	voltage_L1
22	voltage_L2
23	voltage_L3
24	temperature_inlet
25	temperature_outlet
26	temperature_ambient
27	humidity_absolute
28	humidity_relative
29	pressure
30	velocity
31	time
32	time_elapsed
33	time_remaining
34	frequency
35	flow
36	volume
37	apparent power	Note: The value is imported as a sensor, and is not used in any calculations, tooltips, etc.
38	active power	Note: The value is imported as a sensor, and is not used in any calculations, tooltips, etc.
39	power factor
40	temperature delta

Classification: MEASUREMENT_TYPE

The MEASUREMENT_TYPE classification is used to indicate how a measurement was made, with regards to the factor of time. MINIMUM, MAXIMUM and AVERAGE classes are used to indicate that the measured value is aggregated over a period of time, whereas POINT classes are single measurements at a given point in time.

The time interval or point in time is specified in the CONFIGURATION_ITEM_MEASUREMENT table as cim_interval_starttime and cim_interval_endtime.

Note: Not all measurement types are supported for all measurement classes. The currently supported classes are highlighted.  

MEASUREMENT_TYPE
ID	Name	Description
0	Unspecified
1	MINIMUM	Indicates that the measurement is the smallest observed value in the given time interval
2	MAXIMUM	Indicates that the measurement is the largest observed value in the given time interval
3	AVERAGE	Indicates that the measurement is the average value over the given time interval
4	POINT	Indicates that the measurement is a point measurement at a specific point in time
5	AVERAGE_DAY	Indicates that the measurement is the average value for the day. This value is read by ITA in the same way as DCE data and is more useful to the ITA server than AVERAGE
6	PEAK_DAY	Indicates that the measurement is the largest observed value for the day. This value is read by ITA in the same way as DCE data and is more useful to the ITA server than MAXIMUM

Table: BREAKER_PANEL_MEASUREMENT

The breaker_panel_measurement table can be used for integrating power measurement from a branch-circuit monitoring system. You only need to specify either power or watts, but please note that there is no automatic conversion between watts and amps. In most cases if you have both the best option is to specify both.

Learn more about importing manual measurements

BREAKER_PANEL_MEASUREMENT
Field	Data type	Description	Match type	Sample data
id	varchar(255)	Unique identifier of the measurement (primary key)	Mandatory	"m_001"
ci_id	varchar(255)	Unique identifier of the item (which must have breaker panels) that this measurement originated on (foreignkey)	Mandatory	"pde_001"
panel_id	varchar(255)	Name of the breaker panel that have the measurement, this must match the panel in ITA	Mandatory	"Panel A"
circuit_number	INTEGER	Position on the breaker panel, this must match a circuit number on the given panel in ITA	Mandatory	5
power_phase	varchar(2)	Power phase of the measurement, only needed if multiple positions on the panel have the same circuit number	Optional	"L1", "L2" or "L3"
time	timestamp	When was the measurement made	Mandatory	1476057600000
amps	double precision	The measurement in amps	Optional	7.8
watts	double precision	The measurement in watts	Optional	1250.0
state_flag	varchar(16)	Indicates the state of the information. See section on information state above	Mandatory	new, changed, deleted, synced
state_last_updated	long	Timestamp set by the external system whenever the information in the row/record is changed.
state_last_synchronized	long	Timestamp used by SWO to indicate when the row was last successfully read and synchronized.

Table: ASSET

 Introduced in 9.0.5. Rows in this table represent rack-mounted equipment in the model directly, as opposed to CONFIGURATION_ITEMs representing external system Devices. This allows to add, update and delete equipment items.

ASSET
Field	Data type	Description	Match type	Sample data
asset_id	varchar(255)	Unique identifier of the asset (primary key)	Mandatory	"asset_01"
item_uuid	varchar(36)	Internal unique identifier of the asset in the model, assigned automatically by import logic or used to set up reference to existing equipment	Optional	"1a3ff992-a505-48fd-89bd-44c221331dc6"
asset_name	varchar(255)	Name of the asset	Optional	"Server A"
rack_name	varchar(255)	Name of the rack the asset should be added or looked for; rack name should be unique in the room	Mandatory	"Rack 1"
room_name	varchar(255)	Name of the room the asset belongs to; should be unique within the solution, or room_parent_location should be used to identify the correct room; as of now, storages are not supported	Mandatory	"Room 1"
room_parent_location	varchar(255)	Used to find the correct room; if room_name is unique within the solution, this field is optional	Optional	"Location 1"
enclosure_name	varchar(255)	Name of the enclosure, if the row represents switch or blade with it	Optional	"Enclosure 1"
enclosure_slot	Bigint	Slot within the enclosure where switch or blade should be located; counted from left to right then rows from top to bottom	Optional	3
rack_mounting_position	Bigint	U-position or VU-position of the asset mounted in the rack; optional for switches and blades mounted in enclosures or if rack_mounting_type is one of rear_left or rear_right	Optional	1
rack_mounting_type	varchar(255)	How asset is mounted in the rack, see details below; ignored if the asset is mounted in enclosure, otherwise mandatory	Optional	"FRONT"
manufacturer	varchar(255)	Manufacturer used to lookup for correct model in Custom Catalog or Genome Library; optional if it is possible to find unique model by part_number and/or model_name	Optional	"HP"
model_name	varchar(255)	Model name that matches to Genome from Custom Catalog or Genome Library; optional if it is possible to find unique model by part_number	Optional	"HP Proliant"
part_number	varchar(255)	Part number that matches to Genome from Custom Catalog or Genome Library; optional if it is possible to find unique model by model_name	Optional	"DL360"
description	varchar(255)	Description of the asset	Optional	"Honeypot in DMZ"
barcode	varchar(255)	Barcode, must be unique within the solution	Optional	"IT0123456789"
serial_number	varchar(255)	Serial number of the asset	Optional	"ABC"
state_flag	varchar(16)	Indicates the state of the information. See section on information state above	Mandatory	new, changed, deleted, synced
state_last_updated	long	Timestamp set by the external system whenever the information in the row/record is changed
state_last_synchronized	long	Timestamp used by SWO to indicate when the row was last successfully read and synchronized

The rack_mounting_type is matched against the following values (ignoring case) 

rack_mounting_type
Name	Description
FRONT
REAR
REAR_LEFT	Rack PDUs and ATSes only.
REAR_RIGHT	Rack PDUs and ATSes only.
FRONT_LEFT	Only allowed in Racks with vertical u-positions.
FRONT_RIGHT	Only allowed in Racks with vertical u-positions.
TOP	Only allowed in Racks with external top u-positions. Available since ITA 9.2.1
BOTTOM	Only allowed in Racks with external bottom u-positions. Available since ITA 9.2.1

Table: ALARM

The ETL integration is not well suited to handling alarms due to the inherited latency in information-propagation through the system.
Nevertheless, it is possible to raise alarms on configuration items by adding them to the ALARM table. 

ALARM
Field	Data type	Description	Match type	Sample data
alarm_id	varchar(255)	Unique identifier of alarm (primary key)	Mandatory	"alarm_01"
ci_id	varchar(255)	Unique identifier of the item that this alarm concerns (foreignkey)	Mandatory	"server_001"
alarm_severity	varchar(64)	Severity of alarm. Recognized values are critical, failure, error and warning. Not case sensitive	Optional	"Error"
alarm_state	varchar(64)	What is the state of the alarm. Recognized values are active and resolved . Not case sensitive	Optional	"Active"
alarm_message	varchar(255)	Text describing the problem. The text will be show as is, inside ITA	Optional	"Chiller malfunction – refrigerant pressure too high"
alarm_occured	timestamp	When was to alarm observed by the external system	Optional
alarm_resolved	timestamp	When was the alarm resolved on the external system	Optional
state_flag	varchar(16)	Indicates the state of the information. See section on information state above	Mandatory	new, changed, deleted, synced
state_last_updated	long	Timestamp set by the external system whenever the information in the row/record is changed.
state_last_synchronized	long	Timestamp used by SWO to indicate when the row was last successfully read and synchronized.

The alarm_severity is matched against the following values (ignoring case) 

alarm_severity
Name	Description
critical	Critical alarm
failure	Incident in alarming system

If the severity does not match one of these values, the alarm is assumed to be pure informational. 

Note: There is no way of resolving the alarms directly from IT Advisor. 
The alarm_state is matched against the following values (ignoring case) 

alarm_state
Name	Description
Active	Alarm is active
Resolved	Alarm is inactive

Table: CONFIGURATION_ITEM_RELATION

Relations between items are modeled using the CONFIGURATION_ITEM_RELATION table. Currently this is reserved for future use. To model a relation, both ends are referenced together with a classification of the relation. 

CONFIGURATION_ITEM_RELATION
Field	Date type	Description	Match Type	Sample data
cir_id	varchar(255)	Unique identifier of relation (primary key)	Mandatory	"cable_001"
ci_id_a	varchar(255)	Id of start point of this relation. This is a foreign key to the CONFIGURATION_ITEM table	Optional	"server_1"
ci_id_b	varchar(255)	Id of end point of this relation. This is a foreign key to the CONFIGURATION_ITEM table	Optional	"server_2"
rt_id	BigInt	Used to indicate the type of this relation. The value is a foreign key in the RELATION_TYPE classification.	Optional	0
state_flag	varchar(16)	Indicates the state of the information. See section on information state above		new, changed, deleted, synced
state_last_updated	long	Timestamp set by the external system whenever the information in the row/record is changed.
state_last_synchronized	long	Timestamp used by SWO to indicate when the row was last successfully read and synchronized.

Classification: RELATION_TYPE

This classification is used to specify the kind of relation being modeled between two items. 

RELATION_TYPE
ID	Name	Description
0	unspecified	Indicated that nothing is known about the relation.
1	a_inside_b	Reserved for future usage
2	b_inside a	Reserved for future usage
3	a_powered_by_b	Reserved for future usage
4	b_powered_by_a	Reserved for future usage
5	a_connected_to_b	Reserved for future usage
6	a_placed_left_of_b	Reserved for future usage
7	a_placed_right_of_b	Reserved for future usage
8	a_placed_in_front_of_b	Reserved for future usage
9	a_placed_behind_b	Reserved for future usage
10	a_placed_under_b	Reserved for future usage
11	a_placed_above_b	Reserved for future usage
12	a_vm_inside_b	Reserved for future usage

Table: RELATION_PROPERTY

Information, besides the actual type of a relation, can be associated with the relation through RELATION_PROPERTY. 

RELATION_PROPERTY
Field	Date type	Description	Match Type	Sample data
cir_id	varchar(255)	Reference to the CONFIGURATION_ITEM_RELATION	Mandatory	"cable_001"
rp_name	varchar(255)	The name of this property	Mandatory	"cable length"
rp_value	varchar(255)	The value of this property. The value is shown as is, and will not be interpreted or used in calculations.	Optional	"50m"
rpt_id	BigInt	Used to indicate the type of this relation property. The value is a foreign key in the RELATION_PROPERTY_TYPE classification.	Optional	0
state_flag	varchar(16)	Indicates the state of the information. See section on information state above		new, changed, deleted, synced
state_last_updated	long	Timestamp set by the external system whenever the information in the row/record is changed.
state_last_synchronized	long	Timestamp used by SWO to indicate when the row was last successfully read and synchronized.

Note: this table has a complex unique primary key made up of (cir_id, rp_name) 
As an example a simple parent-child relationship between a blade chassis and a blade may require a "placement"-property, where as a cable between two servers may need properties like cable-length, type, speed, etc.
As with the other entities in the staging-database, a RELATION_PROPERTY is classified by a type (rpt_id) 

Classification: RELATION_PROPERTY_TYPE

Classification used for the type of relation-property. 

RELATION_TYPE
ID	Name	Description
0	unspecified	Indicated that nothing is known about the relation property.
1	cable_routing	Used to classify cable routing information.
2	software_environment	Used to specify the software environment of the relation

Note that the b_inside_a implies a child-parent relationship without the need of a specific relation-property. Therefore there is currently no explicit relation property type to capture information for this relation. 

Table: RELATION_ENDPOINT_PROPERTY

In some cases, extra information about the relation endpoints are needed. One example is for network-connections where the endpoint properties are used to specify network port information. 

RELATION_ENDPOINT_PROPERTY
Field	Date type	Description	Match Type	Sample data
cir_id	varchar(255)	Reference to a CONFIGURATION_ITEM_RELATION	Mandatory	"cable_001"
ci_id	varchar(255)	Reference to a CONFIGURATION_ITEM	Mandatory	"server_1"
rep_name	varchar(255)	The name of this property	Mandatory	"port speed"
rep_value	varchar(255)	The value of this property. The value is shown as is, and will not be interpreted or used in calculations.	Optional	"1Gbps"
rept_id	BigInt	Used to indicate the type of this relation endpoint property. The value is a foreign key in the RELATION_ENDPOINT_PROPERTY_TYPE classification.	Optional	4
state_flag	varchar(16)	Indicates the state of the information. See section on information state above		new, changed, deleted, synced
state_last_updated	long	Timestamp set by the external system whenever the information in the row/record is changed.
state_last_synchronized	long	Timestamp used by SWO to indicate when the row was last successfully read and synchronized.

Note: this table has a complex unique primary key made up of (cir_id, ci_id, rep_name) 

Classification: RELATION_ENDPOINT_PROPERTY_TYPE

Used for classification of the endpoint properties. Currently the classification is not used, but prepared for future usage. 

RELATION_ENDPOINT_PROPERTY_TYPE
ID	Name	Description
0	unspecified	Indicated that nothing is known about the relation.
1	network_port_number	Not used
2	network_port_type	Not used
3	network_module_name	Not used
4	network_port_speed	Not used
5	rear_connected_port	Not used

Table: CHANGE_TICKET

Information about change tickets or work orders that should be imported are placed in this table. Note that the specified priority and status should be references to the CHANGE_TICKET_PRIORITY and CHANGE_TICKET_STATUS tables.

CHANGE_TICKET
Field	Data type	Description	Required	Sample data
ticket_id	varchar(32)	Unique identifier of item (primary key).	Mandatory	"ticket_001"
assignee	varchar(64)	Specifies who is assigned to the work order change ticket	Optional	"Mr Black"
comment	varchar(3000)	Used for comments on the work order change ticket	Optional	"Install OS"
due_date	BigInt	Specifies the due date of the work order change ticket	Optional
summary	varchar(128)	Used for summary of the work order change ticket	Optional
priority	Integer	Specifies the priority of the change ticket The value is a foreign key in the CHANGE_TICKET_PRIORITY	Mandatory
status_order	Integer	Specifies the status of the change ticket The value is a foreign key in the CHANGE_TICKET_STATUS	Mandatory
state_flag	varchar(16)	Indicates the state of the information. See section on information state above	Mandatory	new, changed, deleted, synced
state_last_updated	long	Timestamp set by the external system whenever the information in the row/record is changed.
state_last_synchronized	long	Timestamp used by SWO to indicate when the row was last successfully read and synchronized.

Table: CHANGE_TICKET_PRIORITY

The possible priority classifications for change tickets and work orders.

CHANGE_TICKET_PRIORITY
Field	Data type	Description	Required	Sample data
priority	Integer	Specifies the priority of the change ticket	Mandatory
priority_message	varchar(32)		Mandatory
state_flag	varchar(16)	Indicates the state of the information. See section on information state above	Mandatory	new, changed, deleted, synced
state_last_updated	long	Timestamp set by the external system whenever the information in the row/record is changed.
state_last_synchronized	long	Timestamp used by SWO to indicate when the row was last successfully read and synchronized.

Table: CHANGE_TICKET_STATUS

The possible status classifications for change tickets and work orders.

CHANGE_TICKET_STATUS
Field	Data type	Description	Required	Sample data
status_order	Integer	Specifies the status of the change ticket	Mandatory
message	varchar(32)		Mandatory
state_flag	varchar(16)	Indicates the state of the information. See section on information state above	Mandatory	new, changed, deleted, synced
state_last_updated	long	Timestamp set by the external system whenever the information in the row/record is changed.
state_last_synchronized	long	Timestamp used by SWO to indicate when the row was last successfully read and synchronized.

Table: PORT_CONFIGURATION

Specifies network ports on equipment. The ports cannot overlap each other or with port configurations created in Operations client. The same rules as for the client apply.

For network speed the following units are supported: kbps, mbps, gbps, tbps, if no unit is specified it defaults to kbps, example speeds could be "100 kbps", "100 mbps" or "10 gbps".

PORT_CONFIGURATION
Field	Data type	Description	Required	Sample data
ci_id	varchar(255)	Reference to a CONFIGURATION_ITEM	Mandatory	"switch 001"
connector_type	varchar(255)	Connector type for the network port	Mandatory	"RJ45_REGULAR"
module_name	varchar(255)	Module name for the ports	Mandatory	"Module A"
start_port	Integer	Starting port number for the ports	Mandatory	0
port_count	Integer	Number of ports in the configuration	Mandatory	24
port_speed	varchar(255)	Network speed for the port configuration	Mandatory	"100 mbps"
state_flag	varchar(16)	Indicates the state of the information. See section on information state above	Mandatory	new, changed, deleted, synced
state_last_updated	long	Timestamp set by the external system whenever the information in the row/record is changed.
state_last_synchronized	long	Timestamp used by SWO to indicate when the row was last successfully read and synchronized.

Table: ASSET_PORT_CONFIGURATION

Introduced in 9.0.5. Specifies network ports on equipment. This table is similar to PORT_CONFIGURATION and should be used to manage ports on equipment looked up using asset uuid or by names: asset name, enclosure, rack, room and location.

The ports cannot overlap each other or with port configurations created in Operations client. The same rules as for the client apply. For network speed the following units are supported: kbps, mbps, gbps, tbps, if no unit is specified it defaults to kbps, example speeds could be "100 kbps", "100 mbps" or "10 gbps".

ASSET_PORT_CONFIGURATION
Field	Data type	Description	Required	Sample data
id	varchar(255)	Identifier of port configuration	Mandatory	"port_001"
asset_item_uuid	varchar(36)	Internal unique identifier of the asset in the model	Optional	"1a3ff992-a505-48fd-89bd-44c221331dc6"
asset_name	varchar(255)	Asset name; mandatory if asset by-name lookup is used	Optional	"Server 1"
asset_enclosure	varchar(255)	Asset enclosure name	Optional	"Enclosure 1"
asset_rack	varchar(255)	Asset rack name; mandatory if asset by-name lookup is used	Optional	"Rack 1"
asset_room	varchar(255)	Asset room name; mandatory if asset by-name lookup is used	Optional	"Room 1"
asset_location	varchar(255)	Room parent location name; mandatory if asset by-name lookup is used and room name is not unique	Optional	"Floor plan 1"
connector_type	varchar(255)	Connector type for the network port	Mandatory	"RJ45_REGULAR"
module_name	varchar(255)	Module name for the ports	Mandatory	"Module A"
start_port	Integer	Starting port number for the ports	Mandatory	0
port_count	Integer	Number of ports in the configuration	Mandatory	24
port_speed	varchar(255)	Network speed for the port configuration	Mandatory	"100 mbps"
state_flag	varchar(16)	Indicates the state of the information. See section on information state above	Mandatory	new, changed, deleted, synced
state_last_updated	long	Timestamp set by the external system whenever the information in the row/record is changed.
state_last_synchronized	long	Timestamp used by SWO to indicate when the row was last successfully read and synchronized.

The connector_type is matched against the following values (ignoring case)

connector_type
Name	Description
FIBER_REGULAR
FIBER_UPLINK
RJ45_REGULAR
RJ45_UPLINK
APC_LAN_REGULAR
APC_LAN_UPLINK
IP_KVM_UPLINK
IP_KVM_REGULAR
FIBER_SC_REGULAR
FIBER_SC_UPLINK
FIBER_LC_REGULAR
FIBER_LC_UPLINK
FIBER_ST_REGULAR
FIBER_ST_UPLINK
FIBER_MTRJ_REGULAR
FIBER_MTRJ_UPLINK
FIBER_FC_REGULAR
FIBER_FC_UPLINK
FIBER_MPO_MTP_REGULAR
FIBER_MPO_MTP_UPLINK
COAX_REGULAR
COAX_UPLINK
TERA_REGULAR
TERA_UPLINK
GG45_REGULAR
GG45_UPLINK
ARJ45_REGULAR
ARJ45_UPLINK
OTHER_REGULAR
OTHER_UPLINK

Table: NETWORK_CONNECTION

Network connections between both existing ports and ports that are just being created are specified here. The connections is not limited to only those created with ETL, but can also be to ports created in Operations using the client.

Since 9.0.5 it is possible to manage network connections using reference to ASSETs, so reference to CONFIGURATION_ITEMs is optional. Asset is looked up by using either from_asset_item_uuid or by finding equipment by names of: location, room, rack, enclosure (for blades and switches) and the asset name itself.

NETWORK_CONNECTION
Field	Data type	Description	Required	Sample data
id	varchar(255)	Unique identifier of the network connection (primary key)	Mandatory	"connection001"
from_id	varchar(255)	Reference to a CONFIGURATION_ITEM; mandatory if configuration_item lookup is used	Optional	"switch002"
from_asset_item_uuid	varchar(36)	Internal unique identifier of the asset in the model	Optional	"1a3ff992-a505-48fd-89bd-44c221331dc6"
from_asset_name	varchar(255)	Asset name; mandatory if asset by-name lookup is used	Optional	"Server 1"
from_asset_enclosure_name	varchar(255)	Asset enclosure name	Optional	"Enclosure 1"
from_asset_rack	varchar(255)	Asset rack name; mandatory if asset by-name lookup is used	Optional	"Rack 1"
from_asset_room	varchar(255)	Asset room name; mandatory if asset by-name lookup is used	Optional	"Room 1"
from_asset_location	varchar(255)	Room parent location name; mandatory if asset by-name lookup is used and room name is not unique	Optional	"Floor plan 1"
from_connector_type	varchar(255)	Connection type for the from item	Mandatory	"RJ45_REGULAR"
from_module_name	varchar(255)	Module name for the from item	Mandatory	"Module A"
from_port_number	integer	Port number for the from item	Optional	17
is_from_patch_panel_rear	integer	Is the connection from the rear of a patch panel	Optional	0
to_id	varchar(255)	Reference to a CONFIGURATION_ITEM; mandatory if configuration_item lookup is used	Optional	"server007"
to_asset_item_uuid	varchar(36)	Internal unique identifier of the asset in the model	Optional	"1a3ff992-a505-48fd-89bd-44c221331dc6"
to_asset_name	varchar(255)	Asset name; mandatory if asset by-name lookup is used	Optional	"Server 1"
to_asset_enclosure_name	varchar(255)	Asset enclosure name	Optional	"Enclosure 1"
to_asset_rack	varchar(255)	Asset rack name; mandatory if asset by-name lookup is used	Optional	"Rack 1"
to_asset_room	varchar(255)	Asset room name; mandatory if asset by-name lookup is used	Optional	"Room 1"
to_asset_location	varchar(255)	Room parent location name; mandatory if asset by-name lookup is used and room name is not unique	Optional	"Floor plan 1"
to_connector_type	varchar(255)	Connection type for the to item	Mandatory	"RJ45_REGULAR"
to_module_name	varchar(255)	Module name for the to item	Mandatory	"Module A"
to_port_number	integer	Port number for the to item	Optional	1
is_to_patch_panel_rear	integer	Is the connection to the rear of a patch panel	Optional	0
barcode	varchar(255)	Barcode for the connection	Optional	"87654321"
description	varchar(255)	Description for the connection	Optional	"Description for the connection"
install_date	timestamp	Installation date for the connection	Optional	"2015-01-31"
length	double precision	Length of the network cable	Optional	100.0
manufacturer	varchar(255)	Manufacturer of the network cable	Optional	"Network Cable Manufacturer"
model_name	varchar(255)	Model name for the network cable	Optional
name	varchar (255)	Name for the network cable (if relevant)	Optional
part_number	varchar (255)	Part number for the network cable	Optional
serial_number	varchar (255)	Serial number for the network cable	Optional	"12345678"
state_flag	varchar(16)	Indicates the state of the information. See section on information state above	Mandatory	new, changed, deleted, synced
state_last_updated	long	Timestamp set by the external system whenever the information in the row/record is changed.
state_last_synchronized	long	Timestamp used by SWO to indicate when the row was last successfully read and synchronized.

Table: CABLE_TYPE

When importing network connection you can specify cable types for the connections. The cable type should match a cable type already specified in ITA.

CABLE_TYPE
Field	Data type	Description	Required	Sample data
id	varchar(255)	Unique identifier for the cable type (primary key).	Mandatory	"cable_type_001"
description	varchar(255)	Description of the the type of cable.		"KVM cable"
blue	SMALLINT	The blue part of the RGB color for the cable type.		255
green	SMALLINT	The green part of the RGB color for the cable type.		255
red	SMALLINT	The red part of the RGB color for the cable type.		255

Table: SYSTEM_LOCK

Holds a single row with value SYSTEM_LOCK for column lock_name. The ETL jobs lock on this rows when an import starts. You can create an exclusive lock on the row to prevent import from running while staging database is being updated.