Syslog server for pfsense that stores formatted data into MS SQL or MySQL

shaw22

Greetings:

I am looking to setup a syslog server and route the firewall logs from Pfsense.
The log should be parsed, formatted and stored in an MS Sql or MySQL server from which I can query (for a date/time range) the following:
Outbound connections:
Source IP and port / Destination IP and port / Destination URL
Inbound connections:
Source IP and port / Source URL / Destination IP and port

The output will be consolidated for the date/time range - that is one entry for each unique connection.

I will then create a 'whitelist' of Outbound and Inbound connection so that the matching entries in 'whitelist' can be excluded from future reports.

Is there a Log analyzer in existence that will do this? or Do I have to capture to SQL or MySQL server and run queries myself?

Thanks

kiokoman

afaik you have to do it yourself, you can grab the log with a remote syslog if you configure Status / System Logs / Settings after that you can do whatever you want

shaw22

@kiokoman Thank You,
Do you know a syslog server that works with pfsense and formats the log file and keep in MS SQL and MySQL database

kiokoman

rsyslogd can do it
https://www.rsyslog.com/doc/v8-stable/tutorials/database.html

shaw22

@kiokoman

My Firewall connection to Internet is 1Gbps and I am concerned about this statement:
" Database i/o is considerably slower than text file i/o. As such, directly writing to the database makes sense only if your message volume is low enough to allow a) the syslogd, b) the network, and c) the database server to catch up with it"

Is there a way to periodically copy the syslog text file to database - say every hour, rather than directly writing into the database?

kiokoman

i have never done something like this but it shouldn't be too difficult to create a cron job for it, it would be better to ask the rsyslog mailing list or your OS support forum

shaw22

Thanks Kiokoman. Also, do you know how to send syslog to more than one remote server. In the GUI it only allows to put in one ip address

kiokoman

how is that? i see 3 slot

shaw22

Thanks Kiokoman