Yep, patterndb will solve this beautifully for you. Here's a pattern (assuming that you've provided the message, not the timestamp + host + message): <patterndb version='3' pub_date='2011-07-29'> <ruleset name='firewall' id='1'> <pattern></pattern> <rules> <rule provider='local' class='firewall' id='1'> <patterns> <pattern>@ESTRING:month: :@@ESTRING:day: :@@ESTRING:time: :@@ESTRING:host: :@id=@ESTRING:id: :@sn=@ESTRING:sn: :@time=@ESTRING:timestamp: :@fw=@ESTRING:fw: :@pri=@ESTRING:pri: :@c=@ESTRING:c: :@m=@ESTRING:m: :@msg=@ESTRING:msg: :@n=@ESTRING:n: :@src=@ESTRING:src: :@dst=@ESTRING:dst: :@proto=@ESTRING:proto: :@ </patterns> </rule> </rules> </ruleset> </patterndb> What I've done is simply captured each field as name by using ESTRING which says "match until you get to the following string" where the string to stop on is a single space. The format of ESTRING is @ESTRING:<field name to extract>:<pattern to signal stop of capture>:@ You will need to put the program name between <pattern></pattern> so that this pattern match will fire when the program name matches whatever you put in that element. So if the program were "CHECKPOINT-FW-1234" you could put <pattern>CHECKPOINT-FW</pattern> and it would work. So now your columns/values for the sql destination looks like this: columns("host", "facility", "priority", "level", "tag", "datetime", "program", "msg", "source_ip", "destination_ip") values("$HOST_FROM", "$FACILITY", "$PRIORITY", "$LEVEL", "$TAG", "$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC", "$PROGRAM", "$MSG", "$src", "$dst") $src and $dst are now available because we captured them with @ESTRING:src: :@ and @ESTRING:dst: :@ Check out the documentation for specific details such as where to put the patterndb.xml file, etc. On Fri, Jul 29, 2011 at 12:22 PM, Jakub Jankowski <shasta@toxcorp.com> wrote:
On 2011-07-29, Brandon Phelps wrote:
Could anyone explain how I would parse a message that looks like this: Jul 29 08:58:38 192.168.1.1 id=firewall sn=0017C5158708 time="2011-07-29 08:58:38" fw=100.1.1.1 pri=6 c=262144 m=98 msg="Connection Opened" n=0 src=192.168.2.100:123:X0 dst=74.1.2.3:X1 proto=udp/ntp
I am logging to mysql and would like to extract the 'src' and 'dst' fields from the above message so that I can insert them into indexed fields in my database. [...] Is my only option in this case to write a perl script or something that watches a named pipe and have syslog-ng log to the named pipe instead, while my perl script does the actual parsing? Or can I do what I want with syslog-ng alone?
You seriously need to look at patterndb functionality. http://bazsi.blogs.balabit.com/2009/03/an-introduction-to-db-parser/ http://www.balabit.com/sites/default/files/documents/syslog-ng-ose-v3.2-guid...
HTH.
-- Jakub Jankowski|shasta@toxcorp.com|http://toxcorp.com/ GPG: FCBF F03D 9ADB B768 8B92 BB52 0341 9037 A875 942D ______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.balabit.com/wiki/syslog-ng-faq