Nagios not store notification in notification table

[safuanmansor]

Hi Support,

We have a situation where the contact received the alert but the notification details are missing from the list.
The list stop at 15:07 until midnight 00:00.The list continue generated back into database after nagios backup process done.

We check that the state history is there during the missing time, the notification in core is exist but not with xi. I understand that the data is stored in logs and xi is in the db for performance.
Can you help guide me to troubleshoot to find the what make it stop as it already happen twice this week (different time).

[benjaminsmith]

Hi,

It sounds like there is a database issue here when you run the same state history report in XI using the Legacy reports ( from the bottom left hand sidebar in the Reports page) are the missing times now present?

What is the output of the following query?

Code: Select all

echo "SELECT table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema IN ('nagios', 'nagiosql', 'nagiosxi');" | mysql -h 127.0.0.1 -uroot -pnagiosxi --table

Also, please send over the system profile and we'll check the logs.

To send us your system profile.
Login to the Nagios XI GUI using a web browser.
Click the "Admin" > "System Profile" Menu
Click the "Download Profile" button
Save the profile.zip file and share in a private message or upload it to the post/ticket, and then reply to this post to bring it up in the queue.

[safuanmansor]

Hi Benjaminsmith,

We can see the missing notification from the nagios core but not in XI.

Below is the output of the sql query

