Logging syslog and database audit messages to an oracle database with rsyslog

A few months ago i tested yet another syslog implementation: rsyslog. Among all other available syslog implementation such as syslog or syslog-ng rsyslog offers some nice features such as:

• database support
  • MySQL
  • Postgres
  • Oracle
• Failover log destinations
• syslog over tcp
• fine grain output format control
• high precision timestamps
• filer on any message part
• compatible with syslog
• encryption support

So i took the oracle module for rsyslog and tried to get it working. A documentation did not relly exists so i wrote one which is part of the module now. Recently i took a look at rsyslogd again and did some test with rsyslog – especially how to store syslog messages and log oracle audit messages in a queryable way in an oracle database. My experiences will be covered in this article.

Disclaimer: All scripts, packages, procedures are released under the GPL. You can use them freely but on your own risk. However i would like you to send me the changes you made so i can perhaps improve the components.

The environment

We will use our existing Oracle 11g Release 2 RAC environment on VMWARE. In the first part of the article we will compile, install and start rsyslog for the first time. In the second part we will improve the data model and add some log rewrite rules.

Install rsyslogd

rsyslogd with enabled oracle plugin does not ship with any distribution. So you have to download and compile it yourself.

Download

rsyslogd can be downloaded from the project homepage. In order to enable oracle database support you need in addition the oracle instantclient package in version 10.2.0.4.0 which can be downloaded here. You need to download two RPMs matching your platform and architecture:

• oracle-instantclient-basic-10.2.0.4-1.<architecture>.rpm
• oracle-instantclient-devel-10.2.0.4-1.<architecture>.rpm

Compiling the package requires a script to be placed in /usr/local/bin which can be downloaded from here. You have to adjust INCDIRS and LIBDIRS to suit your environment.

Compile

Call the configure script with the following arguments:

./configure --enable-oracle

The output should be look like this:

---{ database support }---
 MySql support enabled:                    no
 libdbi support enabled:                   no
 PostgreSQL support enabled:               no
 Oracle (OCI) support enabled:             yes

Compile it with:

make

Install it with:

make install

Thats it.

Configure rsyslogd to log to an oracle instance

Configure oracle database

For storing your syslog messages in an oracle database you need first of all a database and user to connect to.

In order to connect to a database we need a connection descriptor which is stored in the TNSNAMES.ORA:

ORA11P =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = ora11p)
 )
 )

As you can see: We use the SCAN access schema even with an oracle 10g client (rsyslogd only compiles with 10g instant client libraries at the moment).

In a second step create a schema owner in your database:

create user syslog identified by syslog default tablespace users quota unlimited on users;
grant create session to syslog;
create role syslog_role;
grant syslog_role to syslog;
grant create table to syslog_role;
grant create sequence to syslog_role;

Create a test table “foo” as user “syslog”:

create table foo (hostname varchar2(100), message(4000));

We will use this table to store our first syslog reconds in. This table will be replaced in the second part of the article.

Configure rsyslogd

Create a initial rsyslogd.conf file by copying the shipped rsyslogd.conf from the rsyslogd archive to /etc.

Add the following lines to the end of that file:

$ModLoad omoracle
$OmoracleDBUser syslog
$OmoracleDBPassword syslog
$OmoracleDB ora11p
$OmoracleBatchSize 1
$OmoracleBatchItemSize 4096
$OmoracleStatementTemplate OmoracleStatement
$template OmoracleStatement,"insert into foo(hostname,message) values (:host,:message)"
$template TestStmt,"%hostname%%msg%"
*.*                     :omoracle:;TestStmt

$OmoracleDBUser specifies the username in the database, $OmoracleDBPassword specifies the password and $OmoracleDB specifies the database name as stated in the TNSNAMES.ORA file.

Start rsyslog for the first time

To start rsyslogd process we create a small shell script:

export TNS_ADMIN=<the place to save the TNSNAMES.ORA to>

export LD_LIBRARY_PATH=/usr/lib/oracle/10.2.0.4/client64/lib

rsyslogd &

Exporting the TNS_ADMIN and adjusting the library search path is neccessary for rsyslogd to locate the TNSNAMES.ORA file and locate the oracle instant client libraries.

When executing the script above rsyslogd will start and log messages to /var/log/messages:

