downtime not getting in the scheduled downtime table

[jweijters]

Hi I split this of from
https://support.nagios.com/forum/viewto ... 16&t=61396

I encountered an error first when migrating to NagiosXI 5.8.1 as stated in the previous Topic, however this also counts for NagiosXI 5.6.14, and probably for all versions of Nagios.

When a downtime is scheduled for a date beyond y2k38 the downtime is not entered in the nagios_scheduleddowntime table, nor in the nagios_downtimehistory table when using Mariadb or Mysql as database.

This is because when entering the date, it is calculated to UNIXTIME. and then it is entered in the mentioned tables by FROM_UNIXTIME(). MySQL and MariaDB has a limit on FROM_UNIXTIME() being:

Timestamps in MariaDB have a maximum value of 2147483647, equivalent to 2038-01-19 05:14:07. This is due to the underlying 32-bit limitation. Using the function on a timestamp beyond this will result in NULL being returned. Use DATETIME as a storage type if you require dates beyond this.

See: https://mariadb.com/kb/en/from_unixtime/

This means that entering dates beyond this date, fails.

Looking at Nagios 5.6.14, we see the following in the ndo2db.debug:

Code: Select all

[1611585860.028452] [002.0] [pid=7153] INSERT INTO nagios_downtimehistory SET instance_id='1', downtime_type='1', object_id='43462', entry_time=FROM_UNIXTIME(1606744821), author_name='Martijn Stol', comment_data='Zerto is nog niet geinstalleerd', internal_downtime_id='3315', triggered_by_id='0', is_fixed='1', duration='2495696496', scheduled_start_time=FROM_UNIXTIME(1606744703), scheduled_end_time=FROM_UNIXTIME(4102441199) ON DUPLICATE KEY UPDATE instance_id='1', downtime_type='1', object_id='43462', entry_time=FROM_UNIXTIME(1606744821), author_name='Martijn Stol', comment_data='Zerto is nog niet geinstalleerd', internal_downtime_id='3315', triggered_by_id='0', is_fixed='1', duration='2495696496', scheduled_start_time=FROM_UNIXTIME(1606744703), scheduled_end_time=FROM_UNIXTIME(4102441199)
[1611585860.028575] [002.0] [pid=7153] INSERT INTO nagios_scheduleddowntime SET instance_id='1', downtime_type='1', object_id='43462', entry_time=FROM_UNIXTIME(1606744821), author_name='Martijn Stol', comment_data='Zerto is nog niet geinstalleerd', internal_downtime_id='3315', triggered_by_id='0', is_fixed='1', duration='2495696496', scheduled_start_time=FROM_UNIXTIME(1606744703), scheduled_end_time=FROM_UNIXTIME(4102441199) ON DUPLICATE KEY UPDATE instance_id='1', downtime_type='1', object_id='43462', entry_time=FROM_UNIXTIME(1606744821), author_name='Martijn Stol', comment_data='Zerto is nog niet geinstalleerd', internal_downtime_id='3315', triggered_by_id='0', is_fixed='1', duration='2495696496', scheduled_start_time=FROM_UNIXTIME(1606744703), scheduled_end_time=FROM_UNIXTIME(4102441199)

However there is no Error, or failure in the ndo2db.debug

when I try to enter this manual into the database I get

Code: Select all

MariaDB [nagios]> INSERT INTO nagios_scheduleddowntime SET instance_id='1', downtime_type='1', object_id='43462', entry_time=FROM_UNIXTIME(1606744821), author_name='Martijn Stol', comment_data='Zerto is nog niet geinstalleerd', internal_downtime_id='3315', triggered_by_id='0', is_fixed='1', duration='2495696496', scheduled_start_time=FROM_UNIXTIME(1606744703), scheduled_end_time=FROM_UNIXTIME(4102441199) ON DUPLICATE KEY UPDATE instance_id='1', downtime_type='1', object_id='43462', entry_time=FROM_UNIXTIME(1606744821), author_name='Martijn Stol', comment_data='Zerto is nog niet geinstalleerd', internal_downtime_id='3315', triggered_by_id='0', is_fixed='1', duration='2495696496', scheduled_start_time=FROM_UNIXTIME(1606744703), scheduled_end_time=FROM_UNIXTIME(4102441199);
ERROR 1048 (23000): Column 'scheduled_end_time' cannot be null

