Parsing mySql slow query logs using Logstash

Elastic Stack Logstash
1

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 :

# 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 ?

2

Use mutate+gsub

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

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

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

4

@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 :

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 {}
}
}
5

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 :

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 {}
}
}
6

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

{
          "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';"
}
7

Got that working, thanks

8

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