Consulta simple para obtener un pantallazo general del estado de servicios de
un hostgroup
SELECT
CASE st.current_state
WHEN 0 THEN 'OK'
WHEN 1 THEN 'WARNING'
WHEN 2 THEN 'CRITICAL'
WHEN 3 THEN 'UNKNOWN'
END AS states,
COUNT(st.current_state) AS number
FROM nagios_hostgroup_members m, nagios_services s,
nagios_servicestatus st
WHERE m.host_object_id = s.host_object_id
AND s.service_object_id = st.service_object_id
AND hostgroup_id IN
(
SELECT hostgroup_id
FROM nagios_hostgroups hg, nagios_objects o
WHERE hg.hostgroup_object_id = o.object_id
AND o.name1 = '{nombre_hostgroup}'
)
GROUP BY st.current_state;

Resultando en una tabla como esta
states

number

OK
1754
WARNING
13
CRITICAL
84
UNKNOWN
20
Consulta simple para obtener un conteo general de los estados generales
ordenados por meses de un año determinado.
SELECT
CONCAT('',DATE_FORMAT(nagios_statehistory.state_time,'%M')) AS TEMPS,
ELT(nagios_statehistory.state+1,'OK','WARNING','CRITICAL','UNKNOWN') AS ETAT,
CONCAT('',COUNT(nagios_statehistory.state)) AS QUANTITE
FROM
`nagios_statehistory`
LEFT JOIN nagios_objects AS obj1 ON
nagios_statehistory.object_id=obj1.object_id
LEFT JOIN nagios_instances ON
nagios_statehistory.instance_id=nagios_instances.instance_id
WHERE
obj1.objecttype_id='2'
AND nagios_statehistory.state_type = 1
AND DATE_FORMAT(nagios_statehistory.state_time,'%Y') = 2010
GROUP BY
TEMPS, ETAT
ORDER BY
DATE_FORMAT(nagios_statehistory.state_time,'%m'),nagios_statehistory.state

Resultando en una tabla como esta
TEMPS
November
November
November

ETAT
OK
WARNING
CRITICAL

QUANTITE
1754
13
84