SELECT
obj2.name1 AS host_name,
obj2.name2 AS service_description
FROM nagios_servicegroups
INNER JOIN nagios_servicegroup_members ON
nagios_servicegroups.servicegroup_id=nagios_servicegroup_members.servicegroup_id
INNER JOIN nagios_services ON
nagios_servicegroup_members.service_object_id=nagios_services.service_object_id
INNER JOIN nagios_objects as obj1 ON
nagios_servicegroups.servicegroup_object_id=obj1.object_id
INNER JOIN nagios_objects as obj2 ON
nagios_servicegroup_members.service_object_id=obj2.object_id
INNER JOIN nagios_instances ON
nagios_servicegroups.instance_id=nagios_instances.instance_id
WHERE nagios_servicegroups.alias ='{alias_service_group}'
AND nagios_services.display_name ='{alias_de_servicio}'

Para conocer los estados de los hosts
SELECT nagios_hosts.display_name,nagios_hoststatus.current_state
FROM
nagios_hoststatus,nagios_hosts
WHERE nagios_hoststatus.host_object_id = nagios_hosts.host_object_id;

Resultando en una tabla como esta
display_name
firewall
apache
servidor-sql
sap-db
Para conocer el estado de un servicio,
que se encuentre disponible

current_state
0
1
0
0
independientemente de los hosts en los

En este caso el servicio HTTP
SELECT obj1.name1 AS host_name,
nagios_services.service_object_id,
obj1.name2 AS service_description,
nagios_servicestatus.current_state
FROM `nagios_servicestatus`
LEFT JOIN nagios_objects AS obj1 ON nagios_servicestatus.service_object_id =
obj1.object_id
LEFT JOIN nagios_services ON nagios_servicestatus.service_object_id =
nagios_services.service_object_id
LEFT JOIN nagios_instances ON nagios_services.instance_id =
nagios_instances.instance_id
WHERE nagios_services.config_type = '1'
AND obj1.name2 = 'HTTP'

Resultando en una tabla como esta
host_name

service_object_id service_descripti
on
concentrador_mpls 18
HTTP
router-vpn
23
HTTP
central
28
HTTP
router-45
33
HTTP

current_state
0
1
2
0