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

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 import database

ETL - Extract Transform Load

EcoStruxure IT Advisor integrations for the advanced user

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

Related Forums

  • EcoStruxure IT forum

  • APC UPS Data Center & Enterprise Solutions Forum

Previous Next

Invite a Colleague

Found this content useful? Share it with a Colleague!

Invite a Colleague Invite

EcoStruxure IT Support

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

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

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

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

ETL import database

Picard EcoStruxureIT
‎2020-04-06 07:56 AM

Last Updated: Sisko JLehr Sisko ‎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.

 

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.

 

 

 

Attachments
Was this article helpful? Yes No
No ratings

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

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

Forums

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

Knowledge Center

Events & webinars

Ideas

Blogs

Get Started

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

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

Register today for FREE

Register Now

Already have an account? Login

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

This is a heading

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

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

of