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

user_584239 0
Viewed 4418

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>(?<=\;)(.*?)(?=\;))/

enter image description here

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

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

enter image description here

# 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

amazon-cloudwatchlogs regex
2 Answers

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 ;.
user_38329700

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
user_49207250
Related