10

Out of the box rsyslog will dump everything into the SystemEvents table within the `Syslog database (if you use the default schema provided). I would like to use a regular expression to filter inbound messages into separate database tables.

I've played with this, but I'm having a hard time figuring out the best way to accomplish this (or even a way that functions).

In my rsyslog.conf:

$template wireless, \
 "insert into RogueAPs \
 (ReceivedAt, DeviceReportedTime, Facility, Priority, FromHost, Message) \
 VALUES('%timegenerated%', '%timereported%', '%syslogfacility%', '%syslogpriority%', '%fromhost-ip%', '%msg%');", \ 
 stdsql

if $msg contains 'subtype=wireless' then :ommysql:127.0.0.1,Syslog,dbusername,dbpassword;wireless

*.* :ommysql:127.0.0.1,Syslog,dbusername,dbpassword

This was my latest attempt, but I'm stuck.

(the RogueAPs table is just a clone of the default SystemEvents table that ships with rsyslog)

Version Info:

shell# /usr/local/sbin/rsyslogd -v
rsyslogd 5.5.5, compiled with:
        FEATURE_REGEXP:                         Yes
        FEATURE_LARGEFILE:                      No
        FEATURE_NETZIP (message compression):   Yes
        GSSAPI Kerberos 5 support:              No
        FEATURE_DEBUG (debug build, slow code): No
        Atomic operations supported:            Yes
        Runtime Instrumentation (slow code):    No

See http://www.rsyslog.com for more information.
Michael Mrozek
  • 91,316
  • 38
  • 238
  • 232
efk
  • 231
  • 2
  • 5

2 Answers2

1

from taking a look at this tutorial, I see no difference.

But taking a look at the template documentation from rsyslog, there seems to be a difference with mysql depending on the setting for parameter NO_BACKSLASH_ESCAPES.

From the docs:

sql - format the string suitable for a SQL statement in MySQL format. This will 
replace single quotes ("'") and the backslash character by their backslash-escaped
counterpart ("\'" and "\\") inside each field. Please note that in MySQL
configuration, the NO_BACKSLASH_ESCAPES mode must be turned off for this format to
work (this is the default).

stdsql - format the string suitable for a SQL statement that is to be sent to a
standards-compliant sql server. This will replace single quotes ("'") by two single
quotes ("''") inside each field. You must use stdsql together with MySQL if in MySQL
configuration the NO_BACKSLASH_ESCAPES is turned on.
Rui F Ribeiro
  • 55,929
  • 26
  • 146
  • 227
Christian
  • 1,571
  • 9
  • 10
0

The rsyslog documentation is, unfortunately, not terribly complete or easy to understand in some areas. I've spent a good part of the past few weeks working on rsyslog/MySQL/regex stuff.

Can you post a sample of the log line you're trying to regex match, the schema of the table you want it to go into, etc.? The stuff that you posted seems like it should work... what are you trying to filter out? And is the default schema usable for you?

On a side note, I couldn't help but notice that you're using a table called RogueAPs. I don't know what vendor you're using, but I have regex rules for authentication log messages from Meru Networks and BlueSocket controllers.

Jason Antman
  • 121
  • 4