How to Set Up an Import File in Excel

This section describes how to define an import file. The following topology is created:

• Two hosts

• Two IPs contained by each host

• Network (the IPs mentioned above are members of the network)

• An application with a corresponding process running on the host

This task includes the following steps:

1. Prerequisite

   Open a new Excel file and name it tutorial.xls.

2. Add a CI type

   Double-click the Sheet1 tab and rename it with the desired CI type. For this tutorial, use the name node.

   Note

   • Only use the CI type name, not the display name.

   • Type names are case sensitive.

3. Create Comment sheets - optional

   You can create Comment sheets that will not be imported into UCMDB, but that can be used to describe the data contained in the imported document.

   Double-click one of the Sheet tabs and rename it #Comment sheet.

   

   Note Comment sheet names must begin with the # sign.

4. Define CI key attributes

   Depending on the CIT, to store a CI in UCMDB, you must specify CI key attributes or attributes that participate in reconciliation rules. The names of the imported attributes can be defined as the column headings.

   Our node object only has one key attribute - host_key.

   

   To import a node CI into UCMDB, you must set the host_key attribute. You may do this by one of the following methods:

   • Set host_key in the view <IP address> <Domain>. (For example: 192.168.100.100 DefaultDomain.) This is enough to import a node CI into UCMDB.

   • Set host_key as the lowest MAC address of the attached network interface. This is not enough to import a node CI into UCMDB. You must also configure the following attributes:

     1. Set host_iscomplete to true.

     2. Set values for the node attributes that allow the node to be identified by the reconciliation rule. Note that the node reconciliation rule also allows identification of the nodes linked to this node IP address or network interface CIs. If you prefer to identify nodes by linked CIs, you must ensure the Excel document also has the imported IP/Network interface CIs, and the relationships between node CIs and IP/ network interface CIs.

   Note

   • The column headings must be attribute names, not display names.

   • Attribute names are case sensitive.

   You can show the node name and the operating system.

   

   1. Define two nodes.

      

      Note Each row in the sheet (except the first one) represents a single CI.

   2. Use the same procedure to define IP addresses in a second Excel sheet, for example, Sheet2.

      

   3. Use the same procedure to define a network CI in a third Excel sheet, for example, Sheet3.

      

      running_software and process definitions are described in Add CIs with containers

5. Create Comment columns - optional

   If you want to have a Comment column with explanations of data, use the # sign before the column heading. Any data placed in this column will not be imported into UCMDB.

   

6. Add CIs with containers

   Objects that are contained within other objects cannot exist without them. For example, processes and running software cannot exist without the node they are running on. To show this relationship, a root_container attribute is needed. Because the container is in another CI, a reference to it is needed.

   Objects can be referenced in one of the following ways:

   • By creating an Excel definition reference to the object.

     The Excel definition referencing style is recommended because only the tab name (CI type name) and row number (the row number of the CI defined on the tab) are needed to identify any imported CI - the presence or absence of any key fields is not necessary, reconciliation rules are defined in UCMDB, and so on.

     Typical links appear as =node!A2, meaning that the node tab on the CI defined at row 2 is being referenced. It does not matter which column you are referencing; only the rows numbers are significant.

     Note Such references cannot be used if the Excel file was created from a CSV file or using some other non-Excel format.

     For more information about references, see Microsoft Excel documentation.

   • By setting a composition of the desired object key fields divided by the pipe symbol ('|').

     For example, to reference an IP address, the ip_address and routing_domain attributes are needed: 192.168.100.100|MyDomain.

     Note
     

     • The order of the key fields in the definition is important!
     • Many objects have no keyed attributes and are identified with reconciliation rules. For this reason, Excel references are preferred.

   • By creating an Excel definition reference to the object.

     The Excel definition referencing style is recommended because only the tab name (CI type name) and row number (the row number of the CI defined on the tab) are needed to identify any imported CI - the presence or absence of any key fields is not necessary, reconciliation rules are defined in UCMDB, and so on.

     Typical links appear as =node!A2, meaning that the node tab on the CI defined at row 2 is being referenced. It does not matter which column you are referencing; only the rows numbers are significant.

     Note Such references cannot be used if the Excel file was created from a CSV file or using some other non-Excel format.

     For more information about references, see Microsoft Excel documentation.

   • By setting a composition of the desired object key fields divided by the pipe symbol ('|').

     For example, to reference an IP address, the ip_address and routing_domain attributes are needed: 192.168.100.100|MyDomain.

     Note
     

     • The order of the key fields in the definition is important!
     • Many objects have no keyed attributes and are identified with reconciliation rules. For this reason, Excel references are preferred.

   1. Create a running_software using Excel references.

      

      Note To define an Excel reference, type an equal sign (=) in a cell, select the desired reference cell, and press Enter.

   2. Create a process using a composite key.

      

