syslog-ng-users August 2011 archive
Main Archive Page > Month Archives  > syslog-ng-users archives
syslog-ng-users: Re: [syslog-ng] Parsing Question

Re: [syslog-ng] Parsing Question

From: Brandon Phelps <bphelps_at_nospam>
Date: Mon Aug 01 2011 - 18:52:56 GMT
To: syslog-ng@lists.balabit.hu

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-guide-admin-en.html/index.html-single.html#chapter-patterndb
>>
>>
>> HTH.
>>
>>
>> --
>> Jakub Jankowski|shasta_at_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