Search code examples
regexamazon-cloudwatchlogs

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

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

Solution

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