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 21-1, Custom table definition file format, shows a table definition XML file that contains the column definitions for two custom tables.

Figure 21-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 21-1, Custom attribute definition elements lists and describes the configurable elements in a custom table definition.

Table 21-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:

create and delete tables
import, export, and delete data
list the currently defined custom tables
export a table schema

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:

The script automatically assigns a prefix and suffix to the name of a custom table.
- For NFM-P, references to the table in script operations after table creation must include the samdb prefix and _ct suffix; for example, customTable1 must be specified as samdb.customTable1_ct.
- For NSP, references to the table in script operations after table creation must include the custom_data prefix and _ct suffix; for example, customTable1 must be specified as custom_data.customTable1_ct.
For NFM-P, script operations that modify data, such as createTable, deleteTable, and importData, require the auxiliary database user password.
For NSP, all script operations require the auxiliary database user password.
During a createTable operation, a formatting error in a table definition causes all table creation during the operation to fail, and the script saves the table definition file as tmp/customtables.xml.template.
For NFM-P, the script logs each operation in the /opt/nsp/nfmp/server/nms/log/customdata.log; the maximum log size is five Mbytes.
For NSP, the script logs each operation in the /opt/nsp/nfmp/auxdb/install/custom-data/logs/ directory; the log file format is custom-data-yyyy.MM.dd-HH.mm.ss.log. A new log is produced each time the customdata.bash script is run.

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:

anl_details.xml
maintenance_window.xml