This because FROM_UNIXTIME(4102441199) equals NULL

In Nagios 5.8.1 some code has changed ans can be seen in https://support.nagios.com/forum/viewto ... 96#p324166
here we see

Code: Select all

[1611240271] NDO-3: INSERT INTO nagios_scheduleddowntime (instance_id, downtime_type, object_id, entry_time, author_name, comment_data, internal_downtime_id, triggered_by_id, is_fixed, duration, scheduled_start_time, scheduled_end_time) VALUES (1,1,63030,FROM_UNIXTIME(1554368169),'my-author_name','my-comment_data',915511,0,1,-1778430201,FROM_UNIXTIME(1554368106),FROM_UNIXTIME(-224062095)) ON DUPLICATE KEY UPDATE instance_id = VALUES(instance_id), downtime_type = VALUES(downtime_type), object_id = VALUES(object_id), entry_time = VALUES(entry_time), author_name = VALUES(author_name), comment_data = VALUES(comment_data), internal_downtime_id = VALUES(internal_downtime_id), triggered_by_id = VALUES(triggered_by_id), is_fixed = VALUES(is_fixed), duration = VALUES(duration), scheduled_start_time = VALUES(scheduled_start_time), scheduled_end_time = VALUES(scheduled_end_time)

The value has been changed to -224062095 which is the "signed equivalent" of the "unsigned" integer of 4102441199
( not quite, -224062095 --> 4070905201, date -d@4070905201 = Thu Jan 1 00:00:01 CET 2099
and 4102441199 = date -d@4102441199 = Thu Dec 31 23:59:59 CET 2099
this because the tests were done just a little different )

Questions:
Where are downtime's stored when these are not in the nagios_scheduleddowntime table?
I've seen entries in the nagios_externalcommands table:

Code: Select all

 select * from nagios_externalcommands where command_args like '%4102441199%';
+--------------------+-------------+---------------------+--------------+-----------------------+------------------------------------------------------------------------------------------+
| externalcommand_id | instance_id | entry_time          | command_type | command_name          | command_args                                                                             |
+--------------------+-------------+---------------------+--------------+-----------------------+------------------------------------------------------------------------------------------+
|            1155165 |           1 | 2021-01-22 15:10:02 |           56 | SCHEDULE_SVC_DOWNTIME | snsc-sam-gropdrs01;Time;1611324538;4102441199;1;0;7200;Martijn Stol;test martijn apenoot |
+--------------------+-------------+---------------------+--------------+-----------------------+------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

And in the nagios_commenthistory and nagios_comments but these are just comments, and not calculated
Furthermore I see the field scheduled_downtime_depth in the nagios_hoststatus and nagios_servicestatus.
Are services or hosts with a non zero entry in scheduled_downtime_depth not notified ?

Where does the API: curl -XGET "https://nagiosxi.mydomain.nl/nagiosxi/a ... s/downtime gets it's data from?, so where is it stored?

We can't delete a downtime for services when the scheduled end time is set beyond 2038-01-19 05:14:07

Kind regards,

Joris Weijters

[ssax]

I believe these are taken from /usr/local/nagios/var/retention.dat, so you should be able to:

Code: Select all

systemctl stop nagios

Then delete the downtime(s) that are above 2038 in your /usr/local/nagios/var/retention.dat (it's at the bottom) and then start nagios back up:

Code: Select all

systemctl start nagios

Then re-add the downtime in the web interface with something less than 2038.

Development would need to change the way the code is written to not use FROM_UNIXTIME.

[jweijters]

Hi ssax,

Thanks, this seems to work

Kind regards,

Joris Weijters

[benjaminsmith]

HI Joris,

Glad it's working for you now. When you have a moment, let us know if you'd like to keep this thread open or if it's ok to close it.

[jweijters]

Hi Benjaminsmith,

I was thinking what could he mean with: "When you have a movement", english is not my mother language.
but I think you mean "When you have a moment"

This tread can be closed, if it is picked-up by development for fixing this FROM_UNIXTIME issue in all Nagios.
I'm sure FROM_UNIXTIME is used at more places.

Kind regards,

Joris Weijters

[benjaminsmith]

Hi Joris,

That's correct (fixed the typo, thank you!).

We'll close this out. Feel free to reach to me directly in a PM if you have any questions about this issue.