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