+--------------------------------------------+------------+
| Table | Size in MB |
+--------------------------------------------+------------+
| alc | 0.00 |
| bdc | 0.00 |
| hla | 0.00 |
| hlib | 0.00 |
| hlisb | 0.00 |
| limit_t1 | 0.00 |
| limit_t2 | 0.00 |
| limit_t3 | 0.00 |
| limit_total | 0.00 |
| nagios_acknowledgements | 0.53 |
| nagios_commands | 0.06 |
| nagios_commenthistory | 1937.27 |
| nagios_comments | 0.39 |
| nagios_configfiles | 0.00 |
| nagios_configfilevariables | 0.01 |
| nagios_conninfo | 1.31 |
| nagios_contact_addresses | 0.00 |
| nagios_contact_notificationcommands | 0.20 |
| nagios_contactgroup_members | 0.03 |
| nagios_contactgroups | 0.01 |
| nagios_contactnotificationmethods | 112.76 |
| nagios_contactnotifications | 117.25 |
| nagios_contacts | 0.05 |
| nagios_contactstatus | 0.03 |
| nagios_customvariables | 0.83 |
| nagios_customvariablestatus | 0.87 |
| nagios_dbversion | 0.00 |
| nagios_downtimehistory | 17.37 |
| nagios_eventhandlers | 0.02 |
| nagios_externalcommands | 0.11 |
| nagios_flappinghistory | 6.40 |
| nagios_host_contactgroups | 0.13 |
| nagios_host_contacts | 0.29 |
| nagios_host_parenthosts | 0.00 |
| nagios_hostchecks | 0.00 |
| nagios_hostdependencies | 0.00 |
| nagios_hostescalation_contactgroups | 0.00 |
| nagios_hostescalation_contacts | 0.00 |
| nagios_hostescalations | 0.00 |
| nagios_hostgroup_members | 0.44 |
| nagios_hostgroups | 0.01 |
| nagios_hosts | 0.46 |
| nagios_hoststatus | 1.07 |
| nagios_instances | 0.00 |
| nagios_logentries | 1048.68 |
| nagios_notifications | 2362.01 |
| nagios_objects | 18.16 |
| nagios_processevents | 1.48 |
| nagios_programstatus | 0.00 |
| nagios_runtimevariables | 0.00 |
| nagios_scheduleddowntime | 0.19 |
| nagios_service_contactgroups | 1.43 |
| nagios_service_contacts | 7.57 |
| nagios_service_parentservices | 0.00 |
| nagios_servicechecks | 0.00 |
| nagios_servicedependencies | 0.00 |
| nagios_serviceescalation_contactgroups | 0.00 |
| nagios_serviceescalation_contacts | 0.00 |
| nagios_serviceescalations | 0.00 |
| nagios_servicegroup_members | 0.05 |
| nagios_servicegroups | 0.00 |
| nagios_services | 4.75 |
| nagios_servicestatus | 13.30 |
| nagios_statehistory | 1964.59 |
| nagios_systemcommands | 0.05 |
| nagios_timedeventqueue | 0.00 |
| nagios_timedevents | 0.00 |
| nagios_timeperiod_timeranges | 0.02 |
| nagios_timeperiods | 0.00 |
| profile | 0.00 |
| region | 0.02 |
| tc | 0.00 |
| tbl_command | 0.05 |
| tbl_contact | 0.02 |
| tbl_contactgroup | 0.01 |
| tbl_contacttemplate | 0.01 |
| tbl_domain | 0.01 |
| tbl_host | 0.33 |
| tbl_hostdependency | 0.00 |
| tbl_hostescalation | 0.00 |
| tbl_hostextinfo | 0.00 |
| tbl_hostgroup | 0.03 |
| tbl_hosttemplate | 0.01 |
| tbl_info | 0.13 |
| tbl_lnkcontactgrouptocontact | 0.00 |
| tbl_lnkcontactgrouptocontactgroup | 0.00 |
| tbl_lnkcontacttemplatetocommandhost | 0.00 |
| tbl_lnkcontacttemplatetocommandservice | 0.00 |
| tbl_lnkcontacttemplatetocontactgroup | 0.00 |
| tbl_lnkcontacttemplatetocontacttemplate | 0.00 |
| tbl_lnkcontacttemplatetovariabledefinition | 0.00 |
| tbl_lnkcontacttocommandhost | 0.00 |
| tbl_lnkcontacttocommandservice | 0.00 |
| tbl_lnkcontacttocontactgroup | 0.00 |
| tbl_lnkcontacttocontacttemplate | 0.01 |
| tbl_lnkcontacttovariabledefinition | 0.00 |
| tbl_lnkhostdependencytohost_dh | 0.00 |
| tbl_lnkhostdependencytohost_h | 0.00 |
| tbl_lnkhostdependencytohostgroup_dh | 0.00 |
| tbl_lnkhostdependencytohostgroup_h | 0.00 |
| tbl_lnkhostescalationtocontact | 0.00 |
| tbl_lnkhostescalationtocontactgroup | 0.00 |
| tbl_lnkhostescalationtohost | 0.00 |
| tbl_lnkhostescalationtohostgroup | 0.00 |
| tbl_lnkhostgrouptohost | 0.07 |
| tbl_lnkhostgrouptohostgroup | 0.01 |
| tbl_lnkhosttemplatetocontact | 0.00 |
| tbl_lnkhosttemplatetocontactgroup | 0.00 |
| tbl_lnkhosttemplatetohost | 0.00 |
| tbl_lnkhosttemplatetohostgroup | 0.00 |
| tbl_lnkhosttemplatetohosttemplate | 0.00 |
| tbl_lnkhosttemplatetovariabledefinition | 0.00 |
| tbl_lnkhosttocontact | 0.06 |
| tbl_lnkhosttocontactgroup | 0.05 |
| tbl_lnkhosttohost | 0.00 |
| tbl_lnkhosttohostgroup | 0.03 |
| tbl_lnkhosttohosttemplate | 0.05 |
| tbl_lnkhosttovariabledefinition | 0.01 |
| tbl_lnkservicedependencytohost_dh | 0.00 |
| tbl_lnkservicedependencytohost_h | 0.00 |
| tbl_lnkservicedependencytohostgroup_dh | 0.00 |
| tbl_lnkservicedependencytohostgroup_h | 0.00 |
| tbl_lnkservicedependencytoservice_ds | 0.00 |
| tbl_lnkservicedependencytoservice_s | 0.00 |
| tbl_lnkserviceescalationtocontact | 0.00 |
| tbl_lnkserviceescalationtocontactgroup | 0.00 |
| tbl_lnkserviceescalationtohost | 0.00 |
| tbl_lnkserviceescalationtohostgroup | 0.00 |
| tbl_lnkserviceescalationtoservice | 0.00 |
| tbl_lnkservicegrouptoservice | 0.01 |
| tbl_lnkservicegrouptoservicegroup | 0.00 |
| tbl_lnkservicetemplatetocontact | 0.00 |
| tbl_lnkservicetemplatetocontactgroup | 0.00 |
| tbl_lnkservicetemplatetohost | 0.00 |
| tbl_lnkservicetemplatetohostgroup | 0.00 |
| tbl_lnkservicetemplatetoservicegroup | 0.00 |
| tbl_lnkservicetemplatetoservicetemplate | 0.01 |
| tbl_lnkservicetemplatetovariabledefinition | 0.00 |
| tbl_lnkservicetocontact | 1.81 |
| tbl_lnkservicetocontactgroup | 0.10 |
| tbl_lnkservicetohost | 1.62 |
| tbl_lnkservicetohostgroup | 0.00 |
| tbl_lnkservicetoservicegroup | 0.00 |
| tbl_lnkservicetoservicetemplate | 1.45 |
| tbl_lnkservicetovariabledefinition | 1.11 |
| tbl_lnktimeperiodtotimeperiod | 0.00 |
| tbl_logbook | 0.00 |
| tbl_mainmenu | 0.00 |
| tbl_service | 9.16 |
| tbl_servicedependency | 0.00 |
| tbl_serviceescalation | 0.00 |
| tbl_serviceextinfo | 0.00 |
| tbl_servicegroup | 0.01 |
| tbl_servicetemplate | 0.02 |
| tbl_session | 0.00 |
| tbl_session_locks | 0.00 |
| tbl_settings | 0.00 |
| tbl_submenu | 0.00 |
| tbl_timedefinition | 0.01 |
| tbl_timeperiod | 0.01 |
| tbl_user | 0.01 |
| tbl_variabledefinition | 2.22 |
| xi_auditlog | 8.79 |
| xi_commands | 0.00 |
| xi_eventqueue | 0.01 |
| xi_events | 0.08 |
| xi_incidents | 0.00 |
| xi_meta | 7.57 |
| xi_options | 0.02 |
| xi_sysstat | 0.01 |
| xi_usermeta | 0.66 |
| xi_users | 0.01 |
+--------------------------------------------+------------+

Please refer to the private message for the profiles data.

[benjaminsmith]

Hi,

Thanks for the profile, I did notice some crashed database tables.

mysqld: Table './nagios/nagios_notifications' is marked as crashed and last (automatic?) repair failed

Please log in as root and run the following repair script:

Code: Select all

/usr/local/nagiosxi/scripts/repair_databases.sh

Also, the kernel message queues settings could use increasing, please follow the steps in the guide below, and let me know if it happens again.

NDOUtils - Message Queue Exceeded

Then restart the software stack:

Code: Select all

systemctl stop crond
systemctl stop npcd
systemctl stop nagios
systemctl stop ndo2db
pkill -9 -u nagios
for i in $(ipcs -q | grep nagios |awk '{print $2}'); do ipcrm -q $i; done
rm -rf /usr/local/nagiosxi/var/dbmaint.lock
rm -rf /usr/local/nagiosxi/var/event_handler.lock
rm -rf /usr/local/nagiosxi/scripts/reconfigure_nagios.lock
systemctl restart mariadb
systemctl start ndo2db
systemctl start nagios
systemctl start npcd
systemctl start crond

[safuanmansor]

Thanks for the suggestion,

I wont be able to configure it until next coming monday. I will monitor it and get back to you after that.

[benjaminsmith]

Hi,

I wont be able to configure it until next coming monday. I will monitor it and get back to you after that.

Sounds good. We'll wait until then.

[safuanmansor]

Hi Ben,

Upon checking the current setting are as below.

kernel.msgmnb = 2097152000
kernel.msgmax = 2097152000
kernel.msgmni = 256000000

We are running 16 core cpu with 32GB ram

In the guide also, it is recommended to offload the DB to another server, but this cannot be an option to us as the DB was move back to local to fix the NagiosXI UI 'Last Check' lagging behind.

[benjaminsmith]

Hi,

In the guide also, it is recommended to offload the DB to another server, but this cannot be an option to us as the DB was move back to local to fix the NagiosXI UI 'Last Check' lagging behind.

Those values look ok. Yeah, I would recommend keeping the database local.

[safuanmansor]

Hi,

Since nothing change ( as those value already apply before the crash), how do we ensure that it won't happen in the future. Do you have any other suggestion for this issue?

Thanks

[ssax]

Take an XI backup first:

https://assets.nagios.com/downloads/nag ... ios-XI.pdf

You can convert the table to InnoDB so that it doesn't crash:
- NOTE: You may need to adjust the -h 127.0.0.1, the -uroot, and -pnagiosxi in the command if your DB is offloaded to another server and/or you've changed the root mysql password

Code: Select all

mysql -h 127.0.0.1 -uroot -pnagiosxi nagios -e "ALTER TABLE nagios_notifications ENGINE = InnoDB;"

MyISAM (the engine type currently) is faster than InnoDB but InnoDB doesn't crash, you can run the command above to convert that table to InnoDB without any data loss (still take an XI backup just in case).

You should go to Admin > Performance Settings > Databases tab and adjust ALL of the retention intervals to meet your business data policy standards to keep them cleaned up as these settings are for adjusting the retention on those DB tables.

I would lower them to the smallest possible level and utilize the XI backup/restore process and the Admin > Scheduled Backups process to offload the backups to another server. Since these XI backups contain database backups you can spin them up to grab the data and report on them if needed.