How to Install RSyslog+MySQL with LogAnalyzer on CentOS


Gathering information message is important on Data Center, in some situations you’ll want to store all entries of logfiles on another server. If a server crashes or gets hacked it will be able to trace through  logfiles from your machine. this is can be accomplished by using centralized log server that receive messages from another hosts.  A syslog facility can receive messages from Unix/Linux hosts but also network devices and windows hosts.

In this post, I want to explain step installation of Rsyslog, and Centralized log using MySQL Database. And using LogAnalyzer web interface, for graphical view and administrative.

Step Installation:

1. First we need to install the following packages:

# yum install rsyslog rsyslog-mysql mysql-server php-mysql php-gd httpd mod_ssl

2. Configure rsyslog, mysqld, and httpd to run on startup:

#  chkconfig --add rsyslog
#  chkconfig --add mysqld
#  chkconfig --add httpd
#  chkconfig rsyslog on
#  chkconfig httpd on
#  chkconfig mysqld on
#  service rsyslog start
#  service mysqld start
#  service httpd start

3.  Configure RSyslog with MySQL Database Connection

Assuming for example:
user: root
password: sql password
host: localhost
db to create: Rsyslogdb
RSyslog-mysql database installation path: /usr/share/doc/rsyslog-mysql-2.0.0/createDB.sql

Create database:
# mysql –u root –psqlpassword
mysql> CREATE DATABASE Rsyslogdb;

Export rsyslog database table:
# mysql –u root –psqlpassword Rsyslogdb < /usr/share/doc/rsyslog-mysql-2.0.0/createDB.sql

Setup MySQL permission (must be same with /etc/rsyslog.conf and /path/to/loganalyzer/config.php)
# mysql –u root –psqlpassword
mysql> GRANT ALL ON Rsyslogdb.* TO ‘root’@’localhost’ IDENTIFIED BY 'sqlpassword';

4. Configure RSyslog

edit rsyslog configuration file
vi /etc/rsyslog.conf

add this line below:
$ModLoad ommysql
$ModLoad imuxsock
$ModLoad imklog
$Modload imudp
$UDPServerRun 514
$Modload imtcp
$InputTCPServerRun 514
## Optional
$UDPServerAddress 0.0.0.0
## Optional
$RepeatedMsgReduction ()
$template dbFormat,"insert into SystemEvents (Message, Facility,FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values ('%msg%', %syslogfacility%, '%HOSTNAME%',%syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag%')",sql
*.*       : ommysql:localhost,Ryslog,root,sqlpassword
*.info;mail.none;authpriv.none;cron.none            /var/log/messages
authpriv.*                                          /var/log/secure
mail.*                                              /var/log/maillog
cron.*                                              /var/log/cron
*.emerg                                             *
uucp,news.crit                                      /var/log/spooler
local7.*                                             /var/log/boot.log

5. Restarting rsyslog service:
# service rsyslog restart

6. Centralized Syslog Server
Edit file:  /etc/sysconfig/rsyslog  (add this line)
# vi /etc/sysconfig/rsyslog
SYSLOGD_OPTIONS="-m 0 -r"

7. Log Analyzer Installation
Download the latest installation :
http://loganalyzer.adiscon.com/downloads

# cd /tmp
# wget http://download.adiscon.com/loganalyzer/loganalyzer-3.0.6.tar.gz
# tar -zxvf loganalyzer-3.0.6.tar.gz
# cd /tmp/loganalyzer-3.0.6/src/
# mkdir /var/www/html/Rsyslog
# cp –R * /var/www/html/Rsyslog
# cp loganalyzer-3.0.6/contrib/configure.sh /var/www/html/
# chmod +x configure.sh
# ./configure.sh

After you execute configuration.sh files, it will create an empty config.php that used for Log Analyzer Configuration.

