This documentation supports the 20.02 (12.0) version of BMC Discovery.To view an earlier version of the product, select the version from the Product version menu.

Configuring the RDB adapter

The BMC Discovery export framework is deprecated

The BMC Discovery export framework is deprecated in the 20.02 (12.0) release of BMC Discovery and will be removed in a future release. For new exports or integrations, you should consider Using-the-REST-API.


BMC Discovery Export includes a relational database (RDB) adapter that allows you to export and publish data to a specific set of database tables over JDBC.

Before you begin

Prior to creating an RDB adapter configuration, you will need to set up the database where the data will be exported to.
For further information about the database configuration, see RDB Adapter - Determining the Table Structure for your Mapping Files.

To configure the RDB adapter

  1. From the main menu, click the Administration icon. The Administration page displays. From the Model section, click Export. Then click the Adapter Configurations tab.
  2. Click the New link next to Relational Database Configurations.
     Export includes a number of database drivers (for SQL Server, Sybase and PostgreSQL). You can no longer add new drivers that can be used by the RDB adapter as the export feature is deprecated. 
  3. Select the driver from the Database Driver drop-down list.
  4. Enter the URL to connect to the JDBC database in the Connection URL field. For example,
    jdbc:jtds:sqlserver://mydbserver/export
  5. Enter the RDB user name into the Username field.
  6. Select the Set Password check box (only available if you are editing an existing adapter) and enter the password in the Password field.
     The JDBC adapter can write export statistics and dates to a manifest table after each mapping is complete. This feature is enabled by default on each adapter configuration. The manifest records the date of the export and the files that form part of it.
  7. To disable the inclusion of the manifest, uncheck Write export statistics to a manifest table.

To write export statistics to a manifest table

The JDBC adapter can write export statistics and dates to a manifest table after each mapping is complete. This feature is enabled by default on each adapter configuration.

If the feature is enabled but the table does not exist in the database, an error will be logged in the export logs after each mapping. The export will continue normally.

To create the manifest table in the database, use the following SQL command:

create table manifest (
  export_item varchar(50),
  insert_count int,
  ignore_count int,
  fail_count int,
  start_date timestamp,
  complete_date timestamp)

To determine the table structure for your mapping files

The RDB adapter exports data to tables in the database that are determined by the names used in the mapping files. This section describes how to determine the table structure that will be required for your mapping files.

See The Mapping File Format for more information about mapping files. For your refernce, there is generic SQL at the beginning of each mapping file to delete and create each of the required staging tables for that file.

The RDB adapter exports to a set of staging tables in a manner similar to a CSV export. It requires that the destination tables are empty when the export begins, as it performs no reconciliation and will attempt to INSERT records only.

The tables and fields that it exports to are determined by a simple set of rules:

  1. Each CI declaration in the mapping file exports to a different table.
  2. The table name is formed by concatenating the mapping file's name (without the .xml extension), an underscore (_) and the cmdb-name attribute of the CI.
  3. The fields of the table exported to by the main CI are taken from the fields declared in the main CI as-is.
  4. The fields of the table exported to by all sub CIs are taken from two places:
    • The first fields in the table are the main CI's identity fields.
    • The remaining fields in the table are the sub CI's fields.

Thus, the main CIs are exported to a table of their own. The sub-CIs are exported to a table of their own, but each sub-CI table contains the identity fields of the main CI that it was related to when it was exported.

For example, the following are the CI declarations from host.xml file, a mapping file shipped in the RDB mapping set:

<ci cmdb-name="host" main="true">
    <field src="hostname" dest="host_HostName" identity="true"/>
    <field src="name" dest="host_Name" identity="true"/>
    <field src="description" dest="Description"/>
    <field src="domain" dest="Domain"/>"
    <field src="model" dest="Model"/>
    <field src="serial" dest="Serial"/>
    <field src="ram" dest="RAM"/>
    <field src="workgroup" dest="Workgroup"/>
    <field src="vendor" dest="Vendor"/>
    <field src="os" dest="OS"/>
    <field src="os_version" dest="OsVersion"/>
    <field src="processor" dest="Processor"/>
  </ci>

  <ci cmdb-name="si" collection="true">
    <field src="si_name" dest="SI_Name" identity="true"/>
    <relationship cmdb-name="hostedsoftware"
     direction="main-to-sub"/>
  </ci>

  <ci cmdb-name="ipendpoint" collection="true"
      parent-is-identifier="true">
    <field src="netmask" dest="ipendpoint_Netmask" identity="true"/>
    <field src="ip_addr" dest="ipendpoint_IPAddress"      identity="true"/>
    <relationship cmdb-name="deviceinterface"
     direction="main-to-sub"/>
  </ci>

There are three CI declarations. The first is the main CI, and the following two are both sub-CIs. Therefore, this mapping file will export data to three tables:

  1. host_host - the "host" main CI
  2. host_si - the "SI" sub-CI
  3. host_ipendpoint - the "ipendpoint" sub-CI

Each table name is formed by concatenating the mapping file name, an underscore and the CI name.
 The following is the SQL required for each CI's table. This SQL, while generic, might need slight modifications to run in your database:

drop table host_host;
    create table host_host (
      host_HostName varchar(100) NOT NULL,
      host_Name varchar(100) NOT NULL,
      Description varchar(100),
      Domain varchar(100),
      Model varchar(100),
      Serial varchar(100),
      RAM integer,
      Workgroup varchar(100),
      Vendor varchar(100),
      OS varchar(100),
      OsVersion varchar(100),
      Processor varchar(100),
      primary key (host_HostName, host_Name)
    );

The fields for the host_host table were taken directly from the previous host CI declaration.

drop table host_si;
    create table host_si (
      host_HostName varchar(100) NOT NULL,
      host_Name varchar(100) NOT NULL,
      SI_Name varchar(100) NOT NULL,
      primary key (host_HostName, host_Name, SI_Name)
    );

The first two fields in the host_si table are the host CI's identity fields, and the remainder are the fields from the SI sub-CI.

drop table host_ipendpoint;
    create table host_ipendpoint (
      host_HostName varchar(100) NOT NULL,
      host_Name varchar(100) NOT NULL,
      ipendpoint_Netmask varchar(100) NOT NULL,
      ipendpoint_IPAddress varchar(100) NOT NULL,
      primary key (host_HostName, host_Name, ipendpoint_Netmask, ipendpoint_IPAddress)
    );

As with the SI sub-CI, the ipendpoint CI's fields are the host's identity fields and the ipendpoint fields.
 You should create primary keys in the tables that match the identity fields declared in the mapping files. This will prevent the exporter from inserting duplicate rows, or at least highlight that duplicate rows could exist.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*