How do I configure rsyslog to deal with MySQL Slow Query Log multi-line messages?

Question

Using rsyslog v8.2.2, I would like to group multi-line messages, such as those output by the MySQL Slow Query log.

Each new message begins with three consecutive lines having '#' as the first char of the line, though some messages have only two lines having a '#' at the beginning of the message. (I suspect queries triggered by PHP cli, instead of standard PHP, do not carry the # Time: stamp). Example:

# Time: 140817  0:59:22
# User@Host: root[root] @ localhost []
# Query_time: 5.864315  Lock_time: 0.000033 Rows_sent: 857715  Rows_examined: 857715
SET timestamp=1408237162;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `sales_flat_quote_shipping_rate`;
# Time: 140817  1:00:06
# User@Host: user2[user2] @ localhost []
# Query_time: 4.070595  Lock_time: 0.000068 Rows_sent: 0  Rows_examined: 1536
use db2;
SET timestamp=1408237206;
DELETE FROM `catalog_product_index_price_idx`;
# User@Host: db3[db3] @ localhost []
# Query_time: 3.892674  Lock_time: 0.046493 Rows_sent: 0  Rows_examined: 659
use db3;
SET timestamp=1408237206;
UPDATE user SET cert = 'yes' WHERE site_id < 10;
# Time: 140817  1:00:06
# User@Host: user2[user2] @ localhost []
# Query_time: 4.070595  Lock_time: 0.000068 Rows_sent: 0  Rows_examined: 1536
use db2;
SET timestamp=1408237206;
DELETE FROM `catalog_product_index_price_idx`;
# User@Host: db3[db3] @ localhost []
# Query_time: 3.892674  Lock_time: 0.046493 Rows_sent: 0  Rows_examined: 659
use db3;
SET timestamp=1408237206;
UPDATE user SET cert = 'yes' WHERE site_id < 10;

How do I configure rsyslog to send them over as a single message?

Answer 1

Rsyslog can group multiline log message into a single message via imfile module, however only following three read modes are supported:

• Line based (default) - each line is a new message.
• Paragraph - there is a blank line between log messages.
• Indented - new log messages start at the beginning of a line. If a line starts with a space it is part of the log message before it.

Your case doesn't match any of these, so you can't do the grouping directly via rsyslog. However you can parse your log files and convert them to a format which rsyslog will be able to understand. Simple shell script may do the job or you can have a look at fully featured log management tools like logstash.