Logstash filter for slow query logs

Elastic Stack Logstash
1

Hello can you help me and I am new to ELK.

I am using a filebeat to send to logstash. and i am trying to parse my slow query log can you help me in creating a grok pattern. So what I want to achieve was to have a separate field for user, query_time and query itself.

sample slow logs in my slow_logfile

# Time: 2024-11-15T02:12:24.456514Z
# User@Host: root[root] @ localhost []  Id:     8
# Query_time: 4.000228  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1731636740;
select sleep(4);

sample of what I am currently getting in my logs at elasticsearch or kibana

  "message": [
    "# Time: 2024-11-15T02:44:54.419661Z\n# User@Host: root[root] @ localhost []  Id:     9\n# Query_time: 5.000233  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1\nSET timestamp=1731638689;\nselect sleep(5);"
  ],
  "message.keyword": [
    "# Time: 2024-11-15T02:44:54.419661Z\n# User@Host: root[root] @ localhost []  Id:     9\n# Query_time: 5.000233  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1\nSET timestamp=1731638689;\nselect sleep(5);"
  ],

my multiline that I use in filebeat.yml

filebeat.inputs:
- type: log
  enabled: true
  paths:
    - /var/log/mysql/mysqld-slow.log
  fields:
    log_type: mysql_slow  # For MySQL slow query logs
    tags: ["mysql_slow", "10.201.5.20"]
  fields_under_root: true
  multiline.type: pattern
  multiline.pattern: '^# Time: '
  multiline.negate: true
  multiline.match: after

then my filter in logstash.conf

else if [log_type] == "mysql_slow" {
    grok {
      match => {
        # This pattern captures timestamp, user, query duration, and the actual SQL query.
        "message" => [
          "# Time: %{TIMESTAMP_ISO8601:timestamp}\\n# User@Host: %{WORD:user}\\[%{WORD:username}\\] @ %{HOSTNAME:host}\\s*\\[%{NOTSPACE}\\]\\s*Id: %{NUMBER:id}\\n# Query_time: %{NUMBER:query_time}  Lock_time: %{NUMBER:lock_time} Rows_sent: %{NUMBER:rows_sent}  Rows_examined: %{NUMBER:rows_examined}\\nSET timestamp=%{NUMBER:set_timestamp};\\n%{GREEDYDATA:query}"
        ]
      }
    }
    date {
      match => [ "timestamp", "yyyy-MM-dd HH:mm:ss" ]
      target => "@timestamp"
    }
    mutate { add_tag => ["log_type_present", "mysql_slow_log"] }
  }

But yes that was not working. Can you help me please.

2

Change this to

"(?m)# Time: %{TIMESTAMP_ISO8601:timestamp}\n# User@Host: %{WORD:user}\[%{WORD:username}\] @ %{HOSTNAME:host}\s*\[%{DATA}\]\s*Id:\s+%{NUMBER:id}\n# Query_time: %{NUMBER:query_time}  Lock_time: %{NUMBER:lock_time} Rows_sent: %{NUMBER:rows_sent}  Rows_examined: %{NUMBER:rows_examined}\nSET timestamp=%{NUMBER:set_timestamp};\n%{GREEDYDATA:query}"

Remove one level of escaping for all your backslashes. Use (?m) to do a multiline match. There is nothing between the square brackets so [%{NOTSPACE}] does not match, replace NOTSPACE with DATA. You have multiple spaces between Id: and the value, so use \s+

3

Thank you very much this solve my problem :blush: