[ themattreid ]

So I have a constant battle with Charter Cable – high speed internet at home. Constantly causing all kinds of problems. So I decided to make a little script that runs from cron every minute that will track my outages in a single table database. Then I’ll be graphing the outages in real-time via jpGraph [as seen here]. Here’s the simple code.

The database table create script for router_uptime

CREATE TABLE `response` (
`id` bigint(32) NOT NULL auto_increment,
`state` tinyint(1) NOT NULL,
`ip_address` varchar(15) NOT NULL,
`Creation_time` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3833 ;

The Script

#-(0)> cat check_ping
#!/bin/sh
LOGFILE="/var/log/check_ping.log"
TLOGFILE="/var/log/check_traceroute.log"
PINGIP="24.205.205.1" #primary route ip address to check
SPINGIP="64.105.172.26" #secondary ip to ping
DATE=`date +"%c"`
#####
#DB SETTINGS
####
DBHOST="192.168.0.11"
DBUSER="xxxx"
DBPASS="xxxx"
DBNAME="router_uptime"
####
test -e ${LOGFILE} || touch ${LOGFILE}
test -e ${TLOGFILE} || touch ${TLOGFILE}
ping -c 2 ${PINGIP}
if [ $? = "1" ]; then
echo "$DATE: Could not reach ${PINGIP}" >> ${LOGFILE}
#insert info into DB
SQL="INSERT INTO \`router_uptime\`.\`response\` (\`id\` ,\`state\`, \`ip_address\`,\`Creation_time\`)VALUES (NULL,'0', '$PINGIP', NOW( ));"
echo $SQL > /tmp/check_ping.sql
/usr/bin/mysql -u$DBUSER --password=$DBPASS --host=$DBHOST $DBNAME < /tmp/check_ping.sql
ping -c 2 ${SPINGIP}
if [ $? = "1" ]; then
echo "$DATE: Could not reach ${SPINGIP}" >> ${LOGFILE}
echo "$DATE:" >> ${TLOGFILE}
traceroute ${PINGIP} >> ${TLOGFILE}
fi
else
#insert info into DB
SQL="INSERT INTO \`router_uptime\`.\`response\` (\`id\` ,\`state\`, \`ip_address\`,\`Creation_time\`)VALUES (NULL,'1', '$PINGIP', NOW( ));"
echo $SQL > /tmp/check_ping.sql
/usr/bin/mysql -u$DBUSER --password=$DBPASS --host=$DBHOST $DBNAME < /tmp/check_ping.sql
fi