Alert on logging errors

garetht

Hallo

Does anyone have a method to alert on errors writing to mysql (or any source)?

We had an incident recently, and I found that our Kiwi Syslog instance was unable to write to mysql.  It was spamming up it's error logs, but nothing else.  Ironically, if Kiwi wrote error logs into the Windows Event log, I'd be able to detect those & send out alerts, but I see nothing in the Event logs.

garetht

(replying to myself in case it helps anyone else out)

Hacked together this script to check if the latest entry in your mysql syslogs database is over an hour old & email an alert if so.

I'm sure the script could be cleaned, but it does the job (:

 

#!/bin/bash

mysql_hostname="-h ip-address-of-your-mysql-server"
mysql_username="-u mysql-username"
mysql_userpass="-pmysql-password"

# Procedure to mail the results
mailresult () {
        rm -f /tmp/dbcheckermail
        echo "HELO domain.com" >> /tmp/dbcheckermail
        echo "MAIL FROM: <syslog@domain.com>" >> /tmp/dbcheckermail
        echo "RCPT TO: <alertaddress@domain.com>" >> /tmp/dbcheckermail
        echo "DATA" >> /tmp/dbcheckermail
        echo "Subject: Sylog database is over an hour stale" >> /tmp/dbcheckermail
        echo "The time now is:                                          `date -d @$NOWDATE`.
The last entry in the Syslog database is from:  `date -d @$DBDATE`.
This is over $DIFF seconds stale" >> /tmp/dbcheckermail
        echo "." >> /tmp/dbcheckermail
        nc ip-address-of-SMTP-server 25 < /tmp/dbcheckermail
        rm -f /tmp/dbcheckermail
        logger -p error Sylog database is over an hour stale
                exit 0
        }

RESULT=$(mysql $mysql_username $mysql_userpass syslog-database-name -s -e "select datetime from syslog-table order by datetime desc limit 1")

rm -f /tmp/datestamp.txt
rm -f /tmp/datestampin.txt

echo $RESULT > /tmp/datestampin.txt
awk '{print $2 }' /tmp/datestampin.txt > /tmp/datestamp.txt
DBDATE=`cat /tmp/datestamp.txt`
DBDATE=`date +%s -d"$DBDATE"`

NOWDATE=`date +%s`

rm -f /tmp/datestamp.txt
rm -f /tmp/datestampin.txt

DIFF=$(( $NOWDATE - $DBDATE ))

if [ "$DIFF" -gt "3600" ]; then
        mailresult
fi