[syslog-ng] Help with db_parser()

[Balint Kovacs]

Hi Giovanni,

the problem is, that you are using the @STRING@ parser to get the email 
address, but apart from the alphanumeric chars, the email address will 
contain at least an @ sign (and also can contain many other non-alphanum 
chars), so I think you would be better off with using the @ESTRING@ 
parser and matching the space char at the end of the email address. The 
attached xml should match the supplied log message (but only if the IP 
address is also valid :))

blint at lyra:/tmp$ /usr/local/syslog-ng-patternize/bin/pdbtool match -c -D 
-p mcs2.xml -P mcs -M "###############Accesso dell'utente 
xxxx.1 at xxx.mailware.it da ip 111.222.111.222"
Pattern matching part:
###############Accesso dell'utente 
@ESTRING:LOGIN_USER=xxxx.1 at xxx.mailware.it@da ip 
@IPv4:IP_SOURCE=111.222.111.222@
Matching part:
###############Accesso dell'utente xxxx.1 at xxx.mailware.it da ip 
111.222.111.222
Values:
MESSAGE=###############Accesso dell'utente xxxx.1 at xxx.mailware.it da ip 
111.222.111.222
PROGRAM=mcs
.classifier.class=system
.classifier.rule_id=mcs
LOGIN_USER=xxxx.1 at xxx.mailware.it
IP_SOURCE=111.222.111.222

Best Regards,
Balint

On 12/16/2010 05:49 PM, Giovanni Mancuso wrote:
> Hi,
>
> I configure my Java application to log with log4j in syslog server, 
> and i configure my syslog-ng to store data in mysql database, but this 
> doen't work.
>
> My syslog-ng configuration is:
>
> @version: 3.0
> options {
>         chain_hostnames(no);
>         stats_freq(43200);
> };
> source src {
>     unix-stream("/dev/log" max-connections(256));
>     internal();
>     udp(ip("127.0.0.1") port(514));
>     file("/proc/kmsg");
> };
> destination mcs { 
> file("/var/log/mw-collaboration/mw-collaboration-loginfile.log"); };
> destination mcs_sql {
>         sql(
>                 type(mysql)
>                 host("localhost")
>                 username("syslogng")
>                 password("syslogng")
>                 database("mcslogin")
>                 table("mcslogin")
>                 columns("date varchar(32)","loginuser varchar(32) 
> ","ipsource varchar(32)")
>                 values("${S_YEAR}-${S_MONTH}-${S_DAY} 
> ${S_HOUR}:${S_MIN}:${S_SEC}","${LOGIN_USER}","${IP_SOURCE}")
>         );
> };
>
> filter f_mcs { facility(local2); };
> parser p_mcs {
>         db_parser(file("/etc/syslog-ng/patterndb.d/mcs.xml"));
> };
> log { source(src); filter(f_mcs); parser(p_mcs); destination(mcs_sql); 
> destination(mcs); };
>
> I created also a db_parser file that is:
>
> <patterndb version='1' pub_date='2010-12-14'>
> <program name='mcs'>
> <pattern>mcs</pattern>
> <rule id='mcs' class='system'>
> <pattern>###############Accesso dell'utente @STRING:LOGIN_USER@ da ip 
> @IPv4:IP_SOURCE@</pattern>
> </rule>
> </program>
> </patterndb>
>
> With this configuration, i have that in "mcs" destination it writes 
> all information:
>
> Dec 16 11:55:44 localhost mcs[123] ###############Accesso dell'utente 
> xxxx at xxx.mailware.it da ip 111.222.333.444
> Dec 16 12:53:23 localhost mcs[123] ###############Accesso dell'utente 
> xxxx.1 at xxx.mailware.it da ip 111.222.333.444
> Dec 16 14:07:40 localhost mcs[123] ###############Accesso dell'utente 
> xxxx.1 at xxx.mailware.it da ip 111.222.333.444
>
> but in "mcs_sql" destination, it writes only date:
>
> mysql> desc mcslogin;
> +-----------+-------------+------+-----+---------+-------+
> | Field     | Type        | Null | Key | Default | Extra |
> +-----------+-------------+------+-----+---------+-------+
> | date      | varchar(32) | YES  | MUL | NULL    |       |
> | loginuser | varchar(32) | YES  |     | NULL    |       |
> | ipsource  | varchar(32) | YES  |     | NULL    |       |
> +-----------+-------------+------+-----+---------+-------+
> 3 rows in set (0.00 sec)
>
> mysql> select * from mcslogin limit 10
>     -> ;
> +---------------------+-----------+----------+
> | date                | loginuser | ipsource |
> +---------------------+-----------+----------+
> | 2010-12-15 11:02:16 |           |          |
> | 2010-12-15 11:11:09 |           |          |
> | 2010-12-15 17:53:01 |           |          |
> | 2010-12-15 18:11:55 |           |          |
> | 2010-12-15 18:12:54 |           |          |
> | 2010-12-15 18:35:07 |           |          |
> | 2010-12-16 11:55:36 |           |          |
> | 2010-12-16 11:55:44 |           |          |
> | 2010-12-16 11:55:44 |           |          |
> | 2010-12-16 12:53:23 |           |          |
> +---------------------+-----------+----------+
> 10 rows in set (0.00 sec)
>
> Can you help me?
>
> Thanks
>
>
> ______________________________________________________________________________
> Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng
> Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng
> FAQ: http://www.campin.net/syslog-ng/faq.html
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.balabit.hu/pipermail/syslog-ng/attachments/20101217/a0f2fae9/attachment-0001.htm 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: mcs2.xml
Type: text/xml
Size: 380 bytes
Desc: not available
Url : http://lists.balabit.hu/pipermail/syslog-ng/attachments/20101217/a0f2fae9/attachment-0001.bin