Monday, January 14, 2013

How to make rsyslog to write syslogs in a database(PostgreSQL):


I am using rsyslog as it is more common in RHEL enviroments these days but I am sure you can find the equvalent  packages in other OS and distributions:

Install postgreSQL module for rsyslog:
 # yum install rsyslog-pgsql


In /etc/rsyslog.conf add following lines:

$ModLoad imuxsock # provides support for local system logging (e.g. via logger command)
$ModLoad imklog   # provides kernel logging support (previously done by rklogd)

# Provides UDP syslog reception
$ModLoad imudp
$UDPServerRun 514

# Provides TCP syslog reception
$ModLoad imtcp
$InputTCPServerRun 514


# Include all config files in /etc/rsyslog.d/
$IncludeConfig /etc/rsyslog.d/*.conf


Make the /etc/rsyslog.d/psql.conf file with the following contents:

$ModLoad ompgsql.so

$WorkDirectory /var/tmp/rsyslog/work

# This would queue _ALL_ rsyslog messages, i.e. slow them down to rate of DB ingest.
# Don't do that...
# $MainMsgQueueFileName mainq  # set file name, also enables disk mode

# We only want to queue for database writes.
$ActionQueueType LinkedList # use asynchronous processing
$ActionQueueFileName dbq    # set file name, also enables disk mode
$ActionResumeRetryCount -1   # infinite retries on insert failure

*.*             :ompgsql:127.0.0.1,syslog,rsyslog,secret;


The format is:

*.*           :ompgsql:<DB HOST>,<DB USERNAME>,<DB NAME>,<PASSWORD>;

Now, to config postgreSQL, do the following changes in postgresql config file:
In /var/lib/pgsql/data/postgresql.conf :

listen_addresses = 'localhost'
port = 5432
max_connections = 100

And following changes to /var/lib/pgsql/data/pg_hba.conf to grant the local accesses:

# "local" is for Unix domain socket connections only
#local   all         all                               ident sameuser
local    all         all                               trust
# IPv4 local connections:
#host    all         all         127.0.0.1/32          ident sameuser
host    all         all         127.0.0.1/32          trust
# IPv6 local connections:
#host    all         all         ::1/128               ident sameuser
host    all         all         ::1/128               trust


Now restart the postgreSQL server:

# service postgresql restart

Create the database:

#su - postgres
-bash-4.1$ createuser rsyslog;
Shall the new role be a superuser? (y/n) y
-bash-4.1$ createdb -T template0 -E SQL_ASCII syslog;


-bash-4.1$ psql -l
                                  List of databases
   Name    |  Owner   | Encoding  |  Collation  |    Ctype    |   Access privil
eges
-----------+----------+-----------+-------------+-------------+----------------
-------
 postgres  | postgres | UTF8      | en_US.UTF-8 | en_US.UTF-8 |
 syslog    | postgres | SQL_ASCII | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8      | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
(3 rows)

-bash-4.1$


 
 Now we show create the database schema.  The package has a file located at /usr/shade/doc//rsyslog-pgsql-5.8.10/createDB.sql which has the requiered schema. But I had to comment out the first line to make it work:

 -- CREATE DATABASE Syslog WITH ENCODING 'SQL_ASCII';
\c syslog;
CREATE TABLE SystemEvents
(
        ID serial not null primary key,
        CustomerID bigint,
        ReceivedAt timestamp without time zone NULL,
        DeviceReportedTime timestamp without time zone 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 serial not null primary key,
        SystemEventID int NULL ,
        ParamName varchar(255) NULL ,
        ParamValue text NULL
);


Use the following line to apply the table schema assuming you are already in the right path:


#psql -U rsyslog syslog -f ./createDB.sql
 
Reload the rsyslog service and check if there is any error in /var/log/messages:

# service rsyslog reload


Did I say how to set a password for the rsyslog user in postgreSQL ?

# su - postgres
-bash-4.1$ psql
psql (8.4.13)
Type "help" for help.

postgres=# Alter user rsyslog with password 'secret';
ALTER ROLE
postgres=# \q
-bash-4.1$



This should work. you can see the logs in systemevents table:

# psql -W -Ursyslog syslog
Password for user rsyslog:
psql (8.4.13)
Type "help" for help.

syslog=# select count(*) from systemevents;
 count
-------
  6596
(1 row)

syslog=#


6 comments:

Marnix said...

Thanks! You helped me get a lot closer to having it working. I've spent the last 2 days trying to get rsyslog to log to a postgresql database in my Xubuntu 12.04.2 system. Despite having installed package rsyslog-pgsql, rsyslog doesn't seem to be talking to postgresql. The file /etc/rsyslog.d/pgsql.conf looks like it should take care of what I need, but maybe not. I wasn't able to find that createDB.sql file anywhere so cut and pasted from your article. That worked. I hope I can have this figured out and working within another day. Thanks for your article!

A Shenavandeh said...

Please be careful of varchar(60)s. Sometime systems try to import bigger values which fails the the PGSQL connections altogether which is something weird.

Rob van Halteren said...

I had to omit the ; at the end from
*.* :ompgsql:127.0.0.1,syslog,rsyslog,secret;

to get it to work

Antorg said...
This comment has been removed by the author.
Antorg said...
This comment has been removed by the author.
Antorg said...

*.*:ompgsql:DB HOST,DB NAME,DB USERNAME,PASSWORD;