From rsyslog wiki

Contents 1 Sample Use cases and configurations 1.1 Enabling the ProcessID column with Rsyslog and MySQL logging 1.1.1 Patches 1.1.1.1 PHPLogCon-2.7.1 patch 1.1.1.2 PHPLogCon-2.6.3 patch 1.1.1.3 rsyslog-4.2.0 patch

Sample Use cases and configurations

Enabling the ProcessID column with Rsyslog and MySQL logging

To enable the ProcessID column, three things need to be done. The following assumes you already have RSyslog logging to your database enabled, with the default SystemEvents table having the MonitorWare layout. This also assumes you have at least rsyslog 4.2.0 (it might work with lower versions) and phplogcon version 2.7.1 (2.6.3 is also known to work but needs slightly different handling). Also, we chose to change the definition of the monitorware DB-mapping instead of creating a new one.

1. Open your mysql client of choice for the syslog database. Enter:

   ALTER TABLE SystemEvents ADD COLUMN `ProcessID` char(8) default NULL,

   This adds the column we will need below. Note that an integer field, though perfectly capable of storing real process IDs, didn't work for us. This might have changed since we used a property replacer that replaces non-numeric process IDs with 0 in our template.

2. In your phpLogCon installation, edit include/constants_logstream.php, search for

   $dbmapping['monitorware']['DBMAPPINGS'][SYSLOG_EVENT_USER] = "EventUser";

   and add

   $dbmapping['monitorware']['DBMAPPINGS'][SYSLOG_PROCESSID] = "ProcessID";

   below that line

3. In your rsyslog.conf, add the template:

   $template OurDBLog,"INSERT INTO SystemEvents (Message, Facility, FromHost, Priority, \
   

   DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag, ProcessID) values \

   ( '%msg%',%syslogfacility%,'%HOSTNAME%',%syslogpriority%,'%timereported:::date-mysql%',\

   '%timegenerated:::date-mysql%',%iut%,'%programname%','%procid:R,ERE,0,ZERO:[0-9]+--end%')",SQL then add the database logging line (or change your existing one):

   *.* :ommysql:DB_HOST,DB_DATABASE,DB_USER,DB_PASSWORD;OurDBLog

   while replacing the DB_* with the values needed to write to your database. This template changes two things in relation to the original monitorware template included in rsyslog 4.2.0 is that the syslogtag is now occupied only by the programname (instead of the tag, which sometimes included the process id for some reason) and adds the processid column.

Hope this helps.

Patches

Please note that these patches might not apply because of cut-and-paste problems (I didn't see a way to directly attach these to the page instead of providing them inline).

PHPLogCon-2.7.1 patch

diff -udrw phplogcon-2.7.1/src/include/constants_logstream.php phplogcon-2.7.1.ours/src/include/constants_logstream.php
--- phplogcon-2.7.1/src/include/constants_logstream.php 2009-07-22 15:03:22.000000000 +0200
+++ phplogcon-2.7.1.ours/src/include/constants_logstream.php    2009-07-22 14:53:56.000000000 +0200
@@ -314,6 +314,7 @@
 $dbmapping['monitorware']['DBMAPPINGS'][SYSLOG_EVENT_SOURCE] = "EventSource";
 $dbmapping['monitorware']['DBMAPPINGS'][SYSLOG_EVENT_CATEGORY] = "EventCategory";
 $dbmapping['monitorware']['DBMAPPINGS'][SYSLOG_EVENT_USER] = "EventUser";
+$dbmapping['monitorware']['DBMAPPINGS'][SYSLOG_PROCESSID] = "ProcessID";

 $dbmapping['syslogng']['ID'] = "syslogng";
 $dbmapping['syslogng']['DisplayName'] = "SyslogNG";

PHPLogCon-2.6.3 patch

diff -udrw phplogcon-2.6.3/src/include/constants_logstream.php phplogcon-2.6.3.ours/src/include/constants_logstream.php
--- phplogcon-2.6.3/src/include/constants_logstream.php 2009-07-22 14:57:30.000000000 +0200
+++ phplogcon-2.6.3.ours/src/include/constants_logstream.php    2009-07-22 14:58:30.000000000 +0200
@@ -312,6 +312,7 @@
 $dbmapping['monitorware'][SYSLOG_EVENT_SOURCE] = "EventSource";
 $dbmapping['monitorware'][SYSLOG_EVENT_CATEGORY] = "EventCategory";
 $dbmapping['monitorware'][SYSLOG_EVENT_USER] = "EventUser";
+$dbmapping['monitorware'][SYSLOG_PROCESSID] = "ProcessID";

 $dbmapping['syslogng'][SYSLOG_UID] = "seq";
 $dbmapping['syslogng'][SYSLOG_DATE] = "datetime";

rsyslog-4.2.0 patch

This patch could be used to patch the createDB.sql file included with the ommysql plugin:

--- rsyslog-4.2.0/plugins/ommysql/createDB.sql     2009-03-23 10:50:09.000000000 +0100
+++ rsyslog-4.2.0.ours/plugins/ommysql/createDB.sql  2009-07-24 11:15:04.000000000 +0200
@@ -25,6 +25,7 @@
         SysLogTag varchar(60),
         EventLogType varchar(60),
         GenericFileName VarChar(60),
+        ProcessID varchar(8) NULL,
         SystemID int NULL
 );