Using the avail.cgi script for reporting

[Tom DE BLENDE]

"Garrett, Matt M SITI-ITDIEEE" wrote:
> 
> As my Management seem to be bent on SLA reports.............

Well, I think you'll have a hard time finding a bigger buzz word than
SLA. What I do is dump all availability data into a MySQL database.
This can then be used through ODBC to create nice graphs and tables
from Access, Excel,...

Prerequisites:
* MySQL.
* A database called "reporting" with two tables: "hosts_last_month"
and "services_last_month". They can be empty.
* A GNU date. 
* Bash (might work with Bourne as well, didn't test).

You have to replace "guest" in the script by a username that has
access on your Nagios cgi's.

I launch it every first day of the month from cron. It updates the
tables hosts_last_month and services_last_month. It keeps copies of
all previous months in the format hosts_year_month and
services_year_month.

Here is the code:

<code>

#!/bin/sh
year=`date --date="last month" +%Y` 
month=`date --date="last month" "+%m"`


if [ "$month" -eq "1" -o "$month" -eq "3" -o "$month" -eq "5" -o
"$month" -eq "7" -o "$month" -eq "8" -o "$month" -eq "10" -o "$month"
-eq "12" ] ; then
	enddate="31";
elif [ "$month" -eq "2" ]; then
	if [ "$year" -eq "2004" -o "$year" -eq 2008 ]; then
		enddate="29";
	else	
		enddate="28";
	fi
else
	enddate="30";
fi


#
# Create report for all hosts
#

export
QUERY_STRING="show_log_entries=&host=all&timeperiod=custom&smon=$month&sday=1&syear=$year&shour=0&smin=0&ssec=0&emon=$month&eday=$enddate&eyear=$year&ehour=24&emin=0&esec=0&assumeinitialstates=yes&assumestateretention=yes&initialassumedstate=0&backtrack=1&csvoutput="
export REMOTE_USER="guest"
export REQUEST_METHOD="HEAD"

/usr/local/nagios/sbin/avail.cgi | grep -v TIME | grep -v
"Cache-Control: no-store" | grep -v "Pragma: no-cache" | grep -v
"Last-Modified:" | grep -v "Expires:" | grep -v "Content-type:
text/plain" | sed '/^$/d' |  sed 's/"//g' | sed 's/%//g' | sed 's/, /
/g' > /tmp/nagiosreporttmp.txt

cat -n /tmp/nagiosreporttmp.txt > /tmp/nagiosreport.txt

echo 'use reporting;' > /tmp/nagiosreport.sql
echo 'create table hosts_'$year'_'$month' (HOST_ID SMALLINT DEFAULT 1
NOT NULL, HOST_NAME VARCHAR(30), TIME_UP_SCHEDULED DECIMAL(5,2),
PERCENT_TIME_UP_SCHEDULED DECIMAL(5,2),
PERCENT_KNOWN_TIME_UP_SCHEDULED DECIMAL(5,2), TIME_UP_UNSCHEDULED INT,
PERCENT_TIME_UP_UNSCHEDULED DECIMAL(5,2),
PERCENT_KNOWN_TIME_UP_UNSCHEDULED DECIMAL(5,2), TOTAL_TIME_UP INT,
PERCENT_TOTAL_TIME_UP DECIMAL(5,2), PERCENT_KNOWN_TIME_UP
DECIMAL(5,2), TIME_DOWN_SCHEDULED INT, PERCENT_TIME_DOWN_SCHEDULED
DECIMAL(5,2), PERCENT_KNOWN_TIME_DOWN_SCHEDULED DECIMAL(5,2),
TIME_DOWN_UNSCHEDULED INT, PERCENT_TIME_DOWN_UNSCHEDULED DECIMAL(5,2),
PERCENT_KNOWN_TIME_DOWN_UNSCHEDULED DECIMAL(5,2), TOTAL_TIME_DOWN INT,
PERCENT_TOTAL_TIME_DOWN DECIMAL(5,2), PERCENT_KNOWN_TIME_DOWN
DECIMAL(5,2), TIME_UNREACHABLE_SCHEDULED INT,
PERCENT_TIME_UNREACHABLE_SCHEDULED DECIMAL(5,2),
PERCENT_KNOWN_TIME_UNREACHABLE_SCHEDULED DECIMAL(5,2),
TIME_UNREACHABLE_UNSCHEDULED INT, PERCENT_TIME_UNREACHABLE_UNSCHEDULED
DECIMAL(5,2), PERCENT_KNOWN_TIME_UNREACHABLE_UNSCHEDULED DECIMAL(5,2),
TOTAL_TIME_UNREACHABLE INT, PERCENT_TOTAL_TIME_UNREACHABLE
DECIMAL(5,2), PERCENT_KNOWN_TIME_UNREACHABLE DECIMAL(5,2),
TIME_UNDETERMINED_NOT_RUNNING INT,
PERCENT_TIME_UNDETERMINED_NOT_RUNNING DECIMAL(5,2),
TIME_UNDETERMINED_NO_DATA INT, PERCENT_TIME_UNDETERMINED_NO_DATA
DECIMAL(5,2), TOTAL_TIME_UNDETERMINED INT,
PERCENT_TOTAL_TIME_UNDETERMINED DECIMAL(5,2), PRIMARY KEY (HOST_ID));'
>> /tmp/nagiosreport.sql
echo 'LOAD DATA LOCAL INFILE "/tmp/nagiosreport.txt" INTO TABLE
hosts_'$year'_'$month';' >> /tmp/nagiosreport.sql

