SQL Query to report downtime
SQL Query to report downtime
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
How about this join:
It is probably more verbose than you wanted, but you can always reduce the selected fields.
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 -pnagiosxiFormer Nagios employee
"It is turtles. All. The. Way. Down. . . .and maybe an elephant or two."
VI VI VI - The editor of the Beast!
Come to the Dark Side.
"It is turtles. All. The. Way. Down. . . .and maybe an elephant or two."
VI VI VI - The editor of the Beast!
Come to the Dark Side.
Re: SQL Query to report downtime
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
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.
Former Nagios employee
"It is turtles. All. The. Way. Down. . . .and maybe an elephant or two."
VI VI VI - The editor of the Beast!
Come to the Dark Side.
"It is turtles. All. The. Way. Down. . . .and maybe an elephant or two."
VI VI VI - The editor of the Beast!
Come to the Dark Side.
Re: SQL Query to report downtime
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
You can actually filter out "planned" (scheduled) downtime from the availability report --> Reports --> Availability --> Advanced Options --> Hide Downtime.
Former Nagios employee
"It is turtles. All. The. Way. Down. . . .and maybe an elephant or two."
VI VI VI - The editor of the Beast!
Come to the Dark Side.
"It is turtles. All. The. Way. Down. . . .and maybe an elephant or two."
VI VI VI - The editor of the Beast!
Come to the Dark Side.
Re: SQL Query to report downtime
I will share this information - thank you!!