I found the following article that takes a minimalist approach at managing syslog with Puppet: http://itscblog.tamu.edu/managing-syslog-and-log-forwarding-with-puppet/
Syslog
Configuring syslog-ng to store logs into a MySQL database
This short article describes how to configure syslog-ng in order to store the logs into a MySQL backend. This adds more flexibility when performing log analysis, log searching and correlation.
Installing MySQL
MySQL can be compiled from source and installed using the FreeBSD ports collection:
# cd /usr/ports/databases/mysql41-server/ # make install distclean
Configuring MySQL
Use the following commands to create the MySQL database directory and install a configuration file:
# mkdir /var/db/mysql # cp /usr/local/share/mysql/my-small.cnf /var/db/mysql/my.cnf
Optionally, edit /var/db/mysql/my.cnf
to adjust some parameters like:
- Disabling networking support.
This can be done by uncommenting the
skip-networking
option from the configuration file.This will prevent MySQL from listening on port 3306/tcp. Since we are using MySQL locally, we can use UNIX sockets instead of true networking.
- Reducing memory usage.
By adjusting
innodb_buffer_pool_size
andinnodb_additional_mem_pool_size
to values suited to the amount of RAM available to FreeBSD
Next, add the following lines into /etc/rc.conf
so that MySQL will get launched during system startup:
# MySQL mysql_enable="YES" mysql_limits="YES" mysql_dbdir="/var/db/mysql" mysql_args=""
Starting MySQL
Use the following command to start MySQL:
# /usr/local/etc/rc.d/mysql-server.sh start
Creating the MySQL database
The logs will get stored into a table named logs on database syslog.
To create the database and table, create a file named syslog.sql
with the following SQL commands:
CREATE DATABASE syslog; USE syslog; CREATE TABLE logs ( host varchar(32) default NULL, facility varchar(10) default NULL, priority varchar(10) default NULL, level varchar(10) default NULL, tag varchar(10) default NULL, timestamp datetime default NULL, program varchar(15) default NULL, msg text, seq int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (seq), KEY host (host), KEY seq (seq), KEY program (program), KEY timestamp (timestamp), KEY priority (priority), KEY facility (facility) );
Then, process those SQL commands using MySQL client:
# mysql -u root -p <syslog .sql
Setting up the communication channel
syslog-ng will issue INSERT INTO SQL commands into a UNIX pipe for every log received and processed. Those SQL commands will be retrieved from the UNIX pipe and will be injected into MySQL.
This UNIX pipe will act as the communication channel between syslog-ng and MySQL. To create the UNIX pipe:
# mkfifo /tmp/mysql.pipe
Also, we will create a startup script used to keep feeding SQL commands sent to the UNIX pipe to MySQL called /usr/local/etc/rc.d/040.mysql-syslog.sh
:
( while [ -e /tmp/mysql.pipe ] do /usr/local/bin/mysql -u root --password= syslog
This script will get invoked at startup and will keep feeding the SQL commands generated by the mysql
syslog-ng destination into the MySQL database.
However, we must make sure this startup script is invoked after MySQL has been started. Thus, in FreeBSD I recommend renaming the MySQL startup script:
# mv /usr/local/etc/rc.d/mysql-server.sh /usr/local/etc/rc.d/030.mysql-server.sh
Setting up syslog-ng
Modify /usr/local/etc/syslog-ng/syslog-ng.conf
to add a new source called net
used to retrieve logs via the network:
source net { udp(); };
Next, add a new destination for MySQL:
destination mysql { pipe("/tmp/mysql.pipe" template("INSERT INTO logs (host, facility, priority, level, tag, timestamp, program, msg) VALUES ( '$HOST', '$FACILITY', '$PRIORITY', '$LEVEL', '$TAG', '$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC', '$PRORAM', '$MSG' );n") template-escape(yes) ); };
Finally, configure syslog-ng so all logging is sent to the MySQL destination. Since syslog-ng allows multiple destinations, this makes perfectly possible to keep locally-generated log events stored in text files alongside the MySQL database.
log { source(net); destination(mysql); }; log { source(src); destination(mysql); };
Finally, we send the SIGHUP
signal to syslog-ng to instruct it to re-read its configuration file and reconfigure accordingly:
# pkill -HUP syslog-ng
Remote logging with Linksys WRT54G
Enabling remote syslog logging with Linksys WRT54G and OpenWRT White Russian RC3 is as simple as storing the IP of the remote syslog server into the log_ipaddr NVRAM variable:
nvram set log_ipaddr=A.B.C.D nvram commit reboot
log_ipaddr is used by /etc/init.d/rcS startup script to launch a local syslog daemon with option “-R “:
#!/bin/sh syslog_ip=$(nvram get log_ipaddr) ipcalc -s "$syslog_ip" || syslog_ip="" syslogd -C 16 ${syslog_ip:+-L -R $syslog_ip} klogd ...
syslog-ng replacement for FreeBSD
FreeBSD uses syslog by default. However, syslog is very old and inflexible, so I decided to replace it with syslog-ng. syslog-ng syntax is far more easier to read and understand than syslog’s one.
syslog-ng uses the following elements to determine what to log and where to log it:
- Source: Defines where log entries do come from. For example, syslog-ng can read log entries from the /var/run/log local socket, from the network via UDP port 514, via TCP, and so on.
I just decided to split the sources in two:
- A local source, called src
- A network source, called net
This allows easily to distinguish between locally generated log entries and log entries generated elsewhere by a network device or host machine.
- Destination: Defines where do log entries will get logged into. For example, log entries can be written to a file, can be sent to another syslog-compatible server, sent to a socket, and so on.
I have kept the default destinations, and added a new one called airport, pointing to file /var/log/airport.log. All log events generated by my AirPort Express Wireless Access Point will get logged into this destination.
- Filter: Defines a matching criteria for log entries. Allows to distinguish log entries by some common attributes, like the source host, facility, logging level, a regular expression matching the entry description, and so on.
I have kept the default filters, but added a new one called f_airport, which matches all log entries whose source is my Wireless Access Point.
Finally, log entries combine sources, filters and destinations. When a log entry is received, it is matched against every log rules until a source and a filter matches. Then, the log entry is sent to the destination or destinations for that matching rule.
Since I wanted to centralize some logs into my FreeBSD server across the network, concretely my AirPort Express logs, this is the /usr/local/etc/syslog-ng/syslog-ng.conf file I used to achieve it:
# # options # options { long_hostnames(off); sync(0); }; # # sources # source src { unix-dgram("/var/run/log"); unix-dgram("/var/run/logpriv" perm(0600)); internal(); file("/dev/klog"); }; source net { udp(); }; # # destinations # destination messages { file("/var/log/messages"); }; destination security { file("/var/log/security"); }; destination authlog { file("/var/log/auth.log"); }; destination maillog { file("/var/log/maillog"); }; destination lpd-errs { file("/var/log/lpd-errs"); }; destination xferlog { file("/var/log/xferlog"); }; destination cron { file("/var/log/cron"); }; destination debuglog { file("/var/log/debug.log"); }; destination consolelog { file("/var/log/console.log"); }; destination all { file("/var/log/all.log"); }; destination newscrit { file("/var/log/news/news.crit"); }; destination newserr { file("/var/log/news/news.err"); }; destination newsnotice { file("/var/log/news/news.notice"); }; destination console { file("/dev/console"); }; destination allusers { usertty("*"); }; #destination loghost { udp("loghost" port(514)); }; destination airport { file("/var/log/airport.log"); }; destination linksys { file("/var/log/linksys.log"); }; # # log facility filters # filter f_auth { facility(auth); }; filter f_authpriv { facility(authpriv); }; filter f_not_authpriv { not facility(authpriv); }; filter f_console { facility(console); }; filter f_cron { facility(cron); }; filter f_daemon { facility(daemon); }; filter f_ftp { facility(ftp); }; filter f_kern { facility(kern); }; filter f_lpr { facility(lpr); }; filter f_mail { facility(mail); }; filter f_news { facility(news); }; filter f_security { facility(security); }; filter f_user { facility(user); }; filter f_uucp { facility(uucp); }; filter f_local0 { facility(local0); }; filter f_local1 { facility(local1); }; filter f_local2 { facility(local2); }; filter f_local3 { facility(local3); }; filter f_local4 { facility(local4); }; filter f_local5 { facility(local5); }; filter f_local6 { facility(local6); }; filter f_local7 { facility(local7); }; # # log level filters # filter f_emerg { level(emerg); }; filter f_alert { level(alert..emerg); }; filter f_crit { level(crit..emerg); }; filter f_err { level(err..emerg); }; filter f_warning { level(warning..emerg); }; filter f_notice { level(notice..emerg); }; filter f_info { level(info..emerg); }; filter f_debug { level(debug..emerg); }; filter f_is_debug { level(debug); }; # # airport filter # filter f_airport { host("airport"); }; # # linksys filter # filter f_linksys { host("linksys"); }; # # *.err;kern.warning;auth.notice;mail.crit /dev/console # log { source(src); filter(f_err); destination(console); }; log { source(src); filter(f_kern); filter(f_warning); destination(console); }; log { source(src); filter(f_auth); filter(f_notice); destination(console); }; log { source(src); filter(f_mail); filter(f_crit); destination(console); }; # # *.notice;authpriv.none;kern.debug;lpr.info;mail.crit;news.err /var/log/messages # log { source(src); filter(f_notice); filter(f_not_authpriv); destination(messages); }; log { source(src); filter(f_kern); filter(f_debug); destination(messages); }; log { source(src); filter(f_lpr); filter(f_info); destination(messages); }; log { source(src); filter(f_mail); filter(f_crit); destination(messages); }; log { source(src); filter(f_news); filter(f_err); destination(messages); }; # # security.* /var/log/security # log { source(src); filter(f_security); destination(security); }; # # auth.info;authpriv.info /var/log/auth.log log { source(src); filter(f_auth); filter(f_info); destination(authlog); }; log { source(src); filter(f_authpriv); filter(f_info); destination(authlog); }; # # mail.info /var/log/maillog # log { source(src); filter(f_mail); filter(f_info); destination(maillog); }; # # lpr.info /var/log/lpd-errs # log { source(src); filter(f_lpr); filter(f_info); destination(lpd-errs); }; # # ftp.info /var/log/xferlog # log { source(src); filter(f_ftp); filter(f_info); destination(xferlog); }; # # cron.* /var/log/cron # log { source(src); filter(f_cron); destination(cron); }; # # *.=debug /var/log/debug.log # log { source(src); filter(f_is_debug); destination(debuglog); }; # # *.emerg * # log { source(src); filter(f_emerg); destination(allusers); }; # # airport logging # log { source(net); filter(f_airport); destination(airport); }; # # linksys logging # log { source(net); filter(f_linksys); destination(linksys); };