cat /tmp/nagiosreport.sql | /usr/bin/mysql

echo 'use reporting;' > /tmp/nagiosreport.sql
echo 'drop table hosts_last_month;' >> /tmp/nagiosreport.sql
echo 'create table hosts_last_month (HOST_ID SMALLINT DEFAULT 1 NOT
NULL, HOST_NAME VARCHAR(30), TIME_UP_SCHEDULED DECIMAL(5,2),
PERCENT_TIME_UP_SCHEDULED DECIMAL(5,2),
PERCENT_KNOWN_TIME_UP_SCHEDULED DECIMAL(5,2), TIME_UP_UNSCHEDULED INT,
PERCENT_TIME_UP_UNSCHEDULED DECIMAL(5,2),
PERCENT_KNOWN_TIME_UP_UNSCHEDULED DECIMAL(5,2), TOTAL_TIME_UP INT,
PERCENT_TOTAL_TIME_UP DECIMAL(5,2), PERCENT_KNOWN_TIME_UP
DECIMAL(5,2), TIME_DOWN_SCHEDULED INT, PERCENT_TIME_DOWN_SCHEDULED
DECIMAL(5,2), PERCENT_KNOWN_TIME_DOWN_SCHEDULED DECIMAL(5,2),
TIME_DOWN_UNSCHEDULED INT, PERCENT_TIME_DOWN_UNSCHEDULED DECIMAL(5,2),
PERCENT_KNOWN_TIME_DOWN_UNSCHEDULED DECIMAL(5,2), TOTAL_TIME_DOWN INT,
PERCENT_TOTAL_TIME_DOWN DECIMAL(5,2), PERCENT_KNOWN_TIME_DOWN
DECIMAL(5,2), TIME_UNREACHABLE_SCHEDULED INT,
PERCENT_TIME_UNREACHABLE_SCHEDULED DECIMAL(5,2),
PERCENT_KNOWN_TIME_UNREACHABLE_SCHEDULED DECIMAL(5,2),
TIME_UNREACHABLE_UNSCHEDULED INT, PERCENT_TIME_UNREACHABLE_UNSCHEDULED
DECIMAL(5,2), PERCENT_KNOWN_TIME_UNREACHABLE_UNSCHEDULED DECIMAL(5,2),
TOTAL_TIME_UNREACHABLE INT, PERCENT_TOTAL_TIME_UNREACHABLE
DECIMAL(5,2), PERCENT_KNOWN_TIME_UNREACHABLE DECIMAL(5,2),
TIME_UNDETERMINED_NOT_RUNNING INT,
PERCENT_TIME_UNDETERMINED_NOT_RUNNING DECIMAL(5,2),
TIME_UNDETERMINED_NO_DATA INT, PERCENT_TIME_UNDETERMINED_NO_DATA
DECIMAL(5,2), TOTAL_TIME_UNDETERMINED INT,
PERCENT_TOTAL_TIME_UNDETERMINED DECIMAL(5,2), PRIMARY KEY (HOST_ID));'
>> /tmp/nagiosreport.sql
echo 'LOAD DATA LOCAL INFILE "/tmp/nagiosreport.txt" INTO TABLE
hosts_last_month;' >> /tmp/nagiosreport.sql

cat /tmp/nagiosreport.sql | /usr/bin/mysql

#
# Create report for all services
#

export
QUERY_STRING="show_log_entries=&service=all&timeperiod=custom&smon=$month&sday=1&syear=$year&shour=0&smin=0&ssec=0&emon=$month&eday=$enddate&eyear=$year&ehour=24&emin=0&esec=0&assumeinitialstates=yes&assumestateretention=yes&initialassumedstate=0&backtrack=1&csvoutput="
export REMOTE_USER="guest"
export REQUEST_METHOD="HEAD"

