MonitorWare Knowledge Base

Hi there,

I am relatively new to rsyslog and have managed to get it in installed and configured on RHEL5 using rsyslog-1.19.2-1 and mysql-5.0.22-2.1 but I am getting the following errors when I try and log to the database.


-1208052032: Calling select, active file descriptors (max 13): 3 12 13
-1208054896: Called logerr, msg: db 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 ':)' at line 1

-1208054896: logmsg: syslog.err<43>, flags 5, from '', msg db 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 ':)' at line 1

Can anyone please help?

Thanks in advance.

• Custom written rsyslog.conf?
• Maintenance Contract?
• Installation support?

It looks like there is a problem with the template. Can you post the relevant part of your config file (be sure to delete passwords!)?

Rainer

Thanks here you go:

$template logs,"insert into logs_incoming(facility, priority, date, time, host, message, seq) values (%syslogfacility%, %syslogpr
iority%, '%timereported:::date-mysql%', '%timereported:::date-mysql%', '%HOSTNAME%', '%msg%', %syslogtag%)", SQL
*.* >localhost,syslog,****,****;logs

I think it has to do with some spaces in the parameters (or other special characters). Try this:

$template logs,"insert into logs_incoming(facility, priority, date, time, host, message, seq) values (%syslogfacility%, %syslogpriority%, '%timereported:::date-mysql%', '%timereported:::date-mysql%', '%HOSTNAME%', '%msg%', '%syslogtag%')", SQL

Note the quotes around %syslogtag%.

Rainer

OK thanks, I have tried that and I now have a new error as below.

-1208038512: Called logerr, msg: db error (1062): Duplicate entry '14' for key 1

-1208038512: logmsg: syslog.err<43>, flags 5, from '', msg db error (1062): Duplicate entry '14' for key 1

Also I was looking to find the syslog-ng equivalent to '$SEQ', '$STATUS' for the rsyslog template any ideas, I thought it maybe syslogtag?

It looks like you have defined facility as a key on your database table. Not sure what you did. In any case, there should be no key, at leas no unique.

Can you point me to what these syslog-ng variables mean?

Rainer

Here is a print of the columns in my database table.

+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| facility | varchar(10) | YES | | NULL | |
| priority | varchar(10) | YES | | NULL | |
| date | date | YES | | NULL | |
| time | time | YES | | NULL | |
| host | varchar(128) | YES | | NULL | |
| message | text | YES | | NULL | |
| seq | int(10) unsigned | NO | PRI | NULL | auto_increment |
| status | tinyint(4) | NO | MUL | 0 | |
+----------+------------------+------+-----+---------+----------------+


does the layout of the template have to match the order of the tables as they are displayed here?

If you mean if the fields must be in the same order in the insert statement and the table: no

You provide a character value to the numerical field SEQ. I see it is auto-increment, so I'd just leave it alone and not provide any value at all. But you must provide a value for status, as it does not allow NULLs.

... of course dropping of the status column would be ok from the rsyslog point of view

here is an output from the logs that I am trying to push into the database I think the "14" is referring to the date. I am assuming that the order of the template must match up with the order of the logs?

Dec 14 14:29:23 Dec 14 2007 13:22:50: %ASA-6-302016: Teardown UDP connection 7382460 for outside:x.x.x.x/161 to inside:x.x.x.x/35180 duration 0:02:01 bytes 360

Thanks

No, the message is parsed and fields are extracted. This is why there are template variables. When looking at the message, it is not 100% RFC 3164 compliant, but I think we can handle that. Best for troubleshooting, use a write file with the template you have specified. Then we see the actual sql statements.

I have tried to do what you have suggested but I am having no luck here is the last atempt to log the template I have created to a text file.

$template logs,"insert into logs_incoming(facility, priority, date, time, host, message, seq) values (%syslogfacility%, %syslogpriority%, '%timereported:::date-mysql%', '%timereported:::date-mysql%', '%HOSTNAME%', '%msg%', '%syslogtag%')", SQL
*.* /var/log/test/mysqllog;logs

This logs absolutly nothing to the fifo file mysqllog.

I have also tried by changing the template to the following

$template logs,"%syslogfacility%, %syslogpriority%, '%timereported:::date-mysql%', '%timereported:::date-mysql%', '%HOSTNAME%', '%msg%', '%syslogtag%'"
*.* /var/log/test/mysqllog;logs

Which works but does not tag the columns.

Can you help?

This is the latest which is not writing to the file but I think the debug is saying that it is working.

$ModLoad MySQL

$template logs,"insert into logs_incoming(facility, priority, date, time, host, message, seq) values (%syslogfacility%, %syslogpr
iority%, '%timereported:::date-mysql%', '%timereported:::date-mysql%', '%HOSTNAME%', '%msg%', '%syslogtag%')", SQL
*.* |/var/log/test/mysqllog;logs


Successful select, descriptor count = 1, Activity on: 6
-1208121664: Host name for your address (x.x.x.x) unknown
-1208121664: Message from inetd socket: #6, host: x.x.x.x
-1208121664: Message length: 182, File descriptor: 6.
-1208121664: logmsg: local4.info<166>, flags 2, from 'x.x.x.x', msg Dec 17 2007 12:00:57: %ASA-6-302015: Built outbound UDP connection 7806897 for outside:x.x.x.x/xxx (x.x.x.x/xxx) to inside:x.x.x.x/44935 (x.x.x.x/xxx)
-1208121664: Message has legacy syslog format.
-1208121664: EnqueueMsg signaled condition (0)
-1208121664: nfds == 0, aborting
-1208124528: -1208121664: Listening on UDP syslogd socket 5 (IPv6/port 514).
-1208121664: Lone worker is running...
Listening on UDP syslogd socket 6 (IPv4/port 514).
-1208121664: ----------------------------------------
-1208121664: Calling select, active file descriptors (max 6): 3 5 6
-1208124528: Called fprintlog, logging to builtin-file (/var/log/test/mysqllog)
-1208124528: singleWorker: queue EMPTY, waiting for next message.
-1208121664:

The pipe file is blank.

This is not a pipe, it should be a regular file. I have to admit that I wonder why you don't receive an error message, but anyhow... So please delete that file and do a "touch /var/log/test/mysqllog". Let me know the result.

Thanks,
Rainer

Thanks that worked, here is the output (it was all bundled together but I separated the logs)

insert into syslog_incoming(facility, priority, date, time, host, message, seq) values (20, 6, '20071217143807', '20071217143807', 'Dec', ' 2007 13:32:37: %ASA-6-302014: Teardown TCP connection 7820701 for outside:x.x.x.x/xxx to inside:x.x.x.x/xxx duration 0:00:00 bytes 123 TCP Reset-O', '17')

insert into syslog_incoming(facility, priority, date, time, host, message, seq) values (20, 6, '20071217143807', '20071217143807', 'Dec', ' 2007 13:32:38: %ASA-6-302014: Teardown TCP connection 7820702 for outside:x.x.x.x/xxx to inside:x.x.x.x/xxx duration 0:00:00 bytes 66 TCP FINs', '17')

It all seems to be ok with all the fields correct??