regex101: mysql slow log parse for aws log insights

# Time:the literal text # Time: (case sensitive)

\sany whitespace character

Group timestamp(?<timestamp>.*)

.*any character (except for line terminators), repeated any number of times

\na line-feed (newline) character (ASCII 10)

#the literal # (35₁₀ / 43₈ / 23₁₆)

.*any character (except for line terminators), repeated any number of times

Id:the literal text Id: (case sensitive)

\sany whitespace character

Group id(?<id>.*)

.*any character (except for line terminators), repeated any number of times

\na line-feed (newline) character (ASCII 10)

#the literal # (35₁₀ / 43₈ / 23₁₆)

\sany whitespace character

Query_time:the literal text Query_time: (case sensitive)

\sany whitespace character

Group duration(?<duration>[\d.]*)

Character Class[\d.]*

*matches any number of characters from the set below:

\da digit

.the literal . (46₁₀ / 56₈ / 2E₁₆)

\sany whitespace character

.*any character (except for line terminators), repeated any number of times

time:the literal text time: (case sensitive)

\sany whitespace character

Group lock_time(?<lock_time>[\d.]*)

Character Class[\d.]*

*matches any number of characters from the set below:

\da digit

.the literal . (46₁₀ / 56₈ / 2E₁₆)

\sany whitespace character

.*any character (except for line terminators), repeated any number of times

_sent:the literal text _sent: (case sensitive)

\sany whitespace character

Group returned(?<returned>\d*)

\d*a digit, repeated any number of times

\sany whitespace character

.*any character (except for line terminators), repeated any number of times

_examined:the literal text _examined: (case sensitive)

\sany whitespace character

Group searched(?<searched>\d*)

\d*a digit, repeated any number of times

\na line-feed (newline) character (ASCII 10)

Group sql(?<sql>[\s\S]*)

Character Class[\s\S]*

*matches any number of characters from the set below:

\sany whitespace character

\Sany non-whitespace character

Global pattern flags

g modifier: global. Finds all matches instead of stopping after the first

m modifier: multiline. Causes ^ and $ to match the start and end of each line, not only the start and end of the string

Search reference

Regular Expression

# Time:\s(?<timestamp>.*)\n#.*Id:\s(?<id>.*)\n#\sQuery_time:\s(?<duration>[\d.]*)\s.*time:\s(?<lock_time>[\d.]*)\s.*_sent:\s(?<returned>\d*)\s.*_examined:\s(?<searched>\d*)\n(?<sql>[\s\S]*)

New

	
# Time: 210607 16:27:16
# User@Host: doctime_db_usr[doctime_db_usr] @ [10.2.0.79] Id: 13021
# Query_time: 20.504504 Lock_time: 0.000233 Rows_sent: 52 Rows_examined: 13827347
SET timestamp=1623083236;
SELECT
substring( substring_index( uri, '/', -1 ) , 1, LENGTH(substring_index( uri, '/', -1 )) - 38 ) AS uri,
namespace,
method,
count(*) AS hits
FROM
user_hits
WHERE
namespace = 'hospitals' AND
controller = 'queue' AND
method = 'file'
GROUP BY
substring( substring_index( uri, '/', -1 ) , 1, LENGTH(substring_index( uri, '/', -1 )) - 38 )
UNION
SELECT
uri,
namespace,
method,
count(*) AS hits
FROM
user_hits
WHERE
uri LIKE '%export%'
GROUP BY
method
ORDER BY hits DESC;