Page 1 of 1
SQL Query to report downtime
Posted: Thu Jul 24, 2014 9:28 am
by tsterite
Hello - I'm looking for a stepping stone, a SQL statement that someone has used to query for planned & unplanned downtime. I found the post in technical support where the tables needed were identified: hoststatus, servicestatus, statehistory and logentries. Each time I try linking tables in our reporting tool, the tool stops operating so I'm clearly not understanding how these tables relate to each other. I'm now poking around in phpMyAdmin attempting to figure this out. Has anyone written a successful SQL obtaining the downtime result set? Thank you, in advance for your help!
Re: SQL Query to report downtime
Posted: Thu Jul 24, 2014 2:56 pm
by abrist
How about this join:
Code: Select all
echo "select entry_time,name1,name2,author_name,comment_data,is_fixed,duration,scheduled_start_time,actual_start_time,scheduled_end_time,actual_end_time,was_started,was_cancelled from nagios.nagios_downtimehistory join nagios.nagios_objects on nagios.nagios_downtimehistory.object_id=nagios.nagios_objects.object_id;
" | mysql -pnagiosxi
It is probably more verbose than you wanted, but you can always reduce the selected fields.
Re: SQL Query to report downtime
Posted: Thu Jul 24, 2014 3:27 pm
by tsterite
Thank you for your help. I am reviewing the description of the downtimehistory table; and it apepars that this is specifically for scheduled downtime. I should have been more specific; I am also looking for information on when hosts and/or services go down outside of a scheduled downtime. Thoughts?
Re: SQL Query to report downtime
Posted: Thu Jul 24, 2014 4:31 pm
by abrist
This would be availability parsing. It can be very complex and this is why we offer a report for it. You will need to parse through the nagios.log or through the limited ndo written nagios.nagios_logentries and nagios.nagios_statehistory tables. A query will not be enough. You will need to iterate over all the entries for a particular set of objects. And then do the necessary math for each one to determine how long each critical state lasted. And then you can add them up and perform the division to get an availability percentage if necessary. You could look at a raw select on the statehistory table, but it will only show you when the state changed (hard) for the retention window set for the table.
Re: SQL Query to report downtime
Posted: Fri Jul 25, 2014 8:07 am
by tsterite
Yikes! I thought so - I saw the start & stop points in the table for some of the outages. We have used the provided reports, however, I was asked to provide something that will allow us to remove the "scheduled" or planned outages from the report; or at least report the 2 separately - planned outages are acceptable and unplanned are not. Thank you for your reply!
Re: SQL Query to report downtime
Posted: Fri Jul 25, 2014 9:42 am
by abrist
You can actually filter out "planned" (scheduled) downtime from the availability report --> Reports --> Availability --> Advanced Options --> Hide Downtime.
Re: SQL Query to report downtime
Posted: Mon Jul 28, 2014 7:38 am
by tsterite
I will share this information - thank you!!
Re: SQL Query to report downtime
Posted: Mon Jul 28, 2014 10:30 am
by tmcdonald
Are we free to close this up?