SQL/API query to find disabled hosts and services?

[ottow]

Hi,

We are finding that despite repeated instructions to not do it, some of our admins like to disable hosts and services rather than use the Schedule Downtime feature.
This presents a problem if they forget to re-enable...

Could you please help us define a SQL or API query that will list all disabled Hosts and Services, so we can report on these?

Thanks,

Otto

[benjaminsmith]

Hi Otto,

There maybe a few ways to tackle this one. Just to clarify, are these hosts and services being de-activated in the Core Configuration Manager (CCM)? It's also possible to turn off active/passive checks or notifications using the Command options for the host or service status detail page. Let me know.

If you're de-activating from the CCM, you can run the following query.

Code: Select all

# login into mysql
MySQL -u root -p
use nagiosql;

For hosts:

Code: Select all

SELECT host_name
FROM tbl_host
WHERE active = '0';

For services:

Code: Select all

SELECT tbl_host.host_name, service_description FROM tbl_host, tbl_service, tbl_lnkServiceToHost WHERE tbl_service.active = '0' AND tbl_service.id =  tbl_lnkServiceToHost.idMaster AND tbl_host.id =  tbl_lnkServiceToHost.idSlave GROUP BY tbl_host.host_name, service_description;

--Benjamin

[ottow]

Thanks, this is what I was looking for but your answer also made me realize there are other ways admins could avoid following instructions to use Scheduled Downtime...
Subsequently, I'd appreciate if your could help with SQL queries listing Hosts and Services with Notifications disabled, and also Hosts and Services with disabled active/passive checks.

[benjaminsmith]

Hi @ottow,

The queries are very similar, just plugin in the options you want to pull from. You can run the following command to see all table structure.

Code: Select all

describe tbl_host;
describe tbl_service;

Host Queries

Code: Select all

#Host Active Checks Disabled
SELECT host_name
FROM tbl_host
WHERE active_checks_enabled  = 0;

#Host Notifications Disabled
SELECT host_name
FROM tbl_host
WHERE notifications_enabled = 0;

Services

Code: Select all

# ServiceNotifications Disabled
SELECT tbl_host.host_name, service_description FROM tbl_host, tbl_service, tbl_lnkServiceToHost WHERE tbl_service.notifications_enabled=0 AND tbl_service.id =  tbl_lnkServiceToHost.idMaster AND tbl_host.id =  tbl_lnkServiceToHost.idSlave GROUP BY tbl_host.host_name, service_description;

# Service Active Checks Disabled
SELECT tbl_host.host_name, service_description FROM tbl_host, tbl_service, tbl_lnkServiceToHost WHERE tbl_service.active_checks_enabled=0 AND tbl_service.id =  tbl_lnkServiceToHost.idMaster AND tbl_host.id =  tbl_lnkServiceToHost.idSlave GROUP BY tbl_host.host_name, service_description;

Regards,
Benjamin

[SteveBeauchemin]

This is what I call a gem of a post.

I plan to take all those queries, turn them into what I call Finder tests. I'll make them special services that are not activated, but can be run from the GUI to see the result. Then, I can open the Finder service test in the CCM, click the "Run Check Command" button, and see on the spot if we have an issue of training to deal with.

Thanks.

Steve B

[scottwilkerson]

It is also worth pointing out that users could just disable active checks through the UI, and not in the CCM, these can be found by navigating to the following URLs

Code: Select all

/nagiosxi/includes/components/xicore/status.php?show=services&serviceattr=16
/nagiosxi/includes/components/xicore/status.php?show=hosts&hostattr=16

Or they can be queried through the host/service status API by appending the following to the URL

Code: Select all

&active_checks_enabled=0

Or they could disable notifications through the UI, and not in the CCM, these can be found by navigating to the following URLs

Code: Select all

/nagiosxi/includes/components/xicore/status.php?show=services&serviceattr=4096
/nagiosxi/includes/components/xicore/status.php?show=hosts&hostattr=4096

Or they can be queried through the host/service status API by appending the following to the URL

Code: Select all

&notifications_enabled=0

[ottow]

This is what I call a gem of a post.

I plan to take all those queries, turn them into what I call Finder tests. I'll make them special services that are not activated, but can be run from the GUI to see the result. Then, I can open the Finder service test in the CCM, click the "Run Check Command" button, and see on the spot if we have an issue of training to deal with.

Thanks.

Steve B

You may want to take a look at https://support.nagios.com/forum/viewto ... 16&t=59669 too then.
My approach is a script that runs these queries weekly and e-mails the results to the Nagios Admins for them to fix.

[scottwilkerson]

This is what I call a gem of a post.

I plan to take all those queries, turn them into what I call Finder tests. I'll make them special services that are not activated, but can be run from the GUI to see the result. Then, I can open the Finder service test in the CCM, click the "Run Check Command" button, and see on the spot if we have an issue of training to deal with.

Thanks.

Steve B

You may want to take a look at https://support.nagios.com/forum/viewto ... 16&t=59669 too then.
My approach is a script that runs these queries weekly and e-mails the results to the Nagios Admins for them to fix.

Thanks @ottow

[ottow]

It is also worth pointing out that users could just disable active checks through the UI, and not in the CCM, these can be found by navigating to the following URLs

Code: Select all

/nagiosxi/includes/components/xicore/status.php?show=services&serviceattr=16
/nagiosxi/includes/components/xicore/status.php?show=hosts&hostattr=16

Or they can be queried through the host/service status API by appending the following to the URL

Code: Select all

&active_checks_enabled=0

Or they could disable notifications through the UI, and not in the CCM, these can be found by navigating to the following URLs

Code: Select all

/nagiosxi/includes/components/xicore/status.php?show=services&serviceattr=4096
/nagiosxi/includes/components/xicore/status.php?show=hosts&hostattr=4096

Or they can be queried through the host/service status API by appending the following to the URL

Code: Select all

&notifications_enabled=0

This is another good point, although I am unable to figure out how to set up the API query properly.

The following query works with 1 disabled notification. But not 2:
curl -XGET -k -L --silent "http://nagiosserver.corp.com/nagiosxi/a ... _enabled=0"| jq '.servicestatus.host_name, .servicestatus.name' |tr -d '"' |paste - -

And this works with 2 or more disabled notifications. But not with 1:
curl -XGET -k -L --silent "http://nagiosserver.corp.com/nagiosxi/a ... _enabled=0"| jq '.servicestatus[] | .host_name, .name' |tr -d '"' |paste - -

I am clearly missing something here...

[scottwilkerson]

I'm not familiar with what you are running after the curl command, that is not part of the Nagios XI API