Integrations > Third Party Integrations > Importing Data from External Sources > Import from Database Job

Import from Database Job

This job uses a database table or database query as the source of the information, maps the information to CIs, and imports the CIs into UCMDB.

This section includes the following topics:

ClosedJob Details

The job details are as follows:

  • Adapter: Import from DB

  • Input CI Type: Database

  • Discovered CIs: ConfigurationItem

  • Required Protocol: SQL

This job has no trigger queries associated with it. The job tries to get the Instance name and Port using the attributes Name and Application Listening Port Number of the Input Database CI. If these attributes are empty, it uses the Instance Name and Port number defined in Generic DB Protocol (SQL) credentials.

ClosedDiscovery Adapter Parameters

The following parameters are included by default:

Parameter

Description
bulkSize

This parameter only works if the parameter flushObjects is true, in which case, when sending discovery results, it sets the size of chunks used to that number of CIs.

The default is 2,000 CIs.

ciType

Name of CIT to import.
flushObjects

This parameter allows customization of the reporting mechanism.

If true, the probe divides the discovery result into chunks, and sends each chunk to the UCMDB Server. This helps prevent out-of-memory issues where a large amount of data is sent. The chunk size can be configured with the bulkSize parameter.

If false (the default value), the probe sends the discovery result without dividing it into chunks.

mappingFile

XML file containing the mapping from column to attribute.

mappingString

The string containing mapping information used to map the Database column names and the attributes to import. You define this mapping in the following format:

  • mapping elements should be separated by commas;

  • each mapping element should be specified in a <column name>:<attribute name> format,

Example:

A_IP_ADDRESS:ip_address, A_IP_DOMAIN:ip_domain

schemaName

The name of the database schema.

sqlQuery

If a SQL query is specified, mapping is performed against its result. This parameter is ignored if tableName is defined.

tableName

If a table name is specified, mapping is performed against the table's columns.

For details on overriding an adapter parameter, see "Override Adapter Parameters" in HPE Universal CMDB Developer Reference Guide.

ClosedTables and Queries

The following use cases are supported by the Import from Database job (a single SQL query is performed):

  • Import data using the schema name and table name parameters:

    • The SQL query is generated from these parameters.

  • Import data specifying an arbitrary SQL query as the source of the data:

    • The SQL query is generated from the defined query. For more details, see Importing Data with an SQL Query.

ClosedDatabase, Schema, and Table Names

SQL naming conventions suggest a usage of a <database.schema.table> syntax for the fully qualified name of a table. Note, however, that each vendor treats the specification in a different way. DFM uses the following notation:

  • The schemaName parameter specifies the name of a database.

  • The tableName parameter specifies the name of a table.

  • A schema name cannot be specified in a parameter but can be included in a SQL query.

For Oracle, the SQL query is:

SELECT * FROM <schemaName.tableName>

For Microsoft SQL Server, the SQL query is:

SELECT * FROM dbo.tableName

Note The default dbo schema is used for Microsoft SQL Server.

ClosedImporting Data with an SQL Query

You can use arbitrarily-complex SQL query expressions, for example, joins, sub-selects and other options, as long as the query is valid and complies with the database usage. Currently, you must use a fully-qualified table name in the query according to the specific database.

ClosedColumn Types

Types enable you to specify, in the mapping file, the type of column that exists in the external source. For example, a database includes information about column types, and the value of this type needs to be included in the CI's attributes. This is done by adding a type element to the map element (in mapping_[your mapping file name].xml):

            <column type="int"></column>

Supported type attributes are:

  • string

  • Boolean

  • date

  • int

  • long

  • double

  • float

  • timestamp

    • Note

    • You use the type attribute for database mapping only.

    • If the column element does not include a type attribute, the element is mapped as a string.

    Example of adding a type attribute

    A database column has an integer type and can be either 0 or 1. This integer must be mapped to a Boolean attribute of a CIT in UCMDB. Use the binaryIntToBoolean converter, as follows:

            <map>
            <attribute>cluster_is_active</attribute>
            <column type="int">cluster_is_active</column>
            <converter module="import_converters">binaryIntToBoolean</converter>
        </map>

    type="int". This attribute specifies that the value of cluster_is_active should be retrieved as an integer, and that the value passed to the converter method should be an integer.

    If the cluster_is_active attribute of the CIT is of type integer, the converter is not needed here, and the mapping file should say:

            <map>
            <attribute>cluster_is_active</attribute>
            <column type="int">cluster_is_active</column>
        </map>
ClosedImporting CIs with Root Container Attribute

The main purpose of the Import from database job is importing CIs that do not require a root container attribute. For example: Node, IP address, etc. However, there are many CIs that depend on root CIs (for example: CPUs, SQL servers, etc) that cannot be deployed into UCMDB without the Node on which they are installed. In such cases, it is better to use a different discovery approach. For example, by importing data from Excel, you can import CIs with corresponding relationships (including the composition relationship). You can import such CIs using the Import from database job only if all of the following apply:

  1. The root CI (for example: Node) was already populated into UCMDB.
  2. It is possible to construct an SQL query to select the UCMDB ID of already imported root CIs from an external database.

In such cases, you can map the selected UCMDB ID of the root CI to the root container attribute of the contained CI.

Note Validation of data (checking if the root CI already exists in UCMDB) is not supported. It is your responsibility to correctly configure the population of the root container attribute.