MySQL Query for Hosts/Services/Threshold/Contacts

[seaward1983]

RHEL 7 Linux Distro
64 bit
Manual Install of XI
No special configs
Yes, using SSL
Version 5.10.0

I'm trying to export all hosts, all services, threshold values, and who is notified. I got this far, I'm stuck on the contact info.

SELECT
`nagios_hosts`.`display_name`
, `nagios_hosts`.`check_command_args`
, `nagios_services`.`display_name`
, `nagios_services`.`check_command_args`
, `nagios_services`.`check_timeperiod_object_id`
, `nagios_services`.`check_interval`
, `nagios_services`.`retry_interval`
, `nagios_services`.`max_check_attempts`
, `nagios_services`.`first_notification_delay`
, `nagios_services`.`notifications_enabled`
FROM
`nagios`.`nagios_services`
INNER JOIN `nagios`.`nagios_hosts`
ON (`nagios_services`.`host_object_id` = `nagios_hosts`.`host_object_id`);

[eloyd]

Help me understand what your end goal is and I'll see if I can help you get there.

[seaward1983]

We're needing to understand the systems, services, thresholds, and who alerts are going to, in a csv format.

[eloyd]

You may find it easier to look at the configuration files, not the database. Especially since the database schema is subject to potential change between releases.

Take a look at /usr/local/nagios/var/objects.cache and you'll see every host and service pulled out with its complete, reduced configuration (meaning, no inherits). From there, it's rather trivial to awk/sed/grep your way through finding what you're looking for.

[seaward1983]

Thank you, but no, that won't work. This is why I'm working on a repeatable query, as this is not a one-time pull.

[eloyd]

The database schema is subject to change between releases. The configuration file format is not. So you are more likely to have to recreate this work if you go the SQL route than if you do text parsing on the resultant config files. After all - the information in the database is written to the config files, so the same information is available in both places.

I don't have time to post it now, but I'm pretty sure a relatively simple 5-6 line shell script could parse it all out. I'll take a swing at it tomorrow.

However, if you're looking at the DB, you should know that contacts have IDs, so the ID listed in the host/service entry points to the contacts table so you can join against that to match your contacts.

[CameronWP]

What about using the API?

[eloyd]

Functionally, it would be the same as parsing the objects.cache file, but yeah, that works too.

[eloyd]

Quick spin through the API (compared to what's in the config and the objects.cache file) shows that the API does NOT return the contact information for hosts and services. I consider this a bug, personally.

[CameronWP]

Yeah, I should've checked first and I agree, the fact that it doesn't return that information is an oversight in the API design for sure.