k3s.live

Based on the IT journey of Michael Rickert

rsyslog and loganalyzer

A few days ago I played around with rsyslog and loganalyzer in centOS and found this great guide on the topic by Tiago Marques  http://en.tiagomarques.info/2011/07/centos-syslog-server-rsyslog-mysql-and-loganalyzer/

His guide worked well enough, however I was having some mysql database issues with rsyslog where the syslog messages would be recieved by rsyslog, but they would not be passed along to the mysql server. So that if I entered tail /var/log/messages I could see the syslog messages arriving, but they weren’t in the mysql table.

I was able to get around this issue by changing the inserted line in /etc/rsyslog.conf from:

*.* >127.0.0.1,rsyslogdb,rsyslog,password 

to

*.*    :ommysql:127.0.0.1,rsyslogdb,rsyslog,password

After making the change, I also found that I had to create new tables for the servers I was already logging and increment their templates, like so:

mysql commands:

mysql -u root -p

use rsyslogdb;

CREATE TABLE ap2 LIKE rsyslogdb.ap1;

/etc/rsyslog.conf edits:

$template ap1tmpl,"insert into ap1 (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values ('%msg%', %syslogfacility%, '%HOSTNAME%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag%')",SQL

Became:

$template ap2tmp2,"insert into ap2 (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values ('%msg%', %syslogfacility%, '%HOSTNAME%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag%')",SQL

And of course i matched it in the end of the if command:

if ($source == '10.0.0.30') then :ommysql:127.0.0.1,rsyslogdb,rsyslog,passwordgoeshere;ap2tmp2

Finally, I edited my ‘sources’ in  loganalyzer and was then able to successfully view syslog messages properly over the web interface.

Leave a Reply