[mrtg] Re: inserting log in database

[Paul Chvostek]

On Sat, Oct 26, 2002 at 09:10:44PM +0200, Christian Rasmussen wrote:
>
> To be able to save peaks according to user and so on, I would like to save
> the log data in a MySQL database. My idea would be to find the place in the
> code where the relevant SNMP data is retrieved and then insert some mysql
> commands to insert the data.. Can anyone tell me where in the code it would
> be appropriate to do this? I just need the exact same values which are saved
> to the MRTG log files.

Use the source, Luke.  Or...

>Of course I could also write a script to read all the
> logfiles, but it would be a bit easier if it could be implemented in MRTG
> itself.

Check recent list archives for table definitions I'm using.  With them,
along with simple shell script stuff like:

 #!/bin/sh
 mrtgspool=/var/spool/mrtg
 mysql=/usr/local/bin/mysql
 sqluser="myuser"
 sqlpass="mypw"
 sqldb="bandwidth"
 sqlhost="dbhost"
 q="SELECT date FROM mrtglog WHERE item=$1 ORDER BY -date LIMIT 1;"
 oldend="`$mysql -h$sqlhost -u$sqluser -p$sqlpass -e\"$q\" $sqldb 2>/dev/null | grep '^[0-9]'`"
 if [ -z "$oldend" ]; then
   oldend=0
 fi
 fmt='INSERT INTO mrtglog (item,date,avgin,avgout,peakin,peakout) VALUES (%.0f,%.0f,%.0f,%.0f,%.0f,%.0f);\n'
 if echo "$oldend" | grep -q '^[0-9][0-9]*$' && test -s $mrtgspool/$2.log ; then
   awk -v fmt="$fmt" -v oldend="$oldend" -v itemid="$1" 'BEGIN{ getline; }
     $1 <= oldend { nextfile; }
     $4 || $5 { printf(fmt, itemid, $1, 8*$2, 8*$3, 8*$4, 8*$5); }
   ' $mrtgspool/$2.log | sort -dn >> $tmpfile
 fi
 if [ -f $tmpfile ]; then
   $mysql -h$sqlhost -u$sqluser -p$sqlpass $sqldb < $tmpfile
 fi

You could probably do it a little more cleanly in Perl, but I'm a shell
weenie at heart.

Run this once an hour, and you can stick with stock MRTG code.  Verify
the usage of `sort` for your OS.  I use a slightly larger wrapper that
pulls the $1 and $2 pieces from another table, but you get the idea.
I've written PHP scripts which build PNG files on-the-fly from the data.

-- 
  Paul Chvostek                                             <paul at it.ca>
  Operations / Abuse / Whatever                          +1 416 598-0000
  it.canada - hosting and development                  http://www.it.ca/

--
Unsubscribe mailto:mrtg-request at list.ee.ethz.ch?subject=unsubscribe
Archive     http://www.ee.ethz.ch/~slist/mrtg
FAQ         http://faq.mrtg.org    Homepage     http://www.mrtg.org
WebAdmin    http://www.ee.ethz.ch/~slist/lsg2.cgi