[HowTo]Put syslogs to MySQL database and analyse with PHPLogCon

Urfaust · 2009-12-14 19:24:52

Hi!

I thought some might enjoy this.:D
This is a simple 'how to' for putting syslog events into a MySQL database and analyse the output with PHPLogCon.
This is a very short description and some conditions have to be fulfilled. Please refer to "Resources" at the end of this posting for further links and documentation.

Things you need:
- LAMP - Apache, PHP and MySQL up and running
- rsyslog as system logger

Now we have 3 things to do:
1) create a MySQL database for the logging data
2) tell rsyslog to write its output to this database
3) install PHPLogCon

1)
To create a proper database scheme there is a file called createDB.sql in the rsyslog package.

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
);

You can execute the file with:

mysql -u root -p < /Path/to/file/createDB.sql

After that I created a user "loguser" to access that database:

Enter mysql client:

mysql -u root -p

Grant rights to user "loguser" with password "password".

grant ALL ON Syslog.* to loguser@localhost identified by — 'password'; flush privileges;

2)
Now we have to tell rsyslog to write its output into that database.

Start your favorite texteditor and open /etc/rsyslog.conf.
Search for the "load input modules" section at the very beginning and add the following lines:

$ModLoad ommysql
*.* :ommysql:localhost,Syslog,loguser,password

The first line loads the module for logging into MySQL databases and the second one tells rsyslog what to log under which user into which database.

Restart rsyslogd:

# /etc/rc.d/rsyslogd restart

3)
Install PHPLogCon.

Goto http://www.phplogcon.org/downloads and download the latest release.

Untar with:

tar xzf NameOfDownloadedFile

This creates a subdir phpLogCon-Versionnumber. Now you have to upload all files under /phpLogCon-Versionnumber/src/ into your webservers DocumentRoot (I created a folder "phplogcon" and copied everything in there).
Open your webbrowser with http://localhost/phplogcon. You receive an error and a link to the installation process. First some permissions are checked etc...the important step is where you can choose the source of your syslog messages.
Mainly, you have to choose "Name of the Source" and "Source Type". The name will be displayed later in a drop-down menu with which you choose your active syslog source. The "Source Type" is MySQL in our case with the follwing informations:

Table Type = monitorware (this is default and corresponds to the database scheme we created earlier)
Database Host = localhost
Database Name = Syslog (see createDB.sql from step 1)
Database Tablename = SystemEvents (see createDB.sql from step 1)
Database User = loguser
Database Password = password

And your done.
Now you have a tiny little web gui for yor system messages.:) Here is a demo site: http://demo.phplogcon.org/

Resources:
http://wiki.archlinux.org/index.php/LAMP
http://wiki.archlinux.org/index.php/Rsyslog
http://www.phplogcon.org
http://www.phplogcon.org/module-Static_ … html.phtml
http://www.rsyslog.com/
http://www.rsyslog.com/doc-rsyslog_mysql.html

Last edited by Urfaust (2009-12-14 22:04:24)

fukawi2 · 2009-12-14 21:48:33

This should go in the wiki

Arch Linux

#1 2009-12-14 19:24:52

[HowTo]Put syslogs to MySQL database and analyse with PHPLogCon

#2 2009-12-14 21:48:33

Re: [HowTo]Put syslogs to MySQL database and analyse with PHPLogCon

Board footer