Use grok to filter mysql slow-queries

Hello all,
I'm having a hard time to filter my MySQL slow-queries using logstash (version 6.6.1).
I'm using the following grok filter :
input {
file {
path => ["/tmp/testFile.txt"]
type => "mysql"
codec => multiline {
pattern => "^# User@Host:"
negate => true
what => previous
}
}
}

filter {
grok {
match => { "message" => ["^# User@Host: %{USER:[mysql][slowlog][user]}([[^]]+])? @ %{HOSTNAME:[mysql][slowlog][host]} [(IP:[mysql][slowlog][ip])?](\sId:\s %{NUMBER:[mysql][slowlog][id]})?\n# Query_time: %{NUMBER:[mysql][slowlog][query_time][sec]}\s* Lock_time: %{NUMBER:[mysql][slowlog][lock_time][sec]}\s* Rows_sent: %{NUMBER:[mysql][slowlog][rows_sent]}\s* Rows_examined: %{NUMBER:[mysql][slowlog][rows_examined]}\n(SET timestamp=%{NUMBER:[mysql][slowlog][timestamp]};\n)?%{GREEDYMULTILINE:[mysql][slowlog][query]}"] }
pattern_definitions => {
"GREEDYMULTILINE" => "(.|\n)*"
}
remove_field => "message"
}

    date {
    match => [ "[mysql][slowlog][timestamp]", "UNIX" ]
  }
 mutate {
    gsub => ["[mysql][slowlog][query]", "\n# Time: [0-9]+ [0-9][0-9]:[0-9][0-9]:[0-9][0-9](\\.[0-9]+)?$", ""]
  }

}

But I'm getting tags"=>["_grokparsefailure"] in all of my tests.
For example :
# Time: 190220 15:17:04
# User@Host: user[user1] @ internal [23.22.21.25] Id: 439
# Query_time: 2.274021 Lock_time: 0.000138 Rows_sent: 12 Rows_examined: 274714
SET timestamp=1550675824;
SELECT * from TEST1;

Could you please assist in figuring out what is the issue here?
Thank you very much!

Use two windows. In one, run logstash with the -r option, so that it restarts every time the configuration is changed. In the other, run an editor and start with a configuration like

input { file { path => "/home/user/foo.txt" sincedb_path => "/dev/null" start_position => "beginning" codec => multiline { pattern => "^# User@Host:" negate => true what => "previous" auto_flush_interval => 2 } } }
filter {
        grok { match => { "message" => ["^# User@Host: %{USER:[mysql][slowlog][user]}" }
}
output { stdout { codec => rubydebug { metadata => false } } }

Then add one field at a time to the grok pattern and write the configuration out so that logstash re-reads it. Keep going until it breaks. Then fix the pattern that broke. The first place yours breaks is ''(IP:[mysql][slowlog][ip])?". I think you want %{(IP:[mysql][slowlog][ip])}. If you need that field to be optional you could use a second pattern.

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