Search code examples
regexelasticsearchlogstashelastic-stackfilebeat

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

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

  1. message --> # Time: 2019-11-08T20:02:05.474508Z
  2. 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;
Solution

  • 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.

    enter image description here

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