rsyslog + MySQL + LogAnalyzer on OpenSUSE
1. Install requirements
|
1 |
zypper install rsyslog rsyslog-module-relp rsyslog-module-mysql rsyslog-module-snmp rsyslog-module-mmnormalize |
2. Create RuleBase for PHP log
|
1 2 3 4 5 |
vi /etc/rsyslog.d/phplog.rulebase # PHP Logs rule=: %server:char-to:\x3a%\x3a PHP %priority:char-to:\x3a%\x3a%message:rest% rule=: PHP %priority:char-to:\x3a%\x3a%message:rest% |
3. Prepare MySQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
CREATE DATABASE `syslog`; USE `syslog`; CREATE USER 'syslog'@'localhost' IDENTIFIED BY 'my_password'; GRANT EXECUTE, INSERT, LOCK TABLES, SELECT, SHOW VIEW, UPDATE ON syslog.* TO 'syslog'@'localhost'; FLUSH PRIVILEGES; CREATE TABLE `SystemEvents` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `CustomerID` bigint(20) DEFAULT NULL, `ReceivedAt` datetime DEFAULT NULL, `DeviceReportedTime` datetime DEFAULT NULL, `Facility` smallint(6) DEFAULT NULL, `Priority` smallint(6) DEFAULT NULL, `FromHost` varchar(60) DEFAULT NULL, `Message` text, `NTSeverity` int(11) DEFAULT NULL, `Importance` int(11) DEFAULT NULL, `EventSource` varchar(60) DEFAULT NULL, `EventUser` varchar(60) DEFAULT NULL, `EventCategory` int(11) DEFAULT NULL, `EventID` int(11) DEFAULT NULL, `EventBinaryData` text, `MaxAvailable` int(11) DEFAULT NULL, `CurrUsage` int(11) DEFAULT NULL, `MinUsage` int(11) DEFAULT NULL, `MaxUsage` int(11) DEFAULT NULL, `InfoUnitID` int(11) DEFAULT NULL, `SysLogTag` varchar(60) DEFAULT NULL, `ProcessID` varchar(60) DEFAULT NULL, `EventLogType` varchar(60) DEFAULT NULL, `GenericFileName` varchar(60) DEFAULT NULL, `SystemID` int(11) DEFAULT NULL, `Checksum` int(11) DEFAULT NULL, PRIMARY KEY (`ID`), KEY `ie1_SystemEvents` (`DeviceReportedTime`), KEY `ie2_SystemEvents` (`FromHost`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; DELIMITER $$ ALTER DEFINER=`root`@`%` EVENT `evt_purge_old_events` ON SCHEDULE EVERY 1 DAY STARTS '2010-01-01 06:00:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN DELETE FROM syslog.SystemEvents WHERE DeviceReportedTime < SUBDATE(CURRENT_TIMESTAMP, INTERVAL 3 MONTH); END$$ DELIMITER; |
4. rsyslog Configuration for Log-Servers
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 |
###################### # Aiden (server) ###################### module(load="mmnormalize") module(load="imrelp") module(load="imtcp" keepalive="on") module(load="imudp") module(load="ommysql") input(type="imrelp" port="2514" keepalive="on" ruleset="remote") input(type="imtcp" port="1514" ruleset="remote") input(type="imudp" port="514" ruleset="remote") $AllowedSender TCP, 127.0.0.1, 10.0.1.0/24, 192.168.1.0/24 $AllowedSender UDP, 127.0.0.1, 10.0.1.0/24, 192.168.1.0/24 template(name="aidenSQLformat" type="string" option.sql="on" string="insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag, ProcessID) values (TRIM('%msg%'), %syslogfacility%, '%hostname%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag:R,ERE,1,FIELD:([^\\[]+)(\\[[0-9]{1,5}\\])*:--end%', '%syslogtag:R,ERE,1,BLANK:\\[([0-9]{1,5})\\]--end%')") template(name="aidenSQL4PHPformat" type="string" option.sql="on" string="insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag, ProcessID) values ('%$!msg%', %$!facility%, '%hostname%', %$!syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%$!syslogtag%', '%syslogtag:R,ERE,1,BLANK:\\[([0-9]{1,5})\\]--end%')") ruleset(name="php2db") { if not ($syslogtag contains_i 'suhosin') then { action(type="mmnormalize" rulebase="/etc/rsyslog.d/phplog.rulebase") if ($parsesuccess == 'OK') then { set $!syslogtag = 'php'; set $!facility = 3; set $!msg = 'PHP ' & $!priority & ':' & $!message; if ($!priority contains_i 'error') then { set $!syslogpriority = 3; action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQL4PHPformat" serverport="3306") }else if ($!priority contains_i 'warning') then { set $!syslogpriority = 4; action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQL4PHPformat" serverport="3306") }else if ($!priority contains_i 'notice') then { set $!syslogpriority = 5; action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQL4PHPformat" serverport="3306") } } } if ($fromhost-ip == '127.0.0.1') then action(type="omfile" file="/var/log/php.log") } ruleset(name="remote") { if ($syslogtag contains_i 'php' or $syslogtag contains_i 'httpd2-prefork' or $syslogtag contains_i 'httpd-prefork' or \ $syslogtag contains_i 'ool' or $syslogtag contains_i 'suhosin') then { call php2db }else{ action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQLformat" serverport="3306") } if ($fromhost-ip != '127.0.0.1') then action(type="omfile" file="/var/log/remote") stop } #-------------- if ($syslogpriority <= 4 and ($msg contains_i 'kernel does not support IPv6' or $msg contains_i 'Failed to parse address value')) then { # bug -/var/log/messages stop } if ($syslogpriority <= 4 and ($msg contains_i 'Failed to open private bus connection' or $msg contains_i 'protocol dhcp covered by a ipv4')) then { # bug -/var/log/messages stop } if ($syslogpriority <= 4 and ($msg contains_i 'Could not load host key' or $msg contains_i 'audit_printk_skb')) then { # bug -/var/log/auth.log stop } if ($syslogtag contains_i 'php' or $syslogtag contains_i 'httpd2-prefork' or $syslogtag contains_i 'httpd-prefork' or \ $syslogtag contains_i 'ool' or $syslogtag contains_i 'suhosin') then { call php2db stop } *.=warning;*.=err;*.=crit action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQLformat" serverport="3306") if ($syslogpriority == 5 and ($msg contains_i 'error' or $msg contains_i 'exception' or $msg contains_i 'warning')) then \ action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQLformat" serverport="3306") if ($syslogtag startswith 'kernel' and $msg contains_i 'audit:') then { -/var/log/auth.log stop } auth,authpriv.* -/var/log/auth.log & stop cron.* -/var/log/cron.log & stop if ($msg contains_i 'pam_unix(crond:session)') then { -/var/log/cron.log stop } local5.* -/var/log/sftpd.log & stop if ($syslogtag contains_i 'internal-sftp') then { -/var/log/sftpd.log stop } if ($syslogtag contains_i 'tomcat') then { -/var/log/tomcat.log stop } ###################### |
5. rsyslog Configuration for Log-Client
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
###################### # Aiden (client) ###################### module(load="mmnormalize") module(load="omrelp") ruleset(name="php2remote") { if not ($syslogtag contains_i 'suhosin') then { action(type="mmnormalize" rulebase="/etc/rsyslog.d/phplog.rulebase") if ($parsesuccess == 'OK') then { if ($!priority contains_i 'error' or $!priority contains_i 'warning' or $!priority contains_i 'notice') then { action(type="omrelp" target="logserver_ip_or_name" port="2514") } } } action(type="omfile" file="/var/log/php.log") } #-------------- if ($syslogpriority <= 4 and ($msg contains_i 'kernel does not support IPv6' or $msg contains_i 'Failed to parse address value')) then { # bug -/var/log/messages stop } if ($syslogpriority <= 4 and ($msg contains_i 'Failed to open private bus connection' or $msg contains_i 'protocol dhcp covered by a ipv4')) then { # bug -/var/log/messages stop } if ($syslogpriority <= 4 and ($msg contains_i 'Could not load host key' or $msg contains_i 'audit_printk_skb')) then { # bug -/var/log/auth.log stop } if ($syslogtag contains_i 'php' or $syslogtag contains_i 'httpd2-prefork' or $syslogtag contains_i 'httpd-prefork' or \ $syslogtag contains_i 'ool' or $syslogtag contains_i 'suhosin') then { call php2remote stop } *.=warning;*.=err;*.=crit action(type="omrelp" target="logserver_ip_or_name" port="2514") if ($syslogpriority == 5 and ($msg contains_i 'error' or $msg contains_i 'exception' or $msg contains_i 'warning')) then \ action(type="omrelp" target="logserver_ip_or_name" port="2514") if ($syslogtag startswith 'kernel' and $msg contains_i 'audit:') then { -/var/log/auth.log stop } auth,authpriv.* -/var/log/auth.log & stop cron.* -/var/log/cron.log & stop if ($msg contains_i 'pam_unix(crond:session)') then { -/var/log/cron.log stop } local5.* -/var/log/sftpd.log & stop if ($syslogtag contains_i 'internal-sftp') then { -/var/log/sftpd.log stop } if ($syslogtag contains_i 'tomcat') then { -/var/log/tomcat.log stop } ###################### |
6. Install LogAnalyzer
|
1 2 3 4 5 |
cd /usr/local/src wget http://download.adiscon.com/loganalyzer/loganalyzer-4.1.3.tar.gz tar -zxvf loganalyzer-4.1.3.tar.gz mkdir -p /srv/www/htdocs/loganalyzer cp /usr/local/src/loganalyzer-4.1.3/src/* /srv/www/htdocs/loganalyzer/ |
7. LogAnalyzer Configuration
♦ References
http://www.the-art-of-web.com/system/rsyslog-config/
http://www.liblognorm.com/files/manual/configuration.html
