Filebeat merge several lines from mysql-slow.log into one line

Question

I'm trying to analyze mysql-slow.log by using Filebeat Logstash and Elasticsearch. I have messages in mysql-slow.log file that look like this:

# Time: 2019-11-08T20:02:05.474508Z
# User@Host: user[user] @ localhost []  Id:     2
# Query_time: 0.000716  Lock_time: 0.000223 Rows_sent: 2  Rows_examined: 2
SET timestamp=1573243325;
select * from Persons;

First I try to make Filebeat send this log message with 5 lines to elasticsearch but all of them together in one line.

I set multiline input in filebeat.yml

multiline.pattern = `^\#`
multiline.negate = true
multiline.match = after

Unfortunately it doesn't work and elasticsearch recieves lines separately

message --> # Time: 2019-11-08T20:02:05.474508Z
message --> # User@Host: user[user] @ localhost [] Id: 2 and so on...

I want to recieve it in one message in the following format:

# Time: 2019-11-08T20:02:05.474508Z # User@Host: user[user] @ localhost []  Id:     2 # Query_time: 0.000716  Lock_time: 0.000223 Rows_sent: 2  Rows_examined: 2 SET timestamp=1573243325; select * from Persons;

Your multiline.pattern will match any line starting with a #, try to change it to ^\#[[:space:]]Time so it will match only lines starting with # Time. — leandrojmp
Thank you for your reply ! I changed multiline.pattern to ^\#[[:space:]]Time but with no result it sends me the log in the same format :(( — vladpoverin
There is something wrong on your filebeat or logstash config, I've just tested the pattern and it work, I will post the configs I've used as an asnwer. — leandrojmp

leandrojmp leandrojmp · Accepted Answer · 2019-11-09T17:12:46

Your multiline pattern is wrong, it will match any line that starts with an #, so each of your first three lines in your example will be an event for filebeat/logstash.

You need to change your multiline pattern to match only the first line of your event, which is the line starting with # Time.

The following filebeat configuration worked on my tests.

filebeat.inputs:
- type: log
  enabled: true
  paths:
    - /opt/data/stackoverflow/*.log
  multiline.pattern: '^\#[[:space:]]Time'
  multiline.negate: true
  multiline.match: after

output.logstash:
  hosts: ["elk:5044"]

The logstash pipeline simples listens on 5044 and outputs to elasticsearch, and then the result is the following.

As you can see all the file lines are indexed as a single event on elasticsearch.

Filebeat merge several lines from mysql-slow.log into one line

1 Answers