ETL examples, troubleshooting, and tips

These example templates can be uploaded to the server to import example data, typically from an Excel sheet. They provide a good starting point for developing your own transformations.   Network Ports and Network Connections Import Example   The files below show how port configurations and network connections can be imported with ETL. To use the template:   Upload the files below to the /data/pentaho/import folder. Create a new staging database. Setup a new ETL Integration in the ITA client, and choose the "Network Import" job/transformation. After the initial synchronisation has been done, associate the new devices to equipment. Force the synchronisation to run again, or wait for the next scheduled synchronisation to run. The network ports and connections should be imported and added to the associated equipment. The ETL integration examples.zip file attached below contains: Network Import.kjb  PortConfiguration.ktr  Network.ktr  ConfigurationItems.ktr  Connections.xls    Import Examples   This section focuses on how to model real-life "scenarios" in the import database, and where the information is used/displayed inside EcoStruxure IT Advisor.    Entity   To add a simple entity with only a few properties, only the CONFIGURATION_ITEM table is needed (Figure 1). The references to CONFIGURATION_ITEM_TYPE is used to identify the type of the items.   Figure 1   Simple Measurements   In slightly more complicated cases where simple measurements are associated with the ConfigurationItems, the following general data structure is needed. See Figure 2. This associates an average powerdraw of 193.5W to Odin (server_01).    Figure 2   Advanced Power Measurements   More advanced power measurements can be modeled by using the cim_subid field. See Figure 3. The field cim_subid is used to group measurements – in this case phase power measurements from a breaker-module in a pdu. The id used in cim_subid is used inside IT Advisor to enable branch circuit monitoring by matching the id to breaker names set up in IT Advisor. Please note that you have to provide both an average and a peak power measurement (the example in Figure 3 is for the peak measurement). Also note that that you have to specify the start and end time for the measurement. The time is given in ms since January 1st 1970.   Figure 3   Temperature Sensors   To make it possible to use temperature measurements from external systems in the Cooling Simulations, they need to be modeled as shown in Figure 4. It is not necessary to provide all three different types of temperatures. Each temperature measurement will result in one sensor inside IT Advisor, with the assigned temperature reading. The name of the sensor will be set to "temperatureSensor "+cim_subid.    Figure 4   Alarm   Alarms are associated with Configuration Items, therefore both the CONFIGURATION_ITEM and ALARM table are needed. Because of the database-constraints on these tables, the Configuration Item needs to be updated before the Alarm. Alarms will show up in IT Advisor even if the external device is not associated with a DataCenterItem in the IT Advisor model.    Figure 5   Tag   The data structure needed to map tags is shown in the diagram in Figure 6.  This associates the tag "sales" to the server Odin (server_01). Once the external device (Odin) has been associated with an item in the IT Advisor model, the tag will be associated with that item. If the tag does not already exist, it is created with the cip_value as a description.   If the tag is marked deleted (state_flag set to "deleted"), the tag will be unassociated from the associated DataCenterItem. The tag itself will NOT be deleted from the IT Advisor model. If the external item (Odin) is unassociated, the DataCenterItem will retain all tags, including those created through ETL. Subsequent changes in ETL to the tag are NOT reflected to the IT Advisor model.    Figure 6     Custom Property Custom properties are mapped in the same way as tags, only the classification (property type) differ. In this example (Figure 7) the property "owner=sales" is associated with Odin. Once the external item Odin is associated with a DataCenterItem, the CustomProperty "owner" will be created with the value "sales", and assigned to the DataCenterItem. If the property is marked deleted, it will be removed from the IT Advisor model. If external item is unassociated, the DataCenterItem will retain all its CustomProperties including those created through ETL.    Figure 7   Network Connection   Modeling of network connections is done by using CONFIGURATION_ITEM_RELATION and its related tables – most importantly the RELATION_ENDPOINT_PROPERTY table that is used to model information about network-ports. The requested data-structure can be seen in the diagram (Figure 8). The properties related to the network port, is used inside IT Advisor to create PortConfiguration objects. Among other things the PortConfigurations maintain a range of ports. The ETL synchronization job will try to make the smallest number of PortConfigurations for a piece of network equipment, by using port-ranges. To aid in this, the module-name is used for grouping.  Whenever module_name and connector_type match, the range of existing ports will be extended to include new ports (otherwise outside of range) -> you can't have the same module split in two different ranges eg. 1-15 and 16-32 Network connections and port configurations are only added to the IT Advisor model if both ends of the relation are associated with data center items.    The communication speed that a network-port can handle should be provided with one of the units "Kbps", "Mbps", "Gbps" or "Tbps".    The port type must be one of the following: 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, OTHER_REGULAR, OTHER_UPLINK.    Figure 8     Note the following when importing network connection data: 1. The external items must be associated with equipment, and the association must be saved to the server, before the network connections can be synchronized. 2. If adding or updating information on a RELATION_ENDPOINT_PROPERTY row the corresponding CONFIGURATION_ITEM_RELATION must be marked 'new' or 'changed' in the staging-database. 3. Data for each of the five specific endpoint property types must specified for each relation endpoint property mentioned in the RELATION_END_PROPERTY table. 

For IT Advisor on-premises only   This example uses MySQL8 and the MySQL JDBC driver version 8.2.0.   Download the JDBC driver.   SSH to the server. Navigate to /opt/jboss-as/modules/com/apc/etl/   Create the directory mysql-8_2_0 with the command: mkdir mysql-8_2_0​ Navigate to mysql-8_2_0 and create the directory main with the command: mkdir main​ Navigate to main and create the module.xml file with the command: vi module.xml​ Save the module.xml file and exit. Paste the following into the module.xml file: Note: This is the contents of the module.xml file from postgresql, in the directory /opt/jboss-as/modules/com/apc/etl/postgresql/main/ <?xml version="1.0" encoding="UTF-8"?>                <module xmlns="urn:jboss:module:1.0" name="com.apc.etl.postgresql">                               <main-class name="org.postgresql.Driver"/>                               <properties>                                              <property name="dialect" value="org.hibernate.dialect.PostgreSQL94Dialect"/>                                              <property name="driverclass" value="org.postgresql.Driver"/>                               </properties>                               <dependencies>                                             <system/>                                             <module name="javaee.api"/>                               </dependencies>                </module>   Change all properties where the postgresql driver is mentioned. And also make sure to add the .jar file path line in "<resources> <resource-root path="mysql-connector-java-8.2.0.jar"/> </resources>".  When you are finished, your module.xml file should look like this: <?xml version="1.0" encoding="UTF-8"?>                                <module xmlns="urn:jboss:module:1.0" name="com.apc.etl.mysql-8_2_0">                               <main-class name="com.mysql.cj.jdbc.Driver"/>                            <resources>                                          <resource-root path="mysql-connector-java-8.2.0.jar"/>                            </resources>                               <properties>                                              <property name="dialect" value="org.hibernate.dialect.MySQL8Dialect"/>                                              <property name="driverclass" value="com.mysql.cj.jdbc.Driver"/>                               </properties>                               <dependencies>                                              <system/>                                              <module name="javaee.api"/>                               </dependencies>                </module>     If you use MYSQL5, change <property name="dialect" value="org.hibernate.dialect.MySQL8Dialect"/> to <property name="dialect" value="org.hibernate.dialect.MySQL5Dialect"/>     Copy the JDBC driver .jar file from the downloaded location to the server. You can use the scp command, for example, <download location>/mysql-connector-java-8.2.0/mysql-connector-java-8.2.0.jar <username>@<SERVERIP>:/tmp Move the .jar file from the /tmp folder to /opt/jboss-as/modules/com/apc/etl/mysql-8_2_0/main/ Navigate to /opt/jboss-as/modules/com/apc/etl/main/   Open the module.xml file. Add <property name="mysql-8_2_0"/> to the <properties> section.   Add <module export="true" name="com.apc.etl.mysql-8_2_0"/> to the dependencies section. When you are finished, your module.xml file should look like this: <?xml version="1.0" ?>                                               <module xmlns="urn:jboss:module:1.1" name="com.apc.etl">                               <properties>                                              <property name="postgresql"/>                                              <property name="jtds-1_3_1"/>                                              <property name="mysql-8_2_0"/>                               </properties>                               <resources/>                               <dependencies>                                              <module export="true" name="com.apc.etl.jtds-1_3_1"/>                                              <module export="true" name="com.apc.etl.mysql-8_2_0"/>                               </dependencies>                </module>   Restart the IT Advisor server with the command: systemctl restart operations

Q: Why can't I connect to the postgresql database? The log file contains following statement: 2016-09-09 04:33:07 GMT 13316 export_etl_db FATAL:  pg_hba.conf rejects connection for host "10.11.12.13", user "postgres", database "export_etl_db", SSL off   A: The error message indicates that a user called postgres was trying to access the database export_etl_db from the host 10.11.12.13 but was rejected due to access-restrictions.   To fix the problem, you need to configure the postgresql-server to allow access. This is done by editing the configuration file pg_hba.conf placed in the folder /etc/isx-operations/postgresql/, adding the line  host export_etl_db postgres 10.11.12.13/32 md5  so that the configuration file looks something like this:   # Database administrative login by UNIX sockets local   all         postgres                          ident    # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD       # "local" is for Unix domain socket connections only #local   all         all                               md5 # IPv4 local connections: #host    all         all         127.0.0.1/32          md5 # IPv6 local connections: #host    all         all         ::1/128               md5       hostssl all all 10.216.73.208/32 cert hostssl all all 127.0.0.1/32 cert host staging_hp etluser_hp 10.216.73.208/32 md5 host staging_hp etluser_hp 127.0.0.1/32 md5 host staging_hp etluser_hp 10.216.0.0/16 md5 host export_export etluser_exp 10.216.73.208/32 md5 host export_export etluser_exp 127.0.0.1/32 md5 host export_export etluser_exp 10.216.0.0/16 md5    host export_etl_db postgres 10.11.12.13/32 md5   This tells postgres to allow host access (net-based access) to the database export_etl_db for user postgres IF and only if the source IP is 10.11.12.13 and if the correct password is provided. If the IP is likely to change (DHCP), you can set a netmask as 10.11.12.0/24.   If different users need access, one line per user is needed or, alternatively, you can type in the  all  keyword which matches any user.   After editing the configuration file, you need to reload postgres.   From version 9.1.x: # sudo systemctl restart postgresql