Open your favorite web browser and navigate into your homepage (i.e: http://localhost), then fill in and follow the Log Analyzer configuration steps to complete. From here RSyslog can be displayed on Show Events tab, choose MySQL Native to load your syslog messages on this machine into your RSyslog database on MySQL.

Done!

Download This Guide in PDF

22 thoughts on “How to Install RSyslog+MySQL with LogAnalyzer on CentOS

  1. Dayanand December 22, 2011 at 6:54 PM Reply

    Hi ,
    This is very good document . i install all realted RPM but
    1) Can we start Log Ana. without My SQL
    2) after http://ipadress/Rsyslog it’s shows INDEX of Rsyslog folder .
    3) I can see logs from command Line but enable to view Log analazer page .
    my rsyslog.conf config as below
    i don’t know where i am making mistake
    [root@testlog ~]# grep -v “#” /etc/rsyslog.conf

    $ActionFileDefaultTemplate RSYSLOG_TraditionalFileFormat
    *.info;mail.none;authpriv.none;cron.none /var/log/messages
    authpriv.* /var/log/secure
    mail.* -/var/log/maillog
    cron.* /var/log/cron
    *.emerg *
    uucp,news.crit /var/log/spooler
    local7.* /var/log/boot.log
    $template DailyPerHostLogs, “/var/log/rsyslog/%HOSTNAME%/%HOSTNAME%.%$YEAR%-%$MONTH%-%$DAY%.log”
    $ModLoad imtcp
    $InputTCPServerRun 514
    *.* ?DailyPerHostLogs;
    $ModLoad imudp.so
    $UDPServerAddress *
    $UDPServerRun 514

    ————————————————————————-

    Reagrds ,
    daya

  2. gembuls December 23, 2011 at 10:32 AM Reply

    Hi Dayanand,

    1. Installing loganalyzer without mysql? I’m not sure about that. MySQL used to store syslog messages from your machine into database, and Loganalyzer is a web interface likes a GUI to display a syslog messages.

    2. Mmhhh.. you’re unable to view http://ipaddress/Rsyslog/ page? It seems there’s a problem on your php.ini configuration, what a web server are you use for? it means your web server cannot reads a php files from your doc root directory.

    3. This questions are related with question no.2. i think your configuration was correct.

    CMIIW

    arif

  3. fix nichols January 9, 2012 at 12:53 PM Reply

    yeah but if you allready have rsyslog configured and feeding syslogs into an existing db, can you just give loanalyzer the same db as rsyslog ???? or should be it a diff db???

    Guess im going to find out the hard way. Will post my findings when im done…

  4. gembuls January 10, 2012 at 4:09 PM Reply

    dude, you can use an existing db to store rsyslog from many machines. i’m using same db to store syslog from many machines, just give the privileges to the another host/machine in MySQL/etc.

  5. hedscratchers April 12, 2012 at 3:14 AM Reply

    I also added this to the createDB.sql, as your mysql syntax just wasn’t working for me.
    I also created a separate user for the rsyslogdb.

    I edited the createDB.sql to this:

    # Create user and password for rsyslogdb
    grant all privileges on rsyslogdb.* to rsyslog@”localhost” identified by ‘yourpassword’;
    USE rsyslogdb;
    (
    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
    );

    • hedscratchers April 12, 2012 at 3:51 AM Reply

      So it would go something like this:
      1. Create database:
      # mysql –u root –psqlpassword
      mysql> CREATE DATABASE rsyslogdb;

      2. Edit the createDB.sql file in /usr/share/doc/rsyslog-mysql-4.6.2/createDB.sql so that it creates user rsyslog, with a password and grants full access to the rsyslogdb:

      # Create user and password for rsyslogdb
      grant all privileges on rsyslogdb.* to rsyslog@”localhost” identified by ‘yourpassword’;
      USE rsyslogdb;
      (
      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
      );
      3. Export rsyslog database table:
      # mysql –u root –psqlpassword rsyslogdb drop database rsyslogdb;
      mysql> delete from mysql.user where User = ‘rsyslog’;
      mysql> delete from mysql.db where User = ‘rsyslog’;
      mysql> flush privileges

    • gembuls July 14, 2012 at 2:30 PM Reply

      please edit your MySQL database name and your MySQL password which you have created, do not copy paste a full syntax. because it may be different name.

  6. Mansoor Md July 10, 2012 at 3:21 PM Reply

    After i navigate to http://localhost/Rsyslog/ am getting the error as below
    “Error, main configuration file is missing!” Please advice

    • gembuls July 14, 2012 at 2:26 PM Reply

      did you have configure the config file?

  7. Chirag September 14, 2012 at 12:41 AM Reply

    Could you please tell me: What does the Number#6 exactly do and mean?

    • gembuls September 14, 2012 at 10:01 AM Reply

      No.6 means we are using syslogd for system logging daemon to collect messages from a number of program and collects information from the kernel logging daemon.

      So we used this option to instruct syslogd to receive logs from remote machines over networks connection.

  8. Andrew September 28, 2012 at 10:04 AM Reply

    I’ve got everything setup, but I can’t get it be viewed from the web browser. It seems to be Apache issue or a permissions issue. Can you assist?

    • gembuls September 28, 2012 at 10:48 AM Reply

      Hello,

      I think that was an Apache issue with your php configuration. can you give me phpinfo output?

  9. Vipin Venugopal December 8, 2012 at 3:07 AM Reply

    Hello,

    I’ve Started Loganalyzer Configuration via web browser but after step 4 of the configuration,unable to get the page for step5(it shows blank).Can anybody assist me?It’ll be a great help!!!

    • gembuls December 23, 2012 at 1:24 PM Reply

      it seems same problem with previous comments, thats issue from your php configuration.

  10. Andrew Meyer March 26, 2013 at 10:21 AM Reply

    I have this complete setup just like you do on CentOS 6.3. The only thing I changed was the database name from Rsyslogdb to rsyslogdb. Its still logging to my /var/log/ciscoasa. Should I remove this entry from the syslog file?

  11. Melvin June 8, 2013 at 10:38 PM Reply

    I have a problem in loganalyzer in the index.php “no syslog records found – Error Details:”
    No syslog records found
    Please help me…!!

  12. gembuls June 22, 2013 at 2:26 AM Reply

    there is 3 possibilities:
    1. wrong syslog source file location
    2. wrong syslog file
    3. wrong permission on syslog file

  13. techplesk October 7, 2013 at 1:22 PM Reply

    Could you please tell me how do I add switch and hosts so that I can monitor them. Thanks

    • gembuls October 20, 2013 at 9:30 AM Reply

      for switches: just send your syslog information to syslog server in your switch configuration (configuration depends on each switches)
      for linux host: just add your machine on syslog server (pointing directly to the syslog file)

  14. Melissa November 28, 2013 at 8:34 AM Reply

    HI, Someone can tell me how to customize php error_log to report to loganalyzer but they mus be higlight with the Seevruty Error or Warning. There are any way to do that?

    • Arif Romansyah January 28, 2016 at 9:45 AM Reply

      hi melissa, is that using your own ssl certificate?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: