Centralised Logging Draft
This guide will help you setup Centralised logging in a mixed server environment.
Draft: some sections have not been tested and spelling/grammar has not been checked
Step 1 - Setup Logging Server
First thing is to install Syslog-ng, on Syslog-ng isn't part of the centos 6.x base repos so you will have to add EPEL
| System A - 192.168.0.1 | |
| Make sure you have EPEL installed | yum install epel-release |
| Use yum to install syslog-ng | yum install syslog-ng syslog-ng-libdbi |
| Make sure it starts when the server is restarted. | chkconfig syslog-ng on |
| Add an IP tables rule to allow traffic. Warning this will allow anyone to send syslog data to this server. Consider adding additional restrictions particularly if the server is exposed to the Internet. | iptables -I INPUT 1 -p UDP --dport=514 -j ACCEPT |
| Save IPTables so you don't lose the rule on reboot with | service iptables save |
| Enable remote messages within syslog-ng | vi /etc/syslog-ng/syslog-ng.conf |
| Uncomment the line starting with UDP in the source s_sys section update the port if you are planning on using a non-standard port. Warning This guide is showing how to setup the system using UDP - messages will be transfered in both plain text and also will not be guaranteed delivery. |
/etc/syslog-ng/syslog-ng.conf - Original ...
source s_sys {
file ("/proc/kmsg" program_override("kernel: "));
unix-stream ("/dev/log");
internal();
#udp(ip(0.0.0.0) port(514));
};
...
/etc/syslog-ng/syslog-ng.conf - Changes ...
source s_sys {
file ("/proc/kmsg" program_override("kernel: "));
unix-stream ("/dev/log");
internal(); udp(ip(0.0.0.0) port(514)); };
... |
| Start the syslog-ng service | service syslog-ng start |
Step 2 - Setup Mysql Database
At this point you have a server that will accept syslog messages from other systems.However, All messages will be logged to /var/log/messagess
To make it easier to query we will be setting up a mysql database where log records will be stored
This guide assumes you already have mysql installed - if you don't simply run yum install mysql mysql-server
| System A - 192.168.0.1 | |
| Optional Download the basescript or copy the script below | wget http://git.io/ZEN-syslog-initdb.sql ~/syslog-initdb.sql |
| The following sql script will initialise the database. | vi ~/syslog-initdb.sql |
| Warning the database user created will have access to all tables in the database - if you have other tables in the database you should alter the grant line. | ~/syslog-initdb.sql - New File CREATE DATABASE logdb;
CREATE USER logger;
SET PASSWORD FOR logger@localhost = PASSWORD('password');
GRANT INSERT,DELETE ON logdb.* TO logger@localhost;
USE `logdb`;
|
| The fields in this table need to match the definition in /etc/syslog-ng/syslog-ng.conf (see below) | CREATE TABLE `log` ( `host` varchar(32) NOT NULL, `facility` varchar(10) DEFAULT NULL, `priority` varchar(10) DEFAULT NULL, `level` varchar(10) DEFAULT NULL, `tag` varchar(10) DEFAULT NULL, `datetime` datetime DEFAULT NULL, `program` varchar(20) DEFAULT NULL, `msg` text, `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `eventid` int(11) DEFAULT NULL, PRIMARY KEY (`seq`), KEY `host` (`host`), KEY `program` (`program`), KEY `datetime` (`datetime`), KEY `priority` (`priority`), KEY `facility` (`facility`), KEY `eventid` (`eventid`) ); |
| This table stores (windows) login information - this only works if using the Zensquare custom evtsys. | CREATE TABLE `auth_log` ( `guid` varchar(36) NOT NULL, `username` varchar(45) NOT NULL, `hostname` varchar(45) DEFAULT NULL, `address` varchar(32) DEFAULT NULL, `ts_first` datetime DEFAULT NULL, `ts_last` datetime DEFAULT NULL, `auth_server` varchar(16) DEFAULT NULL, PRIMARY KEY (`guid`,`username`) ); |
| This trigger populates the eventid field in the log table and populates the auth_log table. | DELIMITER $$
CREATE TRIGGER `log_process_event` BEFORE INSERT ON `log` FOR EACH ROW
BEGIN
IF NEW.program = "Security-Auditing" AND NEW.msg LIKE "4624: AUDIT_SUCCESS %" THEN
SET NEW.msg = SUBSTRING(NEW.msg, 20);
SET @pointer = LOCATE(":",NEW.msg);
SET @guid = LEFT(NEW.msg, @pointer-1);
SET NEW.msg = SUBSTRING(NEW.msg, @pointer+1);
SET @pointer = LOCATE(":",NEW.msg);
SET @username = LEFT(NEW.msg, @pointer-1);
SET NEW.msg = SUBSTRING(NEW.msg, @pointer+1);
SET @pointer = LOCATE(":",NEW.msg);
SET @hostname = LEFT(NEW.msg, @pointer-1);
SET NEW.msg = SUBSTRING(NEW.msg, @pointer+1);
SET @address = NEW.msg;
INSERT INTO auth_log
(guid, username, hostname, address, ts_first, ts_last, auth_server)
VALUE
(@guid, @username, @hostname, @address, NEW.`datetime`, NEW.`datetime`, NEW.host)
ON DUPLICATE KEY UPDATE `ts_last` = NEW.`datetime`;
SET NEW.msg = "";
ELSE
SET @pointer = LOCATE(":",NEW.msg);
IF @pointer < 8 AND @pointer > 0 THEN
SET @eventID = LEFT(NEW.msg, @pointer-1);
IF @eventID REGEXP '^[0-9]+$' THEN
SET NEW.eventID = @eventID;
SET NEW.msg = SUBSTRING(NEW.msg, @pointer+1);
END IF;
END IF;
END IF;
END$$
DELIMITER ; |
| Run the SQL script | mysql < ~/syslog-initdb.sql |
| Enable database logging within syslog-ng.conf | vi /etc/syslog-ng/syslog-ng.conf |
| The following changes will setup syslog-ng to log to the database/table that was just created. | /etc/syslog-ng/syslog-ng.conf - Original ...
destination d_mlal { usertty("*"); };
filter f_kernel { facility(kern); };
};
...
log { source(s_sys); filter(f_cron); destination(d_cron); };
# vim:ft=syslog-ng:ai:si:ts=4:sw=4:et:
/etc/syslog-ng/syslog-ng.conf - Changes ...
destination d_mlal { usertty("*"); };
destination d_mysql {
sql(
type(mysql)
username("logdb")
password("password")
database("logdb")
host("localhost")
table("log")
columns("host", "facility", "priority", "level", "tag", "datetime", "program", "msg")
values("$HOST", "$FACILITY", "$PRIORITY", "$LEVEL", "$TAG","$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC","$PROGRAM", "$MSG")
indexes()
);
};
filter f_no_debug { not level(debug); }; filter f_kernel { facility(kern); };
};
...
log { source(s_sys); filter(f_cron); destination(d_cron); };
log {source(s_sys); filter(f_no_debug); destination(d_mysql); };
# vim:ft=syslog-ng:ai:si:ts=4:sw=4:et: |
| Restart the syslog-ng service for the changes to take affect | service syslog-ng restart |
Step 3 - Configure Servers
You now have a server setup waiting for log messages and ready to record them to a mysql database.
The next task is to setup logging on the servers you want to monitor. This guide will cover setting up logging on another Centos Server and also a windows server.
Logging from linux (Centos 6.x)
| System X - 192.168.0.X | |
| Make sure you have EPEL installed | yum install epel-release |
| Use yum to install syslog-ng | yum install syslog-ng syslog-ng-libdbi |
| Enable remote messages within syslog-ng | vi /etc/syslog-ng/syslog-ng.conf |
| Uncomment the line starting with UDP in the source s_sys section update the port if you are planning on using a non-standard port |
/etc/syslog-ng/syslog-ng.conf - Original ...
log { source(s_sys); filter(f_cron); destination(d_cron); };
# vim:ft=syslog-ng:ai:si:ts=4:sw=4:et:
...
/etc/syslog-ng/syslog-ng.conf - Changes ...
log { source(s_sys); filter(f_cron); destination(d_cron); };
destination d_central { udp("address" port(514));};
log { source(s_sys); filter(f_default); destination(d_central); };
# vim:ft=syslog-ng:ai:si:ts=4:sw=4:et:
... |
Logging from windows (Tested on server 2008, 2012 and windows 7)
- Download the evtsys installer
- Run it
- Enter the server address/port
- Finish the install
- Optional You can modify which events to capture in the evtsys.cfg file (in the install directory)
Step 4 - Testing
Back on System A should now have messages coming in from all systems - you can view them live by running tail -f /var/log/messages or you can query the mysql database
Step 5 - Clean Up
We now have a number of servers logging to a central server - however there are no log retension policies in place - the mysql database is going to hold event records forever.
Copy the below script into a file vi /etc/cron.daily/syslog_mysql_cleanup.sh, make it executable with chmod +x /etc/cron.daily/syslog_mysql_cleanup.sh.
/etc/cron.daily/syslog_mysql_cleanup.sh
| #!/bin/sh |
| echo "DELETE FROM \`log\` WHERE (\`level\` IN ('info','debug','notice') AND DATE_ADD(now(), INTERVAL -7 DAYS) < \`datetime\` ) || DATE_ADD(now(), INTERVAL 3 MONTHS) > \`datetime\` || `msg` = ""; DELETE FROM \`auth_log\` WHERE DATE_SUB(now(), INTERVAL 6 MONTHS) > \`ts_last\`" \ | mysql --user=logger --password=password > "/var/log/syslog-mysql-cleanup.log" mysql --user= --password= >> /var/log/syslog-mysql-cleanup.log 2>& |