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
On Thu, Dec 16, 2010 at 05:49:28PM +0100, Giovanni Mancuso wrote:
@version: 3.0
This version looks rather old. Have you tried it in the latest one? A lot of the patterndb and sql stuff has been bug-fixed lately. Have you run the daemon with debugging options such as: -F --no-caps -v -d -t -e Then we could see what it is doing internally. Matthew.
On 16/12/2010 18:04, Matthew Hall wrote:
On Thu, Dec 16, 2010 at 05:49:28PM +0100, Giovanni Mancuso wrote:
@version: 3.0 This version looks rather old. Have you tried it in the latest one? A lot of the patterndb and sql stuff has been bug-fixed lately. If i run syslog-ng with debug, i see that the version that is: 3.1.3 and i hve the warning:
WARNING: You are using the default values for columns(), indexes() or values(), please specify these explicitly as the default will be dropped in the future; Running application hooks; hook='1' Running application hooks; hook='3' Log pattern database reloaded; file='/etc/syslog-ng/patterndb.d/mcs.xml', version='1', pub_date='2010-12-14' syslog-ng starting up; version='3.1.3' Database thread started;
Have you run the daemon with debugging options such as:
-F --no-caps -v -d -t -e
Then we could see what it is doing internally. If i run with this options i have:
Incoming log entry; line='<150>mcs[123] ###############Accesso dell\'utente xxx.1@xxx.mailware.it da ip 10.0.10.98\x0a' Filter rule evaluation begins; filter_rule='f_authpriv' Filter node evaluation result; filter_result='not-match', filter_type='facility' Filter rule evaluation result; filter_result='not-match', filter_rule='f_authpriv' Filter rule evaluation begins; filter_rule='f_cron' Filter node evaluation result; filter_result='not-match', filter_type='facility' Filter rule evaluation result; filter_result='not-match', filter_rule='f_cron' Filter rule evaluation begins; filter_rule='f_kern' Filter node evaluation result; filter_result='not-match', filter_type='facility' Filter rule evaluation result; filter_result='not-match', filter_rule='f_kern' Filter rule evaluation begins; filter_rule='f_lpr' Filter node evaluation result; filter_result='not-match', filter_type='facility' Filter rule evaluation result; filter_result='not-match', filter_rule='f_lpr' Filter rule evaluation begins; filter_rule='f_mail' Filter node evaluation result; filter_result='not-match', filter_type='facility' Filter rule evaluation result; filter_result='not-match', filter_rule='f_mail' Filter rule evaluation begins; filter_rule='f_syslog' Filter node evaluation result; filter_result='match', filter_type='facility' Filter node evaluation result; filter_result='match', filter_type='facility' Filter node evaluation result; filter_result='match', filter_type='AND' Filter node evaluation result; filter_result='match', filter_type='facility' Filter node evaluation result; filter_result='match', filter_type='AND' Filter rule evaluation result; filter_result='match', filter_rule='f_syslog' Filter rule evaluation begins; filter_rule='f_mcs' Filter node evaluation result; filter_result='match', filter_type='facility' Filter rule evaluation result; filter_result='match', filter_rule='f_mcs' Initializing destination file writer; template='/var/log/mw-collaboration/mw-collaboration-loginfile.log', filename='/var/log/mw-collaboration/mw-collaboration-loginfile.log' Running SQL query; query='SELECT * FROM mcslogin WHERE 0=1' Running SQL query; query='INSERT INTO mcslogin (date, loginuser, ipsource) VALUES (\'2010-12-16 18:59:05\', \'\', \'\')' Thanks
On Thu, Dec 16, 2010 at 07:55:54PM +0100, Giovanni Mancuso wrote:
If i run syslog-ng with debug, i see that the version that is: 3.1.3 and i hve the warning:
WARNING: You are using the default values for columns(), indexes() or values(), please specify these explicitly as the default will be dropped in the future;
Probably a good idea to fix whatever is causing it in your sql setup.
Running SQL query; query='SELECT * FROM mcslogin WHERE 0=1' Running SQL query; query='INSERT INTO mcslogin (date, loginuser, ipsource) VALUES (\'2010-12-16 18:59:05\', \'\', \'\')'
It appears your sql setup is inserting empty values to your tables. So probably the variables did not get filled in right. Try logging output to a text file using a rewrite or template that prints out the variables to be sure they work and get filled in. Here's an example of a template to use for an output file: template t_welf { template("time=\"$R_DATE\" fw=\"$HOST\" pri=\"$LEVEL_NUM\" foo=\"$bar\"\n"); template_escape(no); }; If you log to an output file using a template like this, you can see if your variables are getting filled in properly by the patterndb. Matthew.
On Thu, Dec 16, 2010 at 01:10:27PM -0800, Matthew Hall wrote:
Here's an example of a template to use for an output file:
template t_welf { template("time=\"$R_DATE\" fw=\"$HOST\" pri=\"$LEVEL_NUM\" foo=\"$bar\"\n"); template_escape(no); };
I forgot to mention. Of course fill in the foo=$bar part with the names of your variables. One thing you can do is use perl, sed, awk, or egrep to grab every single variable name from your XMLs, and add them all to your messages in some convenient order (I often go with alphabetical order myself). Matthew.
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
Hi, with mcs2.xml it works very well!!!! ;-) Thanks On 17/12/2010 09:57, Balint Kovacs wrote:
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
participants (3)
-
Balint Kovacs
-
Giovanni Mancuso
-
Matthew Hall