Nagios Support Forum

I use the availability Report with Host(s) for step 1, ** ALL HOSTS ** for step 2, and This Year for the Report Period in Step 3, to get the Average % Time Up for all the hosts. This number is entered frequently into our IT Metrics. What I would like to have is a MySQL Query that could pull the same results from the database. The goal would be to automate the placement of that metric so that it would always be real time. It wouldn't have to calculate the average, if it would just return the percentage for each host, I could automate calculating the average. Is this possible?

What version of XI are you currently using? Is what you're wanting something that is different than what is in the new availability report in R2011?

I am using Nagios XI 2009R1.4B. My availability report doesn't look like that one, but produces the result I need. What I am trying to do is have an Excel sheet on a sharepoint site pull that value (Host Average UP %) when ever the sheet is opened. I figured I could do this with an ODBC connection if I knew the MySQL Query that retrieves the value for the NagiosXI report. I assume its a fairly complex query taking into account host down times and checking them against scheduled down time. I've looked through the databases and am not sure what fields to use or the formula for calculating the percent.

Yeah that's a tricky one, because the query also factors the host and service list based on the user. If you want I can maybe hunt for those queries. The other option is that the new reports in Nagios XI 2011 also have the ability to export data into CSV format. That might be a simpler solution.

I knew it would be tricky. I can open the spreadsheet and enter 99.79% faster that exporting to CSV format and importing. It's not that the process is difficult, it's just that the company goal is to have all metrics automated and available in real time. This is the last metric to automate for Informations Systems, and we are suppose to set the standard. I would like to get those queries and see what I can do. I am not in a huge hurry, but this was one of our goals with purchasing XI. The difficulty involved with the project is not important. It is important to management that all systems report live data without daily user intervention.

Thanks for your help

I'll do some digging and see if I can get you the queries. I'm assuming you're comfortable taking it from there once you have the data you need?

Well you're probably not going to be terribly excited to hear this, but the availability reports are actually created by making a backend call to one of the Nagios Core cgi's. The output of the cgi is determined by the arguments passed to it, example:

http://<yourserver>/nagios/cgi-bin/avail.cgi?host=192.168.5.32&show_log_entries&t1=1306268908&t2=1306873708&backtrack=4&assumestateretention=yes&assumeinitialstates=yes&assumestatesduringnotrunning=yes&initialassumedhoststate=0&initialassumedservicestate=0&show_log_entries&showscheduleddowntime=yes

The other place to get unprocessed state histories is in the mysql nagios.nagios_statehistory table. This gives raw state information for both hosts and services, and you'd need to to a JOIN to grab the actual host and service names. I'm not an SQL whiz myself, so you might have to play with the query a bit to have the information display in a useful format, but at the moment that's all that we have in the direction you need. Most people have needed either a pdf or CSV export, so those are the pieces we've developed.