# Logstash filter for slow query logs
**URL:** https://discuss.elastic.co/t/logstash-filter-for-slow-query-logs/370586
**Category:** Logstash
**Created:** [November 15, 2024, 3:22am UTC](https://discuss.elastic.co/t/logstash-filter-for-slow-query-logs/370586 "2024-11-15T03:22:55Z")
**Posts on this page:** 3
**Page:** 1
### Author:  [@FJT](https://discuss.elastic.co/u/FJT)
#### Post date: [November 15, 2024, 3:22am UTC](https://discuss.elastic.co/t/logstash-filter-for-slow-query-logs/370586/1 "2024-11-15T03:22:56Z")
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
```auto
# 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
```auto
"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
```auto
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
```auto
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.
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [November 15, 2024, 2:32pm UTC](https://discuss.elastic.co/t/logstash-filter-for-slow-query-logs/370586/2 "2024-11-15T14:32:53Z")
> [@FJT](#):
>
> ```
> "# 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}"
>
> ```
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+
---
### Author:  [@FJT](https://discuss.elastic.co/u/FJT)
#### Post date: [November 18, 2024, 5:06am UTC](https://discuss.elastic.co/t/logstash-filter-for-slow-query-logs/370586/3 "2024-11-18T05:06:18Z")
Thank you very much this solve my problem 😊