2009-11-04T16:31:11.866237+01:00 rac1 kernel: imklog 5.2.0, log source = /proc/kmsg started.
2009-11-04T16:31:11.866267+01:00 rac1 rsyslogd: [origin software="rsyslogd" swVersion="5.2.0"
x-pid="24604" x-info="http://www.rsyslog.com"] start
2009-11-04T16:31:11.437682+01:00 rac1 rsyslogd: WARNING: rsyslogd is running in compatibility mode.
Automatically generated config directives may interfer with your rsyslog.conf settings. We suggest
upgrading your config and adding -c4 as the first rsyslogd option.
2009-11-04T16:31:11.865890+01:00 rac1 rsyslogd: Warning: backward compatibility layer added to
following directive to rsyslog.conf: ModLoad immark
2009-11-04T16:31:11.865921+01:00 rac1 rsyslogd: Warning: backward compatibility layer added to
following directive to rsyslog.conf: MarkMessagePeriod 1200
2009-11-04T16:31:11.865932+01:00 rac1 rsyslogd: Warning: backward compatibility layer added to
following directive to rsyslog.conf: ModLoad imuxsock

We check our table for rows:

-bash-3.2$ sqlplus syslog/syslog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 4 16:31:20 2009

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select count(1) from foo;

 COUNT(1)
----------
 6

Ok. Obviously there are rows being inserted. We will now continue and improve the logging to be of real use.

Advanced logging

Above we configured a minimalistic logging for testing purposes. To be of real use we will configure different logging scenarios:

• Full detail logging with maximum details
• Oracle Audit logging

The following examples are mainly for educational purposes. You can use them and most likely you have to modify them. Feel free to do so.It would be nice if you´d give me a feedback.

Enabling remote syslogging

Enable udp syslogging

It it recommended to send syslog messages over TCP: But if your syslog daemon only supports UDP you have to enable it in your rsyslog.conf file by uncommenting the follwoing lines:

# Provides UDP syslog reception
$ModLoad imudp.so
$UDPServerRun 514

Configure syslog forwarding

Traditional syslog

Enabling message forwarding to a remote syslog server is acieved by adding the follwoing line to /etc/syslog.conf and restarting syslog:

*.*                             @<hostname or ip>

syslog-ng

To enable remote syslogging with syslog-ng add the following lines to forward ALL messages:

destination loghost-test { tcp("<hostname or ip>" port (514)); };
log { source(src); destination(loghost-test); };

Implement full-detail syslog message logging

Syslog defines so called properties. These properties contain for instance the hostname, the priority and the “message” which is the text to be logged. A list of available properties are here.

Based on these properties we created a full-detail configuration. Note that SYSLOGFACILITY, SYSLOGSEVERITY and SYSLOGPRIORITY are stored in the table twice: as numeric value *and* text string. If you want to optimize the table this is a good starting point. The fields chosen are mostly quite big VARCHAR2 fields. Feel free to optimize them to suit your needs.

Create the required Table

create table logs
(
  msg                 varchar2(4000),
  hostname            varchar2(250),
  syslogtag           varchar2(4000),
  programname         varchar2(4000),
  pri                 varchar2(10),
  pri_text            varchar2(4000),
  iut                 varchar2(10),
  syslogfacility      varchar2(10),
  syslogfacility_text varchar2(100),
  syslogseverity      varchar2(10),
  syslogseverity_text varchar2(100),
  syslogpriority      varchar2(10),
  syslogpriority_text varchar2(100),
  timegenerated       date,
  timereported        date,
  app_name            varchar2(4000),
  procid              varchar2(10),
  msgid               varchar2(10),
  inputname           varchar2(100)
);

The script for creating the table can be found here.

rsyslog configuration

The following lines are required to enable logging:

# enable the module - only required ONCE
$ModLoad omoracle
$OmoracleDBUser syslog
$OmoracleDBPassword syslog
$OmoracleDB ora11p
$OmoracleBatchSize 1
$OmoracleBatchItemSize 16000

# THE ACTUAL STATEMENT STARTS HERE
# enable statement
$OmoracleStatementTemplate OmoracleStatement2

$template OmoracleStatement2,"insert into logs (msg,hostname,syslogtag,programname,pri,pri_text,
iut,syslogfacility,syslogfacility_text,syslogseverity,syslogseverity_text,syslogpriority,
syslogpriority_text,timegenerated,timereported,app_name,procid,msgid,inputname)
values (:msg,:hostname,:syslogtag,:programname,:pri,:pri_text,:iut,:syslogfacility,
:syslogfacility_text,:syslogseverity,:syslogseverity_text,:syslogpriority,
:syslogpriority_text,to_date(:timegenerated, 'MON DD HH24:MI:SS'),
to_date(:timereported, 'MON DD HH24:MI:SS'),:app_name,:procid,:msgid,:inputname)"

