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@lyra:/tmp$ /usr/local/syslog-ng-patternize/bin/pdbtool match -c -D -p mcs2.xml -P mcs -M "###############Accesso dell'utente xxxx.1@xxx.mailware.it da ip 111.222.111.222"
Pattern matching part:
###############Accesso dell'utente @ESTRING:LOGIN_USER=xxxx.1@xxx.mailware.it@da ip @IPv4:IP_SOURCE=111.222.111.222@
Matching part:
###############Accesso dell'utente xxxx.1@xxx.mailware.it da ip 111.222.111.222
Values:
MESSAGE=###############Accesso dell'utente xxxx.1@xxx.mailware.it da ip 111.222.111.222
PROGRAM=mcs
.classifier.class=system
.classifier.rule_id=mcs
LOGIN_USER=xxxx.1@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@xxx.mailware.it da ip 111.222.333.444
Dec 16 12:53:23 localhost mcs[123] ###############Accesso dell'utente xxxx.1@xxx.mailware.it da ip 111.222.333.444
Dec 16 14:07:40 localhost mcs[123] ###############Accesso dell'utente xxxx.1@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

______________________________________________________________________________
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