Hello All, 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. Currently my destination looks like this: destination d_mysql { sql( type(mysql) host("localhost") username("myusername") password("mypassword") database("syslog") table("logs") columns("host", "facility", "priority", "level", "tag", "datetime", "program", "msg") values("$HOST_FROM", "$FACILITY", "$PRIORITY", "$LEVEL", "$TAG", "$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC", "$PROGRAM", "$MSG") indexes("host", "facility", "priority", "datetime", "program") ); }; This table (logs) also has source_ip and destination_ip fields which are currently unused since I don't know how to extract that from the message. For the above example, I would want those fields to contact '192.168.2.100' and '74.1.2.3' respectively. 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? Any help would be greatly appreciated. Thanks, Brandon
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
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
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
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
On Fri, 2011-07-29 at 19:22 +0200, Jakub Jankowski 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...
patterndb() would work if the order of the fields is definite. if they are not, it's going to be ugly. I was pondering to write a welf parser (which the above format is), that could be used to preprocess logs prior to going to db-parser(), but that's something that you either have to wait for, implement yourself or wait someone who has the same itch and does it for you. :) -- Bazsi
participants (4)
-
Balazs Scheidler
-
Brandon Phelps
-
Jakub Jankowski
-
Martin Holste