SNMP Trap Interface

[cbeattie-unitrends]

Hello,

I've got a problem with too many SNMP traps. I don't know yet how I got them, but I need a way to tell Nagios XI it's okay to let them go. The SNMP Trap Interface page is showing records 1-5 of 15888165.

It's been related to a couple issues. /var/lib/mysql/nagiosxi/xi_cmp_trapdata_log.ibd is 23GB, which looks to be a lot more than I need to keep. I would actually be happy to delete all of the existing logged traps to get things started, but I can't figure out how to do that in a safe manner.

I've also got a couple files like this in /var/lib/mysql/nagiosxi:

Code: Select all

-rw-rw----  1 mysql mysql 9.3K Nov 12 07:05 #sql-1e4f5_143.frm
-rw-rw----  1 mysql mysql 992M Nov 12 14:28 #sql-1e4f5_143.ibd
-rw-rw----  1 mysql mysql 9.3K Nov 12 06:52 #sql-820_15ea10a.frm
-rw-rw----  1 mysql mysql  21G Nov 12 07:04 #sql-820_15ea10a.ibd
-rw-rw----  1 mysql mysql 9.3K Nov 12 16:58 #sql-9305_ba38.frm
-rw-rw----  1 mysql mysql  19G Nov 12 17:13 #sql-9305_ba38.ibd

As far as I can tell, they're temporary files left over from a failed operation. I've been looking through MariaDB docs to see if there's a way to delete them without causing orphan table errors, but I don't know enough about databases to know if MariaDB cares about them. They don't show up when I connect to the nagios database and issue a show tables command.

So, what I would like to know is:
1) How can I safely get rid of the logged SNMP traps?
2) Can I configure Nagios XI to limit the number of traps it keeps to prevent this happening in the future?
3) Can I delete those temporary table files without causing orphan table errors? Or, is there a way to fix orphan tables after I delete the files?

Thanks.

[swolf]

Hi @cbeattie-unitrends,