/usr/local/nagios/sbin/avail.cgi | grep -v TIME | grep -v
"Cache-Control: no-store" | grep -v "Pragma: no-cache" | grep -v
"Last-Modified:" | grep -v "Expires:" | grep -v "Content-type:
text/plain" | sed '/^$/d' |  sed 's/"//g' | sed 's/%//g' | sed 's/, /
/g' > /tmp/nagiosreporttmp.txt

cat -n /tmp/nagiosreporttmp.txt > /tmp/nagiosreport.txt

echo 'use reporting;' > /tmp/nagiosreport.sql
echo 'create table services_'$year'_'$month' (SERVICE_ID SMALLINT
DEFAULT 1 NOT NULL, HOST_NAME VARCHAR(30), SERVICE_DESCRIPTION
VARCHAR(50), TIME_OK_SCHEDULED INT, PERCENT_TIME_OK_SCHEDULED
DECIMAL(5,2), PERCENT_KNOWN_TIME_OK_SCHEDULED DECIMAL(5,2),
TIME_OK_UNSCHEDULED INT, PERCENT_TIME_OK_UNSCHEDULED DECIMAL(5,2),
PERCENT_KNOWN_TIME_OK_UNSCHEDULED DECIMAL(5,2), TOTAL_TIME_OK INT,
PERCENT_TOTAL_TIME_OK DECIMAL(5,2), PERCENT_KNOWN_TIME_OK
DECIMAL(5,2), TIME_WARNING_SCHEDULED INT,
PERCENT_TIME_WARNING_SCHEDULED DECIMAL(5,2),
PERCENT_KNOWN_TIME_WARNING_SCHEDULED DECIMAL(5,2),
TIME_WARNING_UNSCHEDULED INT, PERCENT_TIME_WARNING_UNSCHEDULED
DECIMAL(5,2), PERCENT_KNOWN_TIME_WARNING_UNSCHEDULED DECIMAL(5,2),
TOTAL_TIME_WARNING INT, PERCENT_TOTAL_TIME_WARNING DECIMAL(5,2),
PERCENT_KNOWN_TIME_WARNING DECIMAL(5,2), TIME_UNKNOWN_SCHEDULED INT,
PERCENT_TIME_UNKNOWN_SCHEDULED DECIMAL(5,2),
PERCENT_KNOWN_TIME_UNKNOWN_SCHEDULED DECIMAL(5,2),
TIME_UNKNOWN_UNSCHEDULED INT, PERCENT_TIME_UNKNOWN_UNSCHEDULED
DECIMAL(5,2), PERCENT_KNOWN_TIME_UNKNOWN_UNSCHEDULED DECIMAL(5,2),
TOTAL_TIME_UNKNOWN INT, PERCENT_TOTAL_TIME_UNKNOWN DECIMAL(5,2),
PERCENT_KNOWN_TIME_UNKNOWN DECIMAL(5,2), TIME_CRITICAL_SCHEDULED INT,
PERCENT_TIME_CRITICAL_SCHEDULED DECIMAL(5,2),
PERCENT_KNOWN_TIME_CRITICAL_SCHEDULED DECIMAL(5,2),
TIME_CRITICAL_UNSCHEDULED INT, PERCENT_TIME_CRITICAL_UNSCHEDULED
DECIMAL(5,2), PERCENT_KNOWN_TIME_CRITICAL_UNSCHEDULED DECIMAL(5,2),
TOTAL_TIME_CRITICAL INT, PERCENT_TOTAL_TIME_CRITICAL DECIMAL(5,2),
PERCENT_KNOWN_TIME_CRITICAL DECIMAL(5,2),
TIME_UNDETERMINED_NOT_RUNNING INT,
PERCENT_TIME_UNDETERMINED_NOT_RUNNING DECIMAL(5,2),
TIME_UNDETERMINED_NO_DATA INT, PERCENT_TIME_UNDETERMINED_NO_DATA
DECIMAL(5,2), TOTAL_TIME_UNDETERMINED INT,
PERCENT_TOTAL_TIME_UNDETERMINED DECIMAL(5,2), PRIMARY KEY
(SERVICE_ID));' >> /tmp/nagiosreport.sql
echo 'LOAD DATA LOCAL INFILE "/tmp/nagiosreport.txt" INTO TABLE
services_'$year'_'$month';' >> /tmp/nagiosreport.sql

cat /tmp/nagiosreport.sql | /usr/bin/mysql

