Logstash parsing mysql-slow.log

Vladpov · November 5, 2019, 11:27am

Hey guys,

I'm a little bit struggling with Logtash parsing mysql-slow.log file.

I configured Filebeat to send logs from the file into Elasticsearch through Logstash so I can see recieved messages in Kibana like this:

Nov 5, 2019 @ 12:14:26.250	/var/log/mysql/mysql-slow.log	

# User@Host: user[user] @ localhost []  Id:     2
# Query_time: 0.000572  Lock_time: 0.000137 Rows_sent: 2  Rows_examined: 2
SET timestamp=1572952461;
select * from Persons;

I'd like to parse the message by using Logstash but every pattern I used led to some problem in communication between Filebeat and Logstash. For example I used the following configuration file for Logstash:

input {
       beats { port => 5044 
       codec => multiline{ 
             pattern => "^# Time:" 
             negate => true 
             what => previous } 
      } 
}
    filter {

      grok { match => [ "message", "^# User@Host: %{USER:query_user}(?:[[^]]+])?s+@s+%{HOSTNAME:query_host}?s+[%{IP:query_ip}?]" ] }

      grok { match => [ "message", "^# Thread_id: %{NUMBER:thread_id:int}s+Schema: %{USER:schema}s+Last_errno: %{NUMBER:last_errno:int}s+Killed: %{NUMBER:killed:int}"] }

      grok { match => [ "message", "^# Query_time: %{NUMBER:query_time:float}s+Lock_time: %{NUMBER:lock_time}s+ Rows_sent: %{NUMBER:rows_sent:int} s+Rows_examined: %{NUMBER:rows_examined:int}s+Rows_affected: %{NUMBER:rows_affected:int}s+Rows_read: %{NUMBER:rows_read:int}"] }

      grok { match => [ "message", "^# Bytes_sent: %{NUMBER:bytes_sent:float}"] }

      grok { match => [ "message", "^SET timestamp=%{NUMBER:timestamp}" ] }

      grok { match => [ "message", "^SET timestamp=%{NUMBER};s+%{GREEDYDATA:query}" ] }

      date { match => [ "timestamp", "UNIX" ] }

      mutate { remove_field => "timestamp" }

    }

output {
  elasticsearch {
hosts => ["localhost:9200"]
index => "mysql_logs"
  }
}

And it doesn't work because multiline codec isn't supported with beats... I set multiline in input section of filebeat.yml file:

multiline.pattern: '^#'
multiline.negate: true
multiline.match: after

So now I can run Logstash and Filebeat but filters don't match with lines and it doesn't give me any fields that are defined in config file in Logstash.

Does anyone hav an idea what could be wrong?

Thank you for any help!

Badger · November 5, 2019, 4:56pm

What does an event look like if you use this?

output { stdout { codec => rubydebug } }

Vladpov · November 6, 2019, 5:46pm

Thanks for reply!

Now my logstash conf file looks like

input {
       beats { port => 5044
}

    filter {

      grok { match => [ "message", "^# User@Host: %{USER:query_user}(?:[[^]]+])?s+@s+%{HOSTNAME:query_host}?s+[%{IP:query_ip}?]" ] }

      grok { match => [ "message", "^# Thread_id: %{NUMBER:thread_id:int}s+Schema: %{USER:schema}s+Last_errno: %{NUMBER:last_errno:int}s+Killed: %{NUMBER:killed:int}"] }

      grok { match => [ "message", "^# Query_time: %{NUMBER:query_time:float}s+Lock_tim
e: %{NUMBER:lock_time}s+ Rows_sent: %{NUMBER:rows_sent:int} s+Rows_examined: %{NUMBER:r
ows_examined:int}s+Rows_affected: %{NUMBER:rows_affected:int}s+Rows_read: %{NUMBER:rows
_read:int}"] }

      grok { match => [ "message", "^# Bytes_sent: %{NUMBER:bytes_sent:float}"] }

      grok { match => [ "message", "^SET timestamp=%{NUMBER:timestamp}" ] }

      grok { match => [ "message", "^SET timestamp=%{NUMBER};s+%{GREEDYDATA:query}" ] }

      date { match => [ "timestamp", "UNIX" ] }

      mutate { remove_field => "timestamp" }

    }

output { stdout { codec => rubydebug } }

and logs show

[2019-11-06T17:45:24,772][ERROR][logstash.agent           ] Failed to execute action {:action=>LogStash::PipelineAction::Create/pipeline_id:main, :exception=>"LogStash::ConfigurationError", :message=>"Expected one of #, => at line 6, column 12 (byte 66) after input {\n       beats { port => 5044  \n}\n    filter {\n\n      grok ", :backtrace=>["/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:41:in `compile_imperative'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:49:in `compile_graph'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:11:in `block in compile_sources'", "org/jruby/RubyArray.java:2584:in `map'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:10:in `compile_sources'", "org/logstash/execution/AbstractPipelineExt.java:153:in `initialize'", "org/logstash/execution/JavaBasePipelineExt.java:47:in `initialize'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:26:in `initialize'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline_action/create.rb:36:in `execute'", "/usr/share/logstash/logstash-core/lib/logstash/agent.rb:326:in `block in converge_state'"]}
[2019-11-06T17:45:25,749][INFO ][logstash.agent           ] Successfully started Logstash API endpoint {:port=>9600}
[2019-11-06T17:45:30,437][INFO ][logstash.runner          ] Logstash shut down.

And line 6 is the blank line under the filter...

Badger · November 6, 2019, 5:56pm

You are missing a } to close the input section.

Vladpov · November 6, 2019, 6:13pm

Ouu really sorry for that!

Now filebeat and logstash are running. What should I observe?

Messages are sitll not parsed and tags of every message show

beats_input_codec_plain_applied, _grokparsefailure

How can I parse the mysql logs?

Vladpov · November 7, 2019, 11:28am

I used GROK Debugger and wrote a filter that could parse the following one line message:

# Time: 2019-11-07T10:33:28.299458Z # User@Host: vlad[vlad] @ localhost []  Id:     8 # Query_time: 0.000448  Lock_time: 0.000113 Rows_sent: 2  Rows_examined: 2 SET timestamp=1573115750; select * from Persons;

The code:

%{NOTSPACE:MySQL_User} @ %{HOSTNAME:MySQL_Hostname} \[]%{SPACE} Id: %{SPACE} %{BASE10NUM:Id} # Query_time:%{SPACE}%{BASE16FLOAT:Query_time}%{SPACE}Lock_time: %{BASE16FLOAT:Lock_time}%{SPACE}Rows_sent:%{SPACE}%{BASE10NUM:Rows_sent}%{SPACE}Rows_examined: %{BASE10NUM:Rows_examined}%{SPACE}%{GREEDYDATA:Query}

Result:

{
  "Lock_time": "0.000113",
  "Rows_examined": "2",
  "Query": "SET timestamp=1573115750; select * from Persons;",
  "MySQL_User": "vlad[vlad]",
  "MySQL_Hostname": "localhost",
  "Query_time": "0.000448",
  "Id": "8",
  "Rows_sent": "2"
}

I thought that when I change multiline input in filebeat.yml file it sends me the myslq-slow.log in one line format.

My filebeat.yml:

  # The regexp Pattern that has to be matched. The example pattern matches all lines starting with [
  multiline.pattern: '^#'

  # Defines if the pattern set under pattern should be negated or not. Default is false.
  multiline.negate: true

  # Match can be set to "after" or "before". It is used to define if lines should be append to a pattern
  # that was (not) matched before or after or as long as a pattern is not matched based on negate.
  # Note: After is the equivalent to previous and before is the equivalent to to next in Logstash
  multiline.match: after

And I still get grokparsefailure.

How to achieve that logstash recieves the following message:

# Time: 2019-11-07T10:33:28.299458Z
# User@Host: vlad[vlad] @ localhost []  Id:     2
# Query_time: 0.000282  Lock_time: 0.000154 Rows_sent: 2  Rows_examined: 2
SET timestamp=1573122808;
select * from Persons;

In one line format? Is it even possible?

Badger · November 7, 2019, 4:28pm

I am not sure what your question is, but I think it is about filebeat rather than logstash, so you should ask it in the filebeat forum.

Vladpov · November 10, 2019, 10:10am

Thanks for reply! I found out that there is not a problem with filebeat but with Logstash. I thought that logstash can parse only one line messages so I was trying to merge several lines into one via filebeat so logstash could recieve it as one line and parse it into fields I need but there is an option to parse it as it is so mysql-slow.log messages

# Time: 2019-11-07T10:33:28.299458Z
# User@Host: vlad[vlad] @ localhost []  Id:     2
# Query_time: 0.000282  Lock_time: 0.000154 Rows_sent: 2  Rows_examined: 2
SET timestamp=1573122808;
select * from Persons;

could be parsed for fields:

user
host
query_time
rows_sent
rows_examined
query

I just don't know which filters I should use to parse multiple lines...

Badger · November 10, 2019, 3:52pm

OK, so if you are combining lines in filebeat then logstash will see a multi-line event. You can use grok on that just like any other event

    grok {
        break_on_match => false
        match => {
            "message" => [
                "Time: %{NOTSPACE:ts}",
                "User@Host: %{USER:user}\[%{USER}\] @ %{HOSTNAME:hostname}",
                "Query_time: %{NUMBER:queryTime:float}",
                "Rows_sent: %{NUMBER:rowsSent:int}"
            ]
        }
    }

etc.

Vladpov · November 10, 2019, 5:45pm

Thanks I didn't get the meaning of filebeat multiline input. I thought it would make one line from several lines but it just gives rule into the struture and informs logstash that there is multiline event coming?

Thank you for your patience !

Badger · November 10, 2019, 5:50pm

I do not think I can explain multiline handling in filebeat any better than the documentation does. It combines multiple lines into the message field a single event (separated by newline characters).

system · December 8, 2019, 5:51pm

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Mysql slow log Logstash	2	174	October 24, 2023
Use grok to filter mysql slow-queries Logstash	2	1159	March 20, 2019
Filebeat not sending/error sending data to logstash Beats filebeat	1	211	October 6, 2022
Problem with logstash handling the mysql-slow.log Logstash	3	1318	January 17, 2017
MySQL log analysis with Filebeat Logstash and Elasticsearch Logstash	1	331	December 3, 2019

Logstash parsing mysql-slow.log

Related Topics