I am looking for a solution to trim multiple white spaces, removing new lines, and a special character "^M" (in my case) from a multiline mysql query (Not multiline mysql-slow.log) in order to get the mysql query into a single line. The scenario is as follows:
- I have a web application using mysql
- I have enabled mysql-slow.log
- I have installed filebeat and enabled the mysql module
- I have loaded the corresponding filebeat mysql ingest pipeline to Elasticsearch (Not using Logstash)
- The multiline logs from mysql-slow.log are parsed properly
- The parsed query is stored in the structured field "mysql.slowlog.query" is stored as a multiline query. Sometimes is stored as a single line but is not always the case.
I need this mysql query to be parsed and stored into the field "mysql.slowlog.query" as a single line query. As an example, from the multiline log below, I want all the parameters in the multiline log parsed into their corresponding field as they are with the exception of "mysql.slowlog.query"
# Time: 2020-08-05T17:14:07.330582Z # User@Host: root[root] @ localhost [127.0.0.1] Id: 45 # Query_time: 0.000290 Lock_time: 0.000138 Rows_sent: 1 Rows_examined: 23 SET timestamp=1596647647; SELECT * FROM accounts ^M WHERE username='jeremy' AND password='password';
I want the field "mysql.slowlog.query" to provide the following:
SELECT * FROM accounts WHERE username='jeremy' AND password='password';
From what I can observe, the loaded ingest pipeline " filebeat-7.6.2-mysql-slowlog-pipeline" extracts the query into the "mysql.slowlog.query" field by using the following grok pattern:
Where the current pattern definitions are set:
"GREEDYMULTILINE": "(.|\n)*", "METRICSPACE": "([ #\n]*)", "EXPLAIN": "(# explain:.*\n|#\\s*\n)*"
- What would be the solution to trim multiple spaces to a single one, new lines, and in my case the "^M" located at the end of the first line?
- Can the proposed solution also work for the following example:
SELECT * FROM `users` WHERE `user_id` IN (SELECT f2.`friend_user_id` FROM `friends` AS f1 INNER JOIN `friends` AS f2 ON f1.`friend_user_id` = f2.`user_id` WHERE f2.`is_page` = 0 AND f1.`user_id` = "%1$d" AND f2.`friend_user_id` != "%1$d" AND f2.`friend_user_id` NOT IN (SELECT `friend_user_id` FROM `friends` WHERE `user_id` = "%1$d")) AND `user_id` NOT IN (SELECT `user_id` FROM `friend_requests` WHERE `friend_user_id` = "%1$d") AND `user_image` IS NOT NULL ORDER BY RAND()
I greatly appreciate your help.