MySQL/Syslog<title> </head> <body> <h1>Setup rsyslog to store syslog messages in MySQL</h1> <h2>Install mysql, rsyslog, etc.</h2> <code>yum install mysql-server rsyslog rsyslog-mysql</code> <p>If you are interested in storing syslog messages in MySQL so they could be viewed through a web page, using rsyslog in combination with phpLogCon is a good solution. This will also require <tt>php-mysql</tt>, <tt>php-gd</tt> and <tt>httpd</tt>.</p> <p>If you have a different syslog application running then you'll need to stop and disable it.</p> <code># service syslog stop<br /># chkconfig -level 0123456 syslog off<br /># cp /etc/syslog.conf /etc/rsyslog.conf<br /># service rsyslog start</code> <h2>Prepare database and user privilege on MySQL Server for rsyslog</h2> <p>On some linux distros you'll have to create the Syslog database with the following. On others, it is created when the <tt>rsyslog-mysql</tt> package is installed.</p> <code># service mysqld start # mysql mysql> GRANT SELECT, UPDATE, INSERT ON Syslog.* TO rsyslog@localhost IDENTIFIED BY '<i>secret</i>'; mysql> \q # mysql < /usr/share/doc/rsyslog-mysql-*/createDB.sql</code> <pre> CREATE DATABASE Syslog; USE Syslog; CREATE TABLE SystemEvents ( ID int unsigned not null auto_increment primary key, CustomerID bigint, ReceivedAt datetime NULL, DeviceReportedTime datetime NULL, Facility smallint NULL, Priority smallint NULL, FromHost varchar(60) NULL, Message text, NTSeverity int NULL, Importance int NULL, EventSource varchar(60), EventUser varchar(60) NULL, EventCategory int NULL, EventID int NULL, EventBinaryData text NULL, MaxAvailable int NULL, CurrUsage int NULL, MinUsage int NULL, MaxUsage int NULL, InfoUnitID int NULL , SysLogTag varchar(60), EventLogType varchar(60), GenericFileName VarChar(60), SystemID int NULL ); CREATE TABLE SystemEventsProperties ( ID int unsigned not null auto_increment primary key, SystemEventID int NULL , ParamName varchar(255) NULL , ParamValue text NULL ); </pre> After the above steps have been done, you should found a new database named "Syslog" has been created (this is sometimes referred to as "Adiscon's MonitorWare Schema"). <code># mysql<br />mysql> show databases;</code> <p><b>Note:</b> LogAnalyzer can be modified to support different database structures. My notes on the modifications required to support our syslog-ng tables are <a href="mysql_loganalyzer.html">here</a>.</p> <h2>Enable the mysql module of rsyslog</h2> <p>add the following to <tt>/etc/rsyslog.conf</tt> and then restart <tt>rsyslog</tt>: <pre> $ModLoad ommysql.so *.* :ommysql:localhost,Syslog,rsyslog,<i>secret</i> </pre> <p>Check <tt>/var/log/messages</tt> to confirm rsyslog started up and that there isn't any errors. You can also log into MySQL and take a look at the <tt>SystemEvents</tt> table to make sure data is being logged there. If you are using CentOS or Red Hat Enterprise Linux with SELinux enforcing mode, you will need to update the SELinux rules to allow rsyslog to talk to the MySQL socket:</p> <code># setenforce 0 # service rsyslog restart # cat /var/log/audit/audit.log | grep rsyslogd | audit2allow -M myselinuxmod; semodule -i myselinuxmod.pp # setenforce 1 # service rsyslog restart</code> <h2>Install LogAnalyzer</h2>  <p>Having the logs in MySQL is fine, but in order to make use of them and view them easily, LogAnalyzer (formerly phpLogCon) could be installed. It is a web-based front-end that will allow you to view the logs with a nice interface. The latest version can be downloaded from <a href="http://loganalyzer.adiscon.com/downloads">loganalyzer.adiscon.com/downloads</a>. The tarball comes with an <tt>INSTALL</tt> file with the instructions on how to set it up; it's no more difficult than any other PHP web application. You can also install <tt>phplogcon</tt> with the <tt>yum</tt> command:</p> <code># yum install phplogcon<br /># service httpd restart</code> <p>By default, the phplogcon config file is in <tt>/etc/httpd/conf.d/phplogcon.conf</tt></p> <p>By default, the configuration of phplogcon allows localhost access only. You have to made some changes to the <tt>phplogcon.conf</tt> to allow the phplogcon site can be access on other machines. For example, if your server's IP is 192.168.75.134 and you have a intranet with subnet 192.168.75.0/24 and you would like to allow all machines within this subnet are able to access phplogcon, change "<tt>Allow from 127.0.0.1 localhost</tt>" to "<tt>Allow from 192.168.75.</tt>" in <tt>etc/httpd/conf.d/phplogcon.conf</tt> and then restart apache.</p> <p>You can configure phpLogCon by opening a browser to <a href="http://192.168.75.134/loganalyzer/install.php">http://192.168.75.134/loganalyzer/install.php</a> and following the steps prompted. During the processes of configuration, it would check the file accessiblity of phplogconf. Under the SELinux enabled environment, the file <tt>config.php</tt> would be writeable by webserver by default. You have to solve this problem by invoke the follow command to change the SELinux context of the file:</p> <code># chcon -t httpd_sys_content_rw_t /etc/phplogcon/config.php</code> <p>Once it is installed, you will be able to see the web interface at <a href="http://192.168.75.134/loganalyzer/index.php">http://192.168.75.134/loganalyzer/index.php</a></p> <p><b>Notes:</b> Database settings are stored in <tt>config.php</tt> and table schema is defined in <tt>include/constants_logstream.php</tt> in the <tt>loganalyzer</tt> web root. Turning on debug info inside <tt>config.php</tt> may help you when configuring a new table schema. Additional notes are <a href="mysql_loganalyzer.html">here</a>.</p> <h2>Allow Rsyslog to listen log messages from remote systems</h2> <p>If you would like your rsyslog be able to listen logs from remote systems, <p>for rsyslog version 2, you will need to edit <tt>/etc/sysconfig/rsyslog</tt> and change the <b>SYSLOGD_OPTIONS</b> to "<tt>-m 0 -r</tt>"</p> <p>for rsyslog version 3 or newer, you have to edit /etc/rsyslog.conf and uncomment the following lines under <b>MODULES</b>:</p> <pre> $ModLoad imudp.so $UDPServerRun 514 </pre> <p>After configuration has been changed, you have to restart the rsyslog by<p> <code># service rsyslog restart</code> <p>You should found your machine is now listen to UDP port 514 for accept log messages from remote systems</p> <code># netstat -ntul | grep 514 udp 0 0 0.0.0.0:514 0.0.0.0:* udp 0 0 :::514 :::*</code> <p>However, you have to edit iptables rules to allow incoming remote log messages traffic by adding the following line at the top of the <b>INPUT CHAIN</b> of your IP tables: <code># cd /etc/sysconfig/<br /># vi iptables</code> <p>then ass "<tt>-A INPUT -m state -state NEW -m udp -p udp -dport 514 -j ACCEPT</tt>" to the top of <b>INPUT</b> chain</p> <p>save the file and restart iptables by</p><code># service iptables restart</code> <h2>Notes</h2> Resetting the mysql password: <code># service mysql stop # mysqld_safe --skip-grant-tables # mysql -u root mysql> use mysql; mysql> update user set password=PASSWORD("secret") where User='root'; mysql> flush privileges; mysql> quit # service mysql restart # mysql -u root -psecret mysql> </code> Retrieve list of mysql users: <code># mysql -u root -psecret mysql> select * from mysql.user; mysql> select User from mysql.user; mysql> \q</code> <ul> <li><a href="http://dev.mysql.com/tech-resources/articles/mysql_5.1_partitions.html">dev.mysql.com</a> - MySQL 5.1 New Features: MySQL Partitions</li> <li><a href="http://dev.mysql.com/tech-resources/articles/partitioning-event_scheduler.html">dev.mysql.com</a> - Using Partitioning and Event Scheduler to Prune Archive Tables</li> </ul> Also see: <ul> <li><a href="http://www.aboutmyip.com/AboutMyXApp/SyslogJunction.jsp">aboutmyip.com</a> - Syslog Junction</li> <li><a href="http://www.linkdown.org/en/syslogngbrowser.html">linkdown.org</a> - Syslog-ng browser</li> <li><a href="http://www.logzilla.info/">logzilla.info</a> (formerly <a href="http://wiki.rsyslog.com/index.php/Php-syslog-ng">php-syslog-ng</a>) [<a href="http://nms.gdd.net/index.php/Install_Guide_for_LogZilla_v3.x">installation</a>]</li> <li><a href="mysql_loganalyzer.html">Modify LogAnalyzer 3.0.4 to use a different MySQL table structure</a></li> </ul> </body></html>