SQL query to show Hosts and Services with Scheduled Downtime

[ottow]

HI Support,

Could you please assist in creating a sql query which will output all Hosts and Services with scheduled downtime, and include the downtime window?

Thanks,

Otto

[tgriep]

Here is a query that you can run to show what is currently in Scheduled Downtime.

Code: Select all

echo "select nagios.nagios_objects.object_id, nagios.nagios_scheduleddowntime.object_id, nagios.nagios_scheduleddowntime.scheduleddowntime_id, nagios_objects.name1, nagios_objects.name2, nagios.nagios_scheduleddowntime.duration, nagios.nagios_scheduleddowntime.scheduled_start_time, nagios.nagios_scheduleddowntime.scheduled_end_time from nagios.nagios_objects inner join nagios.nagios_scheduleddowntime on nagios.nagios_scheduleddowntime.object_id = nagios.nagios_objects.object_id;" | mysql -t -u root -pnagiosxi

The query should output the data to the console in the following format.

Code: Select all

+-----------+-----------+----------------------+---------------+--------------+----------+----------------------+---------------------+
| object_id | object_id | scheduleddowntime_id | name1         | name2        | duration | scheduled_start_time | scheduled_end_time  |
+-----------+-----------+----------------------+---------------+--------------+----------+----------------------+---------------------+
|       330 |       330 |                    2 | 192.168.1.1-1 |              |     3600 | 2021-10-26 09:00:00  | 2021-10-26 10:00:00 |
|       527 |       527 |                    3 | 192.168.223.4 | / Disk Usage |     7200 | 2021-10-25 13:00:25  | 2021-10-25 15:00:25 |
+-----------+-----------+----------------------+---------------+--------------+----------+----------------------+---------------------+

The name1 field is the name of the Host.
The name2 field is the name of the Service.
The duration field is the length of Downtime in seconds.
The scheduled_start_time, scheduled_end_time fields show the start time and end time.

Thank you.

[ottow]

This is exactly what I was looking for. Thank you very much!

[tgriep]

Your welcome, glad it worked for you.

I'll close and lock up the post for you but feel free to post any new questions in the future if needed.

Thank you.