Logging connections to external SQL database or files

[toomeek]

Hello,

I would log every estabilished and closed connection with following data:

• date/time estabilished
• date/time closed
• IP LAN -> Internet
• Internet -> IP LAN
• MAC address of LAN device should be also included
• avoid repeating messages

Right now (not on pfSense but on Linux box) I'm doing just tcpdump with following script:

#!/bin/bash
#Check if directory exists, if not create
if test -d /home/logi/
        then echo "Directory already exists!"
        else  mkdir /home/logi
        fi
#Moving files
mv /home/logi/tcp-syn.dmp /home/logi/tcp-restart-`date +%d-%B-%Y--%H-%M`.dmp
#This is dump into RAW data
#tcpdump -i eth2 tcp [13] == 2 -w /home/logi/tcp-syn.dmp & tar cvfz /home/logi/tcp-restart-`date +%d-%B-%Y--%H-%M`.tar.gz /h
#This is dump into TXT data
tcpdump -i eth2 tcp [13] == 2 -n >> /home/logi/tcp-syn.dmp & tar cvfz /home/logi/tcp-restart-`date +%d-%B-%Y--%H-%M`.tar.gz
rm /home/logi/tcp-restart-`date +%d-%B-%Y--%H-%M`.dmp

This creates daily logs in separate files, for example:

tcp-restart-08-February-2011--16-20.tar.gz
tcp-31-july-2011.tar.gz

Eth2 is my LAN interface.
And they are really small files (but don't know does it log every single estabilished connection).

Now I have to ask how to log connections in pfSense? Let's assume I would like to collect all logs from many pfSense boxes in one place :)

[marcelloc]

Use syslog to forward pfsense logs to this Linux box and use the same script on log received.

Or use a syslog server That do this sql/frontend for you.