1. In general, we don't want to have users do anything direct with the database. However, in this particular instance, it's okay to just truncate the table (as long as you don't care about that data). This won't affect any part of the application outside of the SNMP Trap Interface.

In the mysql terminal:

Code: Select all

USE nagiosxi;
TRUNCATE TABLE xi_cmp_trapdata_log;

2. As of the current version, you're not able to put a limit on the absolute number of received traps. However, you can limit by age.

Go to Admin -> System Config -> Performance Settings -> Databases. Under "Nagios XI Database", change "Max SNMP Trap Age" from 90 days to whatever seems reasonable to you. Remember to "Update Settings" at the bottom.

3. I'm not 100% certain what these are, but if they remain after following the first two steps, reply to the thread again and we'll figure out a solution.

[cbeattie-unitrends]

Thanks, that helped get some space back and I can see which device is flooding XI with SNMP traps.

After truncating the database, the sql-9305_ba38 files disappeared. However, the other two sets are still in there:

Code: Select all

-rw-rw----  1 mysql mysql 9.3K Nov 12 07:05 #sql-1e4f5_143.frm
-rw-rw----  1 mysql mysql 992M Nov 12 14:28 #sql-1e4f5_143.ibd
-rw-rw----  1 mysql mysql 9.3K Nov 12 06:52 #sql-820_15ea10a.frm
-rw-rw----  1 mysql mysql  21G Nov 12 07:04 #sql-820_15ea10a.ibd

[tgriep]

If you run the following command, it will show you the size of the MYSQL tables.
Run it as root and then post the output here.

Code: Select all

echo "SELECT table_schema as 'Database', table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC;" |mysql -t -u root -pnagiosxi

[cbeattie-unitrends]

Code: Select all

+--------------------+----------------------------------------------+------------+
| Database           | Table                                        | Size in MB |
+--------------------+----------------------------------------------+------------+
| nagios             | nagios_externalcommands                      |     630.81 |
| nagios             | nagios_statehistory                          |     585.44 |
| nagios             | nagios_logentries                            |     138.34 |
| nagios             | nagios_notifications                         |     127.73 |
| nagiosxi           | xi_cmp_trapdata_log                          |      32.81 |
| nagios             | nagios_contactnotifications                  |      24.85 |
| nagios             | nagios_contactnotificationmethods            |      23.44 |
| nagiosxi           | xi_meta                                      |      20.61 |
| nagios             | nagios_servicestatus                         |      19.06 |
| nagios             | nagios_commenthistory                        |      15.38 |
| nagios             | nagios_services                              |       8.75 |
| nagios             | nagios_flappinghistory                       |       5.05 |
| nagios             | nagios_objects                               |       3.78 |
| nagiosxi           | xi_events                                    |       1.01 |
| nagios             | nagios_service_contacts                      |       0.99 |
| nagios             | nagios_downtimehistory                       |       0.79 |
| nagios             | nagios_serviceescalations                    |       0.71 |
| nagios             | nagios_hoststatus                            |       0.69 |
| nagios             | nagios_service_contactgroups                 |       0.62 |
| nagios             | nagios_serviceescalation_contactgroups       |       0.57 |
| nagiosxi           | xi_cmp_trapdata                              |       0.52 |
| nagiosxi           | xi_auditlog                                  |       0.46 |
| mysql              | help_topic                                   |       0.45 |
| nagios             | nagios_processevents                         |       0.31 |
| nagios             | nagios_hosts                                 |       0.31 |
| nagiosql           | tbl_host                                     |       0.22 |
| nagios             | nagios_eventhandlers                         |       0.21 |
| nagios             | nagios_acknowledgements                      |       0.20 |
| nagios             | nagios_conninfo                              |       0.14 |
| nagiosql           | tbl_info                                     |       0.13 |
| nagios             | nagios_servicegroup_members                  |       0.11 |
| mysql              | help_keyword                                 |       0.10 |
| nagios             | nagios_hostescalation_contactgroups          |       0.10 |
| nagios             | nagios_hostescalations                       |       0.09 |
| nagios             | nagios_hostgroup_members                     |       0.05 |
| nagios             | nagios_host_contactgroups                    |       0.05 |
| nagiosxi           | xi_mibs                                      |       0.05 |
| nagiosxi           | xi_usermeta                                  |       0.05 |
| nagiosql           | tbl_command                                  |       0.04 |
| nagiosql           | tbl_lnkHostToHosttemplate                    |       0.04 |
| nagiosxi           | xi_eventqueue                                |       0.04 |
| nagios             | nagios_systemcommands                        |       0.04 |
| nagiosxi           | xi_auth_tokens                               |       0.03 |
| nagiosxi           | xi_sessions                                  |       0.03 |
| nagiosql           | tbl_lnkHostToHostgroup                       |       0.03 |
| nagiosql           | tbl_servicetemplate                          |       0.03 |
| nagios             | nagios_customvariablestatus                  |       0.03 |
| mysql              | help_relation                                |       0.03 |
| nagios             | nagios_customvariables                       |       0.03 |
| nagiosql           | tbl_service                                  |       0.02 |
| nagiosxi           | xi_options                                   |       0.02 |
| nagios             | nagios_commands                              |       0.02 |
| nagiosql           | tbl_lnkServicedependencyToServicegroup_DS    |       0.02 |
| nagiosql           | tbl_lnkServiceescalationToServicegroup       |       0.02 |
| nagiosql           | tbl_permission_inactive                      |       0.02 |
| nagiosql           | tbl_permission                               |       0.02 |
| nagiosxi           | xi_incidents                                 |       0.02 |
| nagiosql           | tbl_lnkServicedependencyToServicegroup_S     |       0.02 |
| nagiosql           | tbl_hosttemplate                             |       0.01 |
| nagios             | nagios_comments                              |       0.01 |
| nagios             | nagios_configfilevariables                   |       0.01 |
| nagiosxi           | xi_users                                     |       0.01 |
| nagiosql           | tbl_contact                                  |       0.01 |
| nagios             | nagios_contact_notificationcommands          |       0.01 |
| nagiosql           | tbl_hostgroup                                |       0.01 |
| nagiosql           | tbl_timeperiod                               |       0.01 |
| nagiosql           | tbl_domain                                   |       0.01 |
| nagiosql           | tbl_serviceescalation                        |       0.01 |
| nagiosql           | tbl_contactgroup                             |       0.01 |
| nagiosql           | tbl_user                                     |       0.01 |
| nagiosql           | tbl_contacttemplate                          |       0.01 |
| nagiosql           | tbl_servicegroup                             |       0.01 |
| nagiosql           | tbl_hostescalation                           |       0.01 |
| nagiosxi           | xi_sysstat                                   |       0.01 |
| information_schema | PARTITIONS                                   |       0.01 |
| information_schema | PARAMETERS                                   |       0.01 |
| information_schema | TRIGGERS                                     |       0.01 |
| information_schema | EVENTS                                       |       0.01 |
| information_schema | ROUTINES                                     |       0.01 |
| information_schema | COLUMNS                                      |       0.01 |
| information_schema | VIEWS                                        |       0.01 |
| information_schema | PROCESSLIST                                  |       0.01 |
| information_schema | PLUGINS                                      |       0.01 |
| nagiosql           | tbl_lnkServiceToServicetemplate              |       0.01 |
| mysql              | db                                           |       0.01 |
| mysql              | proxies_priv                                 |       0.01 |
| nagiosql           | tbl_lnkServicetemplateToServicetemplate      |       0.01 |
| nagiosql           | tbl_lnkServiceToHost                         |       0.01 |
| nagios             | nagios_configfiles                           |       0.00 |
| mysql              | proc                                         |       0.00 |
| mysql              | help_category                                |       0.00 |
| nagios             | nagios_contacts                              |       0.00 |
| mysql              | columns_priv                                 |       0.00 |
| nagiosql           | tbl_servicedependency                        |       0.00 |
| mysql              | tables_priv                                  |       0.00 |
| mysql              | procs_priv                                   |       0.00 |
| nagiosql           | tbl_hostdependency                           |       0.00 |
| nagios             | nagios_timeperiod_timeranges                 |       0.00 |
| nagios             | nagios_runtimevariables                      |       0.00 |
| nagiosql           | tbl_timedefinition                           |       0.00 |
| nagios             | nagios_hostgroups                            |       0.00 |
| nagios             | nagios_contactstatus                         |       0.00 |
| nagiosql           | tbl_submenu                                  |       0.00 |
| nagiosql           | tbl_settings                                 |       0.00 |
| nagios             | nagios_timeperiods                           |       0.00 |
| nagios             | nagios_contactgroup_members                  |       0.00 |
| nagios             | nagios_contactgroups                         |       0.00 |
| nagios             | nagios_programstatus                         |       0.00 |
| nagios             | nagios_servicegroups                         |       0.00 |
| nagiosql           | tbl_lnkHosttemplateToHosttemplate            |       0.00 |
| mysql              | user                                         |       0.00 |
| nagiosql           | tbl_lnkServiceToHostgroup                    |       0.00 |
| nagiosxi           | xi_commands                                  |       0.00 |
| nagiosql           | tbl_mainmenu                                 |       0.00 |
| nagiosql           | tbl_variabledefinition                       |       0.00 |
| nagiosql           | tbl_lnkContactToContacttemplate              |       0.00 |
| nagiosql           | tbl_lnkServiceescalationToService            |       0.00 |
| nagiosql           | tbl_lnkServiceescalationToContactgroup       |       0.00 |
| nagiosql           | tbl_lnkContactToContactgroup                 |       0.00 |
| nagiosql           | tbl_lnkServiceescalationToHostgroup          |       0.00 |
| nagiosql           | tbl_lnkServiceToVariabledefinition           |       0.00 |
| nagiosql           | tbl_lnkHosttemplateToContactgroup            |       0.00 |
| nagiosql           | tbl_lnkContactToCommandService               |       0.00 |
| nagiosql           | tbl_lnkServicetemplateToContact              |       0.00 |
| nagiosql           | tbl_lnkContactToCommandHost                  |       0.00 |
| nagios             | nagios_instances                             |       0.00 |
| nagiosql           | tbl_lnkServicetemplateToContactgroup         |       0.00 |
| nagiosql           | tbl_lnkHostescalationToContactgroup          |       0.00 |
| nagiosql           | tbl_lnkServiceToServicegroup                 |       0.00 |
| nagiosql           | tbl_lnkContacttemplateToCommandService       |       0.00 |
| nagiosql           | tbl_lnkContacttemplateToCommandHost          |       0.00 |
| nagiosql           | tbl_lnkHostgroupToHost                       |       0.00 |
| nagiosql           | tbl_lnkHostescalationToHost                  |       0.00 |
| nagiosql           | tbl_lnkHosttemplateToHostgroup               |       0.00 |
| nagiosql           | tbl_lnkContactgroupToContact                 |       0.00 |
| nagiosql           | tbl_lnkServiceToContact                      |       0.00 |
| nagiosql           | tbl_lnkTimeperiodToTimeperiod                |       0.00 |
| nagiosql           | tbl_lnkContacttemplateToContactgroup         |       0.00 |
| nagiosql           | tbl_lnkHostToContactgroup                    |       0.00 |
| mysql              | event                                        |       0.00 |
| mysql              | host                                         |       0.00 |
| nagios             | nagios_dbversion                             |       0.00 |
| nagiosql           | tbl_lnkContactgroupToContactgroup            |       0.00 |
| nagiosql           | tbl_lnkServicetemplateToHost                 |       0.00 |
| nagiosql           | tbl_lnkContacttemplateToVariabledefinition   |       0.00 |
| nagios             | nagios_hostdependencies                      |       0.00 |
| nagiosql           | tbl_session_locks                            |       0.00 |
| nagiosql           | tbl_lnkHosttemplateToContact                 |       0.00 |
| mysql              | ndb_binlog_index                             |       0.00 |
| nagios             | nagios_servicechecks                         |       0.00 |
| nagiosql           | tbl_lnkContacttemplateToContacttemplate      |       0.00 |
| nagiosql           | tbl_lnkServicedependencyToService_S          |       0.00 |
| nagiosql           | tbl_session                                  |       0.00 |
| nagiosql           | tbl_lnkHostgroupToHostgroup                  |       0.00 |
| nagios             | nagios_service_parentservices                |       0.00 |
| nagiosql           | tbl_lnkServicedependencyToService_DS         |       0.00 |
| nagios             | nagios_hostchecks                            |       0.00 |
| nagiosql           | tbl_lnkContactToVariabledefinition           |       0.00 |
| nagiosql           | tbl_lnkServicedependencyToHostgroup_H        |       0.00 |
| nagiosql           | tbl_lnkHostescalationToHostgroup             |       0.00 |
| nagios             | nagios_host_parenthosts                      |       0.00 |
| nagios             | nagios_contact_addresses                     |       0.00 |
| nagiosql           | tbl_lnkServicegroupToServicegroup            |       0.00 |
| nagios             | nagios_host_contacts                         |       0.00 |
| nagios             | nagios_scheduleddowntime                     |       0.00 |
| nagiosql           | tbl_lnkServicegroupToService                 |       0.00 |
| nagiosql           | tbl_lnkServicedependencyToHostgroup_DH       |       0.00 |
| nagiosql           | tbl_serviceextinfo                           |       0.00 |
| mysql              | time_zone_transition                         |       0.00 |
| nagiosql           | tbl_lnkHostescalationToContact               |       0.00 |
| nagiosql           | tbl_lnkServicedependencyToHost_H             |       0.00 |
| mysql              | time_zone_name                               |       0.00 |
| nagiosql           | tbl_lnkHostdependencyToHostgroup_H           |       0.00 |
| nagiosql           | tbl_lnkServicedependencyToHost_DH            |       0.00 |
| mysql              | time_zone_leap_second                        |       0.00 |
| nagiosql           | tbl_lnkHostdependencyToHostgroup_DH          |       0.00 |
| nagios             | nagios_timedevents                           |       0.00 |
| nagiosql           | tbl_lnkHostdependencyToHost_H                |       0.00 |
| mysql              | time_zone                                    |       0.00 |
| nagiosql           | tbl_lnkHostdependencyToHost_DH               |       0.00 |
| nagiosql           | tbl_lnkServiceToContactgroup                 |       0.00 |
| nagios             | nagios_timedeventqueue                       |       0.00 |
| nagiosql           | tbl_lnkHostToVariabledefinition              |       0.00 |
| nagiosql           | tbl_hostextinfo                              |       0.00 |
| nagiosql           | tbl_logbook                                  |       0.00 |
| nagiosql           | tbl_lnkHosttemplateToVariabledefinition      |       0.00 |
| mysql              | servers                                      |       0.00 |
| nagiosql           | tbl_lnkServiceescalationToHost               |       0.00 |
| mysql              | time_zone_transition_type                    |       0.00 |
| nagiosql           | tbl_lnkServicetemplateToVariabledefinition   |       0.00 |
| nagios             | nagios_serviceescalation_contacts            |       0.00 |
| nagiosql           | tbl_lnkHostToHost                            |       0.00 |
| nagiosql           | tbl_lnkServicetemplateToServicegroup         |       0.00 |
| nagiosql           | tbl_lnkServiceescalationToContact            |       0.00 |
| nagios             | nagios_hostescalation_contacts               |       0.00 |
| nagiosql           | tbl_lnkHosttemplateToHost                    |       0.00 |
| nagiosql           | tbl_lnkServicetemplateToHostgroup            |       0.00 |
| nagiosql           | tbl_lnkHostToContact                         |       0.00 |
| mysql              | func                                         |       0.00 |
| mysql              | plugin                                       |       0.00 |
| nagios             | nagios_servicedependencies                   |       0.00 |
| information_schema | CLIENT_STATISTICS                            |       0.00 |
| information_schema | USER_PRIVILEGES                              |       0.00 |
| information_schema | INNODB_SYS_INDEXES                           |       0.00 |
| information_schema | CHARACTER_SETS                               |       0.00 |
| information_schema | INNODB_SYS_FOREIGN                           |       0.00 |
| information_schema | TABLE_STATISTICS                             |       0.00 |
| performance_schema | file_summary_by_instance                     |       0.00 |
| information_schema | INNODB_BUFFER_PAGE                           |       0.00 |
| information_schema | INNODB_SYS_STATS                             |       0.00 |
| information_schema | TABLE_PRIVILEGES                             |       0.00 |
| performance_schema | file_summary_by_event_name                   |       0.00 |
| information_schema | TABLESPACES                                  |       0.00 |
| information_schema | INNODB_SYS_COLUMNS                           |       0.00 |
| information_schema | TABLE_CONSTRAINTS                            |       0.00 |
| performance_schema | file_instances                               |       0.00 |
| information_schema | KEY_COLUMN_USAGE                             |       0.00 |
| information_schema | INNODB_BUFFER_POOL_STATS                     |       0.00 |
| information_schema | INNODB_SYS_FIELDS                            |       0.00 |
| performance_schema | events_waits_summary_global_by_event_name    |       0.00 |
| information_schema | KEY_CACHES                                   |       0.00 |
| information_schema | INNODB_BUFFER_PAGE_LRU                       |       0.00 |
| information_schema | TABLES                                       |       0.00 |
| performance_schema | events_waits_summary_by_thread_by_event_name |       0.00 |
| information_schema | INDEX_STATISTICS                             |       0.00 |
| information_schema | INNODB_SYS_FOREIGN_COLS                      |       0.00 |
| information_schema | STATISTICS                                   |       0.00 |
| information_schema | INNODB_SYS_TABLES                            |       0.00 |
| information_schema | INNODB_INDEX_STATS                           |       0.00 |
| performance_schema | events_waits_summary_by_instance             |       0.00 |
| information_schema | GLOBAL_VARIABLES                             |       0.00 |
| information_schema | INNODB_TABLE_STATS                           |       0.00 |
| information_schema | SESSION_VARIABLES                            |       0.00 |
| information_schema | INNODB_BUFFER_POOL_PAGES_BLOB                |       0.00 |
| information_schema | INNODB_LOCK_WAITS                            |       0.00 |
| performance_schema | events_waits_history_long                    |       0.00 |
| information_schema | GLOBAL_STATUS                                |       0.00 |
| information_schema | XTRADB_ADMIN_COMMAND                         |       0.00 |
| performance_schema | threads                                      |       0.00 |
| information_schema | SESSION_STATUS                               |       0.00 |
| information_schema | INNODB_LOCKS                                 |       0.00 |
| information_schema | FILES                                        |       0.00 |
| performance_schema | setup_timers                                 |       0.00 |
| information_schema | SCHEMA_PRIVILEGES                            |       0.00 |
| information_schema | INNODB_BUFFER_POOL_PAGES_INDEX               |       0.00 |
| information_schema | INNODB_SYS_TABLESTATS                        |       0.00 |
| performance_schema | events_waits_history                         |       0.00 |
| performance_schema | setup_instruments                            |       0.00 |
| information_schema | SCHEMATA                                     |       0.00 |
| information_schema | INNODB_TRX                                   |       0.00 |
| mysql              | slow_log                                     |       0.00 |
| information_schema | INNODB_CMPMEM                                |       0.00 |
| performance_schema | events_waits_current                         |       0.00 |
| information_schema | INNODB_BUFFER_POOL_PAGES                     |       0.00 |
| information_schema | INNODB_UNDO_LOGS                             |       0.00 |
| performance_schema | cond_instances                               |       0.00 |
| information_schema | ENGINES                                      |       0.00 |
| performance_schema | setup_consumers                              |       0.00 |
| information_schema | INNODB_CHANGED_PAGES                         |       0.00 |
| information_schema | INNODB_RSEG                                  |       0.00 |
| information_schema | COLUMN_PRIVILEGES                            |       0.00 |
| performance_schema | rwlock_instances                             |       0.00 |
| information_schema | REFERENTIAL_CONSTRAINTS                      |       0.00 |
| information_schema | INNODB_CMPMEM_RESET                          |       0.00 |
| performance_schema | performance_timers                           |       0.00 |
| information_schema | PROFILING                                    |       0.00 |
| information_schema | INNODB_CMP_RESET                             |       0.00 |
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY        |       0.00 |
| performance_schema | mutex_instances                              |       0.00 |
| mysql              | general_log                                  |       0.00 |
| information_schema | INNODB_CMP                                   |       0.00 |
| information_schema | COLLATIONS                                   |       0.00 |
| information_schema | USER_STATISTICS                              |       0.00 |
+--------------------+----------------------------------------------+------------+

[cbeattie-unitrends]

I managed to remove the leftover temporary tables. First, I took a snapshot of Nagios XI's VM for safety. Then:

Code: Select all

# mysql -u root -pnagiosxi
MariaDB [(none)]> use nagiosxi
Database changed
MariaDB [nagiosxi]> drop table `#mysql50##sql-1e4f5_143`;
Query OK, 0 rows affected (0.05 sec)

MariaDB [nagiosxi]> drop table `#mysql50##sql-820_15ea10a`;
Query OK, 0 rows affected (0.62 sec)

MariaDB [nagiosxi]> Bye
# /usr/local/nagiosxi/scripts/repair_databases.sh

After running the database repair script, MariaDB's log file showed a clean start. Everything is back to running like I expect it to and you can lock this thread. Thanks!

[tgriep]

Glad that it is fixed now. I'll go ahead and lock the post.