AWS Cloudwatch Log Insights query using Regex does not extract columns for MySql Slow Query Logs

Question

When I run the following query in AWS Cloudwatch Log Insights I get blank output

parse @message /Query_time: (?<Query_time>[0-9](\.[0-9]+)?), Lock_time: (?<Lock_time>[0-9](\.[0-9]+)?), (?<Query>(?<=\;)(.*?)(?=\;))/ 

but if I run it each colume individualy it does extract the values

parse @message /Query_time: (?<Query_time>[0-9](\.[0-9]+)?)/

# User@Host: ****[****] @ localhost [] Id: 10
# Query_time: 0.000283 Lock_time: 0.000075 Rows_sent: 1 Rows_examined: 1
SET timestamp=1589784518; SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'TIME_SINCE_ZERO_CONNECTIONS';

Is there something I am missing

Answer 1

You may use

Query_time:\s*(?<Query_time>[0-9]+(?:\.[0-9]+)?)\s*Lock_time:\s*(?<Lock_time>[0-9]+(?:\.[0-9]+)?)[\s\S]*?;\s*(?<Query>[^;]*)

See the regex demo. Details:

• Query_time: - literal string
• \s* - any 0+ whitespaces
• (?<Query_time>[0-9]+(?:\.[0-9]+)?) - Group "Query_time": a float or int like number
• \s* - any 0+ whitespaces
• Lock_time: - literal string
• \s* - any 0+ whitespaces
• (?<Lock_time>[0-9]+(?:\.[0-9]+)?) - Group "Lock_time": a float or int like number
• [\s\S]*? - any 0+ chars including line break chars, as few as possible
• ; - a semi-colon
• \s* - 0+ whitespaces
• (?<Query>[^;]*) - Group "Query": any 0 or more chars other than ;.

Answer 2

Here's mine, which extracts all the fields:

# fields @message |
parse @message /# Time:\s(?<Time>.*?)\sUser@Host: (?<User>.*)\[.*?\]\s@ \s.(?<Host>.*?). Id: \d*\s# Query_time: (?<Query_time>.*?)\s Lock_time: (?<Lock_time>.*?)\sRows_sent: (?<Rows_sent>.*?)\sRows_examined: (?<Rows_examined>.*?)\s(?<Query>.*)/ 
| sort Query_time desc