# Use grok to filter mysql slow-queries

**URL:** <https://discuss.elastic.co/t/use-grok-to-filter-mysql-slow-queries/169248>\
**Category:** Logstash\
**Created:** [February 20, 2019, 3:44pm UTC](https://discuss.elastic.co/t/use-grok-to-filter-mysql-slow-queries/169248 "2019-02-20T15:44:42Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![dank](https://avatars.discourse-cdn.com/v4/letter/d/8dc957/32.png) [@dank](https://discuss.elastic.co/u/dank)\
**Post date:** [February 20, 2019, 3:44pm UTC](https://discuss.elastic.co/t/use-grok-to-filter-mysql-slow-queries/169248/1 "2019-02-20T15:44:42Z")

</div>

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])?](\s_Id:\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!

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [February 20, 2019, 4:17pm UTC](https://discuss.elastic.co/t/use-grok-to-filter-mysql-slow-queries/169248/2 "2019-02-20T16:17:50Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 20, 2019, 4:17pm UTC](https://discuss.elastic.co/t/use-grok-to-filter-mysql-slow-queries/169248/3 "2019-03-20T16:17:55Z")

</div>

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