echo 'use reporting;' > /tmp/nagiosreport.sql
echo 'drop table services_last_month;' >> /tmp/nagiosreport.sql
echo 'create table services_last_month (SERVICE_ID SMALLINT DEFAULT 1
NOT NULL, HOST_NAME VARCHAR(30), SERVICE_DESCRIPTION VARCHAR(50),
TIME_OK_SCHEDULED INT, PERCENT_TIME_OK_SCHEDULED DECIMAL(5,2),
PERCENT_KNOWN_TIME_OK_SCHEDULED DECIMAL(5,2), TIME_OK_UNSCHEDULED INT,
PERCENT_TIME_OK_UNSCHEDULED DECIMAL(5,2),
PERCENT_KNOWN_TIME_OK_UNSCHEDULED DECIMAL(5,2), TOTAL_TIME_OK INT,
PERCENT_TOTAL_TIME_OK DECIMAL(5,2), PERCENT_KNOWN_TIME_OK
DECIMAL(5,2), TIME_WARNING_SCHEDULED INT,
PERCENT_TIME_WARNING_SCHEDULED DECIMAL(5,2),
PERCENT_KNOWN_TIME_WARNING_SCHEDULED DECIMAL(5,2),
TIME_WARNING_UNSCHEDULED INT, PERCENT_TIME_WARNING_UNSCHEDULED
DECIMAL(5,2), PERCENT_KNOWN_TIME_WARNING_UNSCHEDULED DECIMAL(5,2),
TOTAL_TIME_WARNING INT, PERCENT_TOTAL_TIME_WARNING DECIMAL(5,2),
PERCENT_KNOWN_TIME_WARNING DECIMAL(5,2), TIME_UNKNOWN_SCHEDULED INT,
PERCENT_TIME_UNKNOWN_SCHEDULED DECIMAL(5,2),
PERCENT_KNOWN_TIME_UNKNOWN_SCHEDULED DECIMAL(5,2),
TIME_UNKNOWN_UNSCHEDULED INT, PERCENT_TIME_UNKNOWN_UNSCHEDULED
DECIMAL(5,2), PERCENT_KNOWN_TIME_UNKNOWN_UNSCHEDULED DECIMAL(5,2),
TOTAL_TIME_UNKNOWN INT, PERCENT_TOTAL_TIME_UNKNOWN DECIMAL(5,2),
PERCENT_KNOWN_TIME_UNKNOWN DECIMAL(5,2), TIME_CRITICAL_SCHEDULED INT,
PERCENT_TIME_CRITICAL_SCHEDULED DECIMAL(5,2),
PERCENT_KNOWN_TIME_CRITICAL_SCHEDULED DECIMAL(5,2),
TIME_CRITICAL_UNSCHEDULED INT, PERCENT_TIME_CRITICAL_UNSCHEDULED
DECIMAL(5,2), PERCENT_KNOWN_TIME_CRITICAL_UNSCHEDULED DECIMAL(5,2),
TOTAL_TIME_CRITICAL INT, PERCENT_TOTAL_TIME_CRITICAL DECIMAL(5,2),
PERCENT_KNOWN_TIME_CRITICAL DECIMAL(5,2),
TIME_UNDETERMINED_NOT_RUNNING INT,
PERCENT_TIME_UNDETERMINED_NOT_RUNNING DECIMAL(5,2),
TIME_UNDETERMINED_NO_DATA INT, PERCENT_TIME_UNDETERMINED_NO_DATA
DECIMAL(5,2), TOTAL_TIME_UNDETERMINED INT,
PERCENT_TOTAL_TIME_UNDETERMINED DECIMAL(5,2), PRIMARY KEY
(SERVICE_ID));' >> /tmp/nagiosreport.sql
echo 'LOAD DATA LOCAL INFILE "/tmp/nagiosreport.txt" INTO TABLE
services_last_month;' >> /tmp/nagiosreport.sql

cat /tmp/nagiosreport.sql | /usr/bin/mysql

rm -f /tmp/nagiosreport.sql
rm -f /tmp/nagiosreporttmp.txt
rm -f /tmp/nagiosreport.txt

<code>

If anyone is interested, I might clean it up (move paths to variables
etc...) a bit and post it to the contrib.

Kind regards,
Tom


-------------------------------------------------------
This SF.net email is sponsored by: SlickEdit Inc. Develop an edge.
The most comprehensive and flexible code editor you can use.
Code faster. C/C++, C#, Java, HTML, XML, many more. FREE 30-Day Trial.
www.slickedit.com/sourceforge
_______________________________________________
Nagios-users mailing list
Nagios-users at lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/nagios-users
::: Please include Nagios version, plugin version (-v) and OS when reporting any issue. 
::: Messages without supporting info will risk being sent to /dev/null