7. Define relationships

   To define relationships, create a sheet called relationships.

   Note You cannot import relationship CIs.

   All links (relationships) in UCMDB are directed. This means each link has a start and end point. Also, links have names that might have some attributes similar to other CIs.

   A link definition in an import file looks as follows:

   Start object reference -> link name -> End object reference [-> Attributes]
   

   Link attribute definitions are described in Add relationship attributes.

   The first row (column headings) displays the reason for the information. On this sheet, only the order of the parameters is important.

   1. Using Excel references, add informative captions and define member links between the IP subnet and first two IP addresses.

      

      In this image, defined formulas are displayed (for example, =ip_address!A2). In actuality, the values of referenced cells are shown.

   2. Using key composition, define the relationships between the two IP addresses and their routing domains as follows:

      IP key fields are ip_address and routing_domain. The composite key looks like 192.168.100.100|MyDomain.

      The relationship tab looks as follows:

      

      Note

      • Any type of reference can be chosen. You can use only one reference type in a cell.

      • Since the IP subnet CI has no key attributes in UCMDB 9.0x, they can be referenced only by Excel reference.

   3. Add a containment reference from node to ip_address and add a dependency reference from running_software to process:

      

      After importing this Excel file, the topology appears as follows:

      

8. Add relationship attributes

   Note This use case is not widespread, but the Import from Excel Workbook job offers such capability.

   Since many different types of links can be defined on the relationships tab in Excel, it is impossible to name columns with attribute names. For this purpose, the following notation is used:

   <Attribute name>< relationship_attr_delimiter><Attribute value>
   

   By default, for relationship_attr_delimiter, a pipe symbol ('|') is used.

   The description definition for the link dependency from running_software to process looks like description|The Business app depends from the Sample process.

   Now the relationships tab appears as follows:

   

   If many attributes must be added, they must be defined in additional columns in the dependency row.

   Note On the relationships tab, no captions are needed for the attribute columns. If the column heading is present, these columns are treated as comment columns.

9. Convert attribute types to UCMDB attribute types

   At the importing stage, each attribute is converted to the type defined in the UCMDB class model. This means that if an attribute is defined in UCMDB with a text value (for example, the attribute port in Service Address), but in the Excel file it has an integer value (for example, 5), it will be converted to the corresponding type.

   The following UCMDB attribute types are supported:

   boolean	date	double	enumerations
   float	integer	integer_list	long
   string	string_list	xml

   Note If the attribute cannot be converted to the type defined in UCMDB, it is skipped and you receive a warning in the UI.

   Two list types exist in UCMDB: integer_list and string_list. To import such types, the value delimiters are intended. They are integer_list_delimiter and string_list_delimiter respectively. The default values are separated by a comma (','), but this can be changed to a job parameter.

   If there is an attribute named some_int_list and it needs to be set using an integer list from 1 to 5, the cell in the relationships tab will look like:

   some_int_list|1,2,3,4,5

   • Enumerate attribute types

     Enumeration data types are supported for attributes. The job assumes the enumeration has been entered in human readable form and performs a search of the internal integer representation used in UCMDB.

     If a value is entered that is not an enumeration value, it is ignored and you receive a warning in the log.

     Note: It is not supported to enter an integer as an enumeration value. Replacing an enumeration value with an integer is also not supported. When defining CI attributes using the Enumeration/List option in the Add/Edit Attribute dialog box, make sure you specify the type of input values as enumeration, instead of integer.

     Because enumeration values are case sensitive in UCMDB, they are also case sensitive in Excel.

     For example, if SSN in the image below had been written in lower case letters, ssn, the job would send an error message because it would not find the ssn string in UCMDB.