How do I customize auxiliary database tables?

Custom auxiliary database table attributes

Some NSP Analytics reports require data that is not available by default. Data such as location names, geographic co-ordinates, and maintenance windows must be imported to an auxiliary database in order to be included in reports. The NSP and NFM-P have a mechanism for the creation and management of auxiliary database tables and content.

An XML file that you create defines the table columns and data types to add to an auxiliary database. After you import a table definition to the NSP or NFM-P, an operator can add data records to the table using a CSV file whose record format matches the format defined in the XML file. An operator can also delete one or more tables, or the content of a table.

Note: A data-import operation appends the new records to the table, and does not affect the existing table contents or structure.

Custom auxiliary database table attributes are retained through system upgrades, and are included in auxiliary database backup and restore operations.

Table definition file format

Figure 20-1, Custom table definition file format, shows a table definition XML file that contains the column definitions for two custom tables.

Figure 20-1: Custom table definition file format
<customTablesConfig organization="OurCompany" name="CustomTableDefs"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:noNamespaceSchemaLocation="./schema/customtables.xsd">
   <customTables>
      <customTable
         name="table1"
         description="This is table 1"
         orderBy="column1">
         <columns>
            <column name="column1" type="STRING"  length="8" encoding="RLE" />
            <column name="column2" type="INTEGER" />
            <column name="column3" type="FLOAT"   />
            <column name="column4" type="BOOLEAN" />
            <column name="column5" type="NUMERIC" precision="12" scale="4" />
         </columns>
      </customTable>
      <customTable
         name="table2"
         description="This is table 2"
         segmentedBy="column1,column2">
         <columns>
            <column name="column1" type="STRING" length="64" />
            <column name="column2" type="STRING" length="64" />
         </columns>
      </customTable>
   </customTables>
</customTablesConfig>

Table 20-1, Custom attribute definition elements lists and describes the configurable elements in a custom table definition.

Table 20-1: Custom attribute definition elements

Element

Description

description

Text that describes the table; optional

orderBy

Comma-separated list of column names that define how the table data is to be ordered; optional

segmentedBy

Comma-separated list of column names that define how the table is to be segmented; optional

column name

Table column name

type

One of the following:

  • STRING—1 to 4096 characters

  • INTEGER

  • FLOAT

  • BOOLEAN

  • NUMERIC

precision

Maximum number of significant digits, represented by a positive integer less than or equal to 1024; required for NUMERIC data type

scale

Maximum number of digits to the right of the decimal point in a NUMERIC data type, represented by a positive integer less than or equal to the precision value; if omitted, defaults to 0

If the number of decimal digits in a data value exceeds the scale value, the data value is rounded to the number of digits specified by the scale value.

encoding

Text encoding type for STRING data type; optional, default is AUTO

length

Field length; required for STRING data type

Functional description

For NFM-P, the management tool for custom tables is the customData.bash CLI script on a main server station.

For NSP, the management tool for custom tables is the customdata.bash CLI script on an active auxiliary database station.

You can use the tool to do the following:

Note: In order for the customData.bash script to function on NFM-P, the main server configuration must include an auxiliary database.

Note: In order to run the customdata.bash script on NSP, the NSP deployment must include an auxiliary database, and NSP is started so that NSP can create auxiliary database users.

Note: If a string data value to be imported includes a comma, you must precede the comma with a backslash to prevent the comma from being interpreted as a CSV file delimiter.

The customData.bash script and customdata.bash script have the following operating characteristics:

Static custom data tables in NSP

Static custom data table definitions are included in NFM-P, which creates and upgrades these tables automatically.

If you want to use static custom data tables in NSP, you must install the static custom data table definitions. The NSP custom data tool includes these definitions in the /opt/nsp/nfmp/auxdb/install/custom-data/table-definitions/predefined/ directory: