Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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.
share|improve this question

2 Answers 2

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.

share|improve this answer

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.

Hope this helps.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.