syslog-ng + mySQL + firewall

[vortmax]

I'm attempting to get my firewall to remotely dump into an mySQL database set up on an Ubuntu 7 box. I am using syslog-ng and have successfully configured it to pull the logs from the firebox and store them on the local disk. So that half of the problem is solved. I just need to pipe them into the mySQL dbase now.

The three sections that matter in my syslog-ng.conf file are:

source firebox {
       unix-stream("/dev/log");
       udp();
       tcp(ip(0.0.0.0) port(4107));
       tcp(ip(0.0.0.0) port(4115));
       internal();
};

destination d_mysql { 
		program("/usr/bin/mysql --user=sqluser --password=xxxx firelog < /var/log/mysql.pipe"); 
		pipe ("/var/log/mysql.pipe" template("INSERT INTO logs (MONTH, DAY, TIME, LOCIP, MODULE, TYPE, DIRECTION, ADP, PORT, PROTO, TW, BIT, SRCIP, DESTIP, BIT2, BIT3, CAST) VALUES ( '$MONTH', '$DAY', '$HOUR:$MIN:$SEC', '$LOCIP','$MODULE ,'$TYPE', '$DIRECTION','$ADP','$PORT','$PROTO','$TW','$BIT','$SRCIP','$DESTIP','$BIT2','$BIT3','($CAST)' );\n") template-escape(yes)); 
	};

log { 
	source(firebox); 
	destination(d_mysql); 
};

I of course made the pipe 'mysql.pipe' in /var/log/ with mkfifo
I can't tell if the issue is parsing the incoming stream into a format the sql database can use or if the database is not configured correctly. I do have a database (firelog) with table (log) and all of those fields defined.

So...first off, does anyone see an obvious solution? Secondly, can anyone help make sure my template is correct. I can't find any documentation on how to properly structure these things. Here is a sample line of my firewall output:

Oct  5 18:53:15 192.168.xxx.x kernel deny in eth0 604 udp 20 255 0.0.0.0 255.255.2xxx.xxx 68 67  (broadcast)

I've already looked at most websites discussing this issue and none of them talk about custom log log templates. They all just refer back to standard syslog structure
thanks

 

[geirendre]

Take a look at this howto from Debian:

http://www.debianhelp.co.uk/syslog-ng.htm

esspesialy the part: Setup syslog-ng to MySQL pipe:

 An example for a script that feeds log entries from the FIFO pipe to MySQL is included in the scripts directory. The script is called syslog2mysql.sh.
#!/bin/bash

if [ ! -e /var/log/mysql.pipe ]
then
mkfifo /var/log/mysql.pipe
fi
while [ -e /var/log/mysql.pipe ]
do
mysql -u syslogfeeder --password=PASS_HERE syslog < /var/log/mysql.pipe >/dev/null
done

If you decide to use this script then you have to replace PASS_HERE with the password for the syslogfeeder user. You will also probably want to have this script started automatically whenever you start the server. So add an entry in the inittab or start it through init.d (or whatever is appropriate on your system). But make sure you call it after MySQL has been started.

Think what you'r missing is something to take the entries
from the fifo-pipe and put them in MySql.

Also check this tread:

http://www.tek-tips.com/viewthread.cfm?qid=1367346