$template TestStmt2,"%msg%%hostname%%syslogtag%%programname%%pri%%pri-text%%iut%
%syslogfacility%%syslogfacility-text%%syslogseverity%%syslogseverity-text%
%syslogpriority%%syslogpriority-text%%timegenerated%%timereported%
%app-name%%procid%%msgid%%inputname%"

*.*                     :omoracle:;TestStmt2

Attention: TEMPLATE directives needs to be written in ONE SINGLE line!

Oracle Database Audit Logging

After database auditing over syslog works we will now enable oracle databases to send audit messages over syslog instead of writing to AUD$ table or local files on database server.

Enable database audit logging over syslog

To enable auditing just set the following settings in your (s)pfile:

audit_syslog_level='local0.notice'
audit_trail='os'

If you have multiple database running on your system you have to use separate syslog lever (e.g. “local0.notice” for the first database, “local1.notive” for the second database and so on). Otherwise you will not be able to distinguish multiple database running on the same host.

Configure rsyslogd

Once configured the database will send audit messages to the local running syslog daemon which should be configured to forward syslog messages to the centralized rsyslog server.

A sample syslog line received from a remote host looks like the following:

2009-11-05T16:38:10+01:00 servername Oracle Audit[18065]: LENGTH: “231” SESSIONID:[8] “25275249” ENTRYID:[2] “12”
STATEMENT:[3] “144” USERID:[8] “SCOTT” USERHOST:[14] “CLIENTNAME” TERMINAL:[10] “TERMINALNAME”
ACTION:[2] “55” RETURNCODE:[1] “0” OBJ$NAME:[4] “NONE” OS$USERID:[14] “joeuser”

Note that the sample shown above consists of three fields:

• timestamp (“2009-11-05T16:38:10+01:00”)
• Servername (“servername”)
• message field (“Oracle Audit[18065]: LENGTH: “231” SESSIONID:[8] “2527524…..”)

As you can see the whole oracle audit message is placed as a whole in the message field. This makes querying extremely hard. In order to get a better overview i wanted to separate the audit message fields and store them in a separated table.

The required settings in /etc/rsyslog.conf files are placed BELOW the lines for enabling general syslogging:

###############################################################################
$OmoracleStatementTemplate OmoracleStatement3
$template TestStmt3,"%msg%%timereported:::date-mysql%
%hostname%%syslogfacility%%syslogseverity%

$template OmoracleStatement3,"begin
transform_oracle_audit(:msg,:timereported,:logging_host,:SYSLOGFACILITY,:SYSLOGSEVERITY); end;"

if $programname=='Oracle' then :omoracle:;TestStmt3

Create required databases objects

Unfortunately i am a quite bad programmer so a friend of mine helped me a lot (thanks Stephan!). Here is the resulting procedure which takes a audit message string and separates it into single fields and saves them in a table. The required table to hold the data can be downloaded here.

How does the logging information look like?

pure syslog messages

The figure shown below is an example of an oracle audit message stored in the “logs” table (which stores the basic syslog messages).

A non-oracle-aduit syslog messages is shown in the following picture. In this case postfix received mail…..

Oracle audit messages

The picture below is taken from the audit tables filled by our package which splits audit messages. The message before processing can be seen in the very first picture above. I guess you will notice the difference. By the way: The ACTIONS are still encoded. But they are documented and you can easily expand the split procedure to decode them. In case you aksing: “100” means: “LOGIN”.

Tuning

Tune the module

Thing the modules performance is quite easy. All you can adjust is the value of $OmoracleBatchSize which specifies the number of rows at which a “commit” occurs. The value should not be too low (e.g. “1”) to add significant overhead but not too high (e.g. 1000) to make real-time-monitoring impossible.

What about Indexes?

Currently i have not thought about indexes…. Of course there are some obvious candidates like the date column or programname or hostname. For large tables you will vertainly need indexes but what indexes depends on your queries made by your frontend.

Partitioning?

Yeah, i thought about that too. At the current implementation status i have not implemented partitioning. But i am planning to add this later to speed up queries (“real-time monitoring”).

I need a parser for Windows event messages

Yes, that would be fine. Feel free to write one :-)

I need a Web Interface

In short: Currently there are none – at least for Oracle. You have to write one your own (e.g. with APEX) or modify an existing product (e.g. “phplogcon” or “php-syslogng”).