unable to add trap definitions

[Snowman]

Have Nagions XI 5.5.0 with SNMP Trap Interface component installed
get the following error when trying to add a trap definition

SQL Error [nagiosxi] : Unknown column 'trapdata_custom_format' in 'field list'
when trying to add a trap definition

One click systems test fails on stage 2/9 - Nagios XI was not able to find our trap definition in the database.

[swolf]

Previously I've only seen this error in systems where we've already successfully added traps - I'm assuming this isn't the case here, so if you don't mind I'd like to gather some info:

1.) What Linux distribution is your XI server running?
2.) Is this a new install, or are you upgrading from a previous version? If you're upgrading, what version of Nagios XI were you using prior to 5.5.0?
3.) Just to make absolutely certain of your issue, can you also go to Admin->Monitoring Config->SNMP Trap Interface, click on the "Defined Traps" tab, and tell me if there is anything in that table?
4.) If you're able, please login to your Nagios XI database and show me the output from these two commands:

Code: Select all

use nagiosxi;
DESCRIBE xi_cmp_trapdata;

By default, you should be able to login to the database on the terminal using

Code: Select all

mysql -u root -pnagiosxi

, though you or another administrator may have changed the default password.

[Snowman]

yes I had found the nxti_import script which told me some fields were missing in the xi_cmp_trapdata table

MariaDB [nagiosxi]> describe
-> xi_cmp_trapdata
-> ;
+------------------------+--------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+--------------+------+-----+---------------------+-----------------------------+
| trapdata_id | int(11) | NO | PRI | NULL | auto_increment |
| trapdata_updated | timestamp | NO | | 0000-00-00 00:00:00 | on update CURRENT_TIMESTAMP |
| trapdata_enabled | smallint(6) | YES | | 1 | |
| trapdata_event_name | varchar(128) | NO | UNI | NULL | |
| trapdata_event_oid | varchar(256) | NO | | NULL | |
| trapdata_category | varchar(128) | NO | | NULL | |
| trapdata_severity | varchar(64) | NO | | NULL | |
| trapdata_format_string | text | YES | | NULL | |
| trapdata_exec | text | YES | | NULL | |
| trapdata_desc | text | YES | | NULL | |
+------------------------+--------------+------+-----+---------------------+-----------------------------+


so this is what I did
use nagiosxi;
alter table xi_cmp_trapdata add column trapdata_custom_format varchar(256);
alter table xi_cmp_trapdata add column trapdata_raw_data varchar(256);
alter table xi_cmp_trapdata add column trapdata_wizard_integration_enabled smallint(6);
alter table xi_cmp_trapdata add column trapdata_wizard_integration_data text;

are the type definitions correct?
are there any more missing?

[Snowman]

the install is the appliance(.ova) running on vwmare esxi 6.5
It was 5.4.13 which I ran the upgrade script (via GUI) to get to 5.5.0

the import of the snmptt.conf failed so there were no definitions & it could not create the test one either
Once adding those field in the db table - the snmptt.conf file imported and showed trap definitions in the GUI

[swolf]

Alright, I think I see the issue. It looks like we updated the SNMP trap schema in one place and not in another. I'll make sure we push a fix in the next patch.

Your new columns are pretty close, but still might not work due to the default values and other keywords.
Specifically, the trap interface might not be writing the correct definitions to the snmptt.conf.nxti file, even if the database holds the correct information.
If you'd like to have the schema exactly as we have it, please try running these commands:

Code: Select all

DROP TABLE nagiosxi.xi_cmp_trapdata;
CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_cmp_trapdata` (
    `trapdata_id` int auto_increment,
    `trapdata_updated` timestamp NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp,
    `trapdata_enabled` smallint default 1,
    `trapdata_event_name` varchar(128) NOT NULL default '',
    `trapdata_event_oid` varchar(256) NOT NULL default '',
    `trapdata_category` varchar(128) NOT NULL default '',
    `trapdata_severity` varchar(64) NOT NULL default '',
    `trapdata_custom_format` text,
    `trapdata_raw_data` text,
    `trapdata_exec` text,
    `trapdata_desc` text,
    `trapdata_wizard_integration_enabled` smallint default 0,
    `trapdata_wizard_integration_data` text,
    primary key (`trapdata_id`),
    unique (`trapdata_event_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

DROP TABLE nagiosxi.xi_cmp_trapdata_log;
CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_cmp_trapdata_log` (
    `trapdata_log_id` int auto_increment,
    `trapdata_log_event_name` varchar(128) NOT NULL default '',
    `trapdata_log_event_oid` varchar(50) NOT NULL default '',
    `trapdata_log_numeric_oid` varchar(100),
    `trapdata_log_symbolic_oid` varchar(100),
    `trapdata_log_community` varchar(20),
    `trapdata_log_trap_hostname` varchar(100),
    `trapdata_log_trap_ip` varchar(16),
    `trapdata_log_agent_hostname` varchar(100),
    `trapdata_log_agent_IP` varchar(16),
    `trapdata_log_category` varchar(20) NOT NULL default '',
    `trapdata_log_severity` varchar(20) NOT NULL default '',
    `trapdata_log_uptime` varchar(20) NOT NULL default '',
    `trapdata_log_datetime` datetime,
    `trapdata_log_bindings` text,
    primary key (`trapdata_log_id`),
    unique (`trapdata_log_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

and let me know if this fixes your issue.

[Snowman]

I have made those database changes
All looks good so far - tests work and I can add the traps I need

thanks

[swolf]

No problem! I'm going to go ahead and close this thread but feel free to open another if you have any other issues.