# Parsing mySql slow query logs using Logstash

**URL:** <https://discuss.elastic.co/t/parsing-mysql-slow-query-logs-using-logstash/275518>\
**Category:** Logstash\
**Created:** [June 10, 2021, 6:35am UTC](https://discuss.elastic.co/t/parsing-mysql-slow-query-logs-using-logstash/275518 "2021-06-10T06:35:46Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![learningelk](https://avatars.discourse-cdn.com/v4/letter/l/3d9bf3/32.png) [@learningelk](https://discuss.elastic.co/u/learningelk)\
**Post date:** [June 10, 2021, 6:35am UTC](https://discuss.elastic.co/t/parsing-mysql-slow-query-logs-using-logstash/275518/1 "2021-06-10T06:35:46Z")

</div>

I want to parse the mysql slow query logs using logstash and I want to replace modifieddate \> '2021-06-08 08:13:48' with some generix text like modifieddate \> 'NNNN'

Below is the log sample :

```auto
# Time: 2021-06-10T06:30:36.160482Z
# User@Host: mosaicpreprodmysql[mosaicpreprodmysql] @ [10.63.21.201] Id: 2563675
# Query_time: 1.045809 Lock_time: 0.000099 Rows_sent: 1 Rows_examined: 1696142
use tags_test;
SET timestamp=1623306635;
Select t.name as name, t.id as tag_id, t.ownerid as customer_id, tr.resourceid as id, tr.modifieddate as modifieddate, t.isdeleted as is_tag_deleted, tr.isdeleted as is_deleted from tag_resource tr join tag t on t.id = tr.tagid where tr.modifieddate > '2021-06-08 08:13:48' ORDER BY tr.modifieddate;

```

How can this be done ?

---

<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:** [June 10, 2021, 3:12pm UTC](https://discuss.elastic.co/t/parsing-mysql-slow-query-logs-using-logstash/275518/2 "2021-06-10T15:12:06Z")

</div>

> [@learningelk](#):
>
> I want to replace modifieddate \> '2021-06-08 08:13:48' with some generix text like modifieddate \> 'NNNN'

Use mutate+gsub

```
mutate { gsub => ["message", "\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}", "NNNN"] }

```

---

<div class="post-metadata">

**Author:** ![learningelk](https://avatars.discourse-cdn.com/v4/letter/l/3d9bf3/32.png) [@learningelk](https://discuss.elastic.co/u/learningelk)\
**Post date:** [June 10, 2021, 3:37pm UTC](https://discuss.elastic.co/t/parsing-mysql-slow-query-logs-using-logstash/275518/3 "2021-06-10T15:37:37Z")

</div>

Thanks for the reply.  
Will this get the output as below :

```auto
Select t.name as name, t.id as tag_id, t.ownerid as customer_id, tr.resourceid as id, tr.modifieddate as modifieddate, t.isdeleted as is_tag_deleted, tr.isdeleted as is_deleted from tag_resource tr join tag t on t.id = tr.tagid where tr.modifieddate > 'NNNN' ORDER BY tr.modifieddate;

```

Also are the mutate and filter patterns specific to versions of logstash cause I am tring this with logstash version 7.11.1 but the grok is not working for me

---

<div class="post-metadata">

**Author:** ![learningelk](https://avatars.discourse-cdn.com/v4/letter/l/3d9bf3/32.png) [@learningelk](https://discuss.elastic.co/u/learningelk)\
**Post date:** [June 11, 2021, 10:29am UTC](https://discuss.elastic.co/t/parsing-mysql-slow-query-logs-using-logstash/275518/4 "2021-06-11T10:29:45Z")

</div>

@Badger I managed to parse the logs however the complete config file is not working , the groks I created for each line works fine but when I combine them , it doesn't work, here is the configuration file :

```auto
input {
    stdin {
    }
}

filter {

    grok {    
        match => { "message" => "
          "^# Time:%{SPACE}%{TIMESTAMP_ISO8601:mysql_slow_querydate}\n
          ^# User@Host: (?:%{USERNAME:mysql_slow_clientuser})\[(?:%{DATA:mysql_cluster})\] @ %{SPACE}\[(?:%{DATA:mysql_slow_clientip})\]%{SPACE}Id:%{SPACE}%{NUMBER:mysql_slow_id}\n
          ^# Query_time: %{NUMBER:mysql_slow_querytime:float}(?:%{SPACE})Lock_time: %{NUMBER:mysql_slow_locktime:float}(?:%{SPACE})Rows_sent: %{NUMBER:mysql_slow_rowssent:int}(?:%{SPACE})Rows_examined: %{NUMBER:mysql_slow_rowsexamined:int}\n
          %{WORD} %{WORD:mysql_db};\n
          SET timestamp=%{NUMBER:mysql_slow_timestamp}\n
          %{GREEDYDATA:mysql_slow_query}"
           }
    }
}

output {
 stdout {
 codec => rubydebug {}
}
}

```

---

<div class="post-metadata">

**Author:** ![learningelk](https://avatars.discourse-cdn.com/v4/letter/l/3d9bf3/32.png) [@learningelk](https://discuss.elastic.co/u/learningelk)\
**Post date:** [June 11, 2021, 1:31pm UTC](https://discuss.elastic.co/t/parsing-mysql-slow-query-logs-using-logstash/275518/5 "2021-06-11T13:31:43Z")

</div>

Was able to get all the events in without new line but the grok is not working spcifically in logstash, If I try grok debugger in Kibana it works :

```auto
input {
  file {
    path => "/Users/learnelk/Documents/elk/logstash-7.11.1/event-data/mysql-slow.log"
    start_position => "beginning"
    codec => multiline {
      pattern => "^# Time:"
      negate => true
      what => "previous"
    }
  }
}

filter {

    mutate {
        gsub => ['message', "\n", " "]
    }

    grok {    
        match => ["message" , "%{GREEDYDATA}Time:%{SPACE}%{TIMESTAMP_ISO8601:mysql_slow_querydate} %{GREEDYDATA}User@Host: (?:%{USERNAME:mysql_slow_clientuser})\[(?:%{DATA:mysql_cluster})\] @ %{SPACE}\[(?:%{DATA:mysql_slow_clientip})\]%{SPACE}Id:%{SPACE}%{NUMBER:mysql_slow_id} %{GREEDYDATA}Query_time: %{NUMBER:mysql_slow_querytime:float}(?:%{SPACE})Lock_time: %{NUMBER:mysql_slow_locktime:float}(?:%{SPACE})Rows_sent: %{NUMBER:mysql_slow_rowssent:int}(?:%{SPACE})Rows_examined: %{NUMBER:mysql_slow_rowsexamined:int} %{WORD} %{WORD:mysql_db}; SET timestamp=%{NUMBER:mysql_slow_timestamp}; %{GREEDYDATA:mysql_slow_query};" ]
    }

}

output {
 stdout {
 codec => rubydebug {}
}
}

```

---

<div class="post-metadata">

**Author:** ![learningelk](https://avatars.discourse-cdn.com/v4/letter/l/3d9bf3/32.png) [@learningelk](https://discuss.elastic.co/u/learningelk)\
**Post date:** [June 11, 2021, 1:40pm UTC](https://discuss.elastic.co/t/parsing-mysql-slow-query-logs-using-logstash/275518/6 "2021-06-11T13:40:11Z")

</div>

I am getting below error from above configuration, but the Grok works on grok debugger :

```auto
{
          "host" => "learnelk-mac.local",
      "@version" => "1",
          "path" => "/Users/learnelk/Documents/elk/logstash-7.11.1/event-data/mysql-slow.log",
          "tags" => [
        [0] "multiline",
        [1] "_grokparsefailure"
    ],
    "@timestamp" => 2021-06-11T13:39:02.979Z,
       "message" => "# Time: 2021-06-11T10:02:55.505096Z # User@Host: rdsadmin[rdsadmin] @ localhost [] Id: 1116057 # Query_time: 0.001457 Lock_time: 0.000224 Rows_sent: 5 Rows_examined: 60 SET timestamp=1623405775; select * from information_schema.rds_processlist where command <> 'Sleep';"
}

```

---

<div class="post-metadata">

**Author:** ![learningelk](https://avatars.discourse-cdn.com/v4/letter/l/3d9bf3/32.png) [@learningelk](https://discuss.elastic.co/u/learningelk)\
**Post date:** [June 13, 2021, 5:07pm UTC](https://discuss.elastic.co/t/parsing-mysql-slow-query-logs-using-logstash/275518/7 "2021-06-13T17:07:10Z")

</div>

Got that working, thanks

---

<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:** [July 11, 2021, 5:07pm UTC](https://discuss.elastic.co/t/parsing-mysql-slow-query-logs-using-logstash/275518/8 "2021-07-11T17:07:46Z")

</div>

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