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!