Hello,
I am trying to create a query which will calculate the minutes of Availability/Unavailability for Services/Hosts from the nagios_db MySQL database updated by NDOUtils.
I have been using the SQL below to get results from our nagios_db. It has been adapted from the ‘service_status.sql’ supplied in the NDO distro.
I need to get a better idea about qualifying what constitutes a RECOVERY following a CRITICAL based on HARD states. Does anyone have some insight into this issue?
Has anyone already done this? I keep hitting a wall from several different angles, the Calculation makes little or no sense as it reports negative numbers in many cases. I’ve tried several combinations of TIME/DATE fields and can’t seem to pull out the data.
Thx
###############################
SELECT nagios_instances.instance_id,
nagios_instances.instance_name,
nagios_services.host_object_id,
obj1.name1 AS host_name,
nagios_services.service_object_id,
obj1.name2 AS service_description,
###############################
############################### Need to factor correct fields here
TIMESTAMPDIFF(MINUTE, nagios_servicestatus.last_time_critical,nagios_servicestatus.last_hard_state_change) AS DIFF,
###############################
nagios_servicestatus.*
FROM ( ( nagios_db.nagios_services nagios_services
LEFT OUTER JOIN
nagios_db.nagios_instances nagios_instances
ON (nagios_services.instance_id = nagios_instances.instance_id))
RIGHT OUTER JOIN
nagios_db.nagios_servicestatus nagios_servicestatus
ON (nagios_servicestatus.service_object_id =
nagios_services.service_object_id))
LEFT OUTER JOIN
nagios_db.nagios_objects obj1
ON (nagios_servicestatus.service_object_id = obj1.object_id)
WHERE nagios_services.config_type = '1’
ORDER BY nagios_instances.instance_name ASC, obj1.name1 ASC, obj1.name2 ASC