Ok, a couple of things. The big thing is that the message you gave is unclear as to whether it's the message or whether it's the timestamp, host, and message. Assuming that the message looks like this: 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 then use this pattern and make sure to fill in the right program name between <program></program> (alternatively, you can leave out <program></program> to match against all programs which will increase load): <patterndb version='3' pub_date='2011-08-04'> <ruleset name="firewall" id='2'> <pattern>sonicwall</pattern> <rules> <rule provider="local" class='2' id='2'> <patterns> <pattern>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::@@ESTRING:src_interface: @dst=@ESTRING:dst::@@ESTRING:dst_interface: @proto=@ESTRING:proto:/@@ANYSTRING:subproto:@</pattern> </patterns> </rule> </rules> </ruleset> </patterndb> I tested it like this: bin/pdbtool match -dvtep /tmp/test2.xml -P sonicwall -D -M '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 also had a few mistakes in my first pattern because I didn't test it. Please give this one a shot. On Mon, Aug 1, 2011 at 1:52 PM, Brandon Phelps <bphelps@gls.com> wrote:
Thanks Martin,
However using the below configuration I get the following in the output of 'syslog-ng -d':
Running application hooks; hook='1' Running application hooks; hook='3' Unknown parser type specified; type='id=' Log pattern database reloaded; file='/etc/syslog-ng/sonicwall.xml', version='3', pub_date='2011-08-01' syslog-ng starting up; version='3.1.3' Incoming log entry; line='<134>id=firewall sn=0017C5158708 time="2011-08-01 14:34:51" fw=1.2.3.4 pri=6 c=1024 m=537 msg="Connection Closed" n=0 src=10.1.1.1:29356:X1 dst=6.7.8.9:25:X3-V104 proto=tcp/smtp sent=460 rcvd=748 ' Running SQL query; query='SELECT * FROM test_table WHERE 0=1' Running SQL query; query='INSERT INTO test_table (when, src_ip, dst_ip) VALUES (\'2011-08-01 14:34:51\', \'\', \'\')' Error running SQL query; type='mysql', host='localhost', port='', user='myuser', database='syslog', error='1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'when, src_ip, dst_ip) VALUES (\'2011-08-01 14:34:51\', \'\', \'\')\' at line 1', query='INSERT INTO test_table (when, src_ip, dst_ip) VALUES (\'2011-08-01 14:34:51\', \'\', \'\')'
So it would appear that A) $src and $dst are not being set properly (they are empty) and B) some stuff is getting escaped that shouldn't be, namely all of those "'" marks.
An ideas? My configuration is below.
syslog-ng.conf:
source s_udp514 { udp( port(514) ); };
destination d_mysql { sql( type(mysql) host("localhost") username("myuser") password("mypass") database("syslog") table("test_table") columns("when", "src_ip", "dst_ip") values("$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC", "$src", "$dst") indexes("id") ); };
parser p_sonicwall { db-parser( file("/etc/syslog-ng/sonicwall.xml") ); };
log { parser(p_sonicwall); source(s_udp514); destination(d_mysql); };
/etc/syslog-ng/sonicwall.xml:
<?xml version='1.0' encoding='UTF-8'?> <patterndb version='3' pub_date='2011-08-01'> <ruleset name='firewall' id='0000001'> <pattern>id=firewall</pattern> <rules> <rule provider='local' class='firewall' id='0000001: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:</pattern> </patterns> </rule> </rules> </ruleset> </patterndb>
On 07/30/2011 11:01 AM, Martin Holste wrote:
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
______________________________________________________________________________ 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
______________________________________________________________________________ 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