TOP SQL Query

No.PurposeCommands
1Find slow query with query_time greater than 0.250(s) and sort descparse @message “Query_time: * Lock_time: * Rows_sent: * Rows_examined: *\n*” as Query_time,Lock_time,Rows_sent,Rows_examined,q
| filter Query_time > 0.250
| sort Query_time desc
2Find slowest write queriesparse @message /Query_time: (?<queryTime>.*?) Lock_time: (?<lockTime>.*?) Rows_sent: (?<rowsSent>.*?) Rows_examined: (?<rowsExamined>.*?)\s(?<query>.*?)$/
| filter @message like /(?i)insert/
| sort queryTime desc
| limit 10
3Find slowest read queriesparse @message /Query_time: (?<queryTime>.*?) Lock_time: (?<lockTime>.*?) Rows_sent: (?<rowsSent>.*?) Rows_examined: (?<rowsExamined>.*?)\s(?<query>.*?)$/
| filter @message like /(?i)select/
| sort queryTime desc
| limit 10
4Number of slow queries per hourparse @message /Query_time: (?<queryTime>.+?) /
| stats count() as count by bin(1h) as hour
5Number of slow queries per dayparse @message /Query_time: (?<queryTime>.+?) /
| stats count() as count by bin(1d) as day
6Averge of slow query duration per dayparse @message /Query_time: (?<queryTime>.+?) /
| stats avg(queryTime) as avg by bin(1d) as day
7Max slow query duration per dayparse @message /Query_time: (?<queryTime>.+?) /
| stats max(queryTime) as max by bin(1d) as day
8Summary stats for query time per hourparse @message /Query_time: (?<queryTime>.+?) /
| stats count() as count, max(queryTime) as max, avg(queryTime) as avg by bin(1h) as hour
9Summary stats of slow write queries by dayparse @message /Query_time: (?<queryTime>.+?) /
| filter @message like /(?i)insert/
| stats count() as count, max(queryTime) as max, avg(queryTime) as avg by bin(1d) as day
10Summary stats of slow read queries by dayparse @message /Query_time: (?<queryTime>.+?) /
| filter @message like /(?i)select/
| stats count() as count, max(queryTime) as max, avg(queryTime) as avg by bin(1d) as day
11Summary stats of slow write queries by tablefilter @message like /(?i)insert/
| parse @message /(?i)# Query_time: (?<queryTime>.*?) [\s\S]*insert into `?(?<tableName>.*)`?\(?[\s\S]*/
| stats count() as count, max(queryTime) as max, avg(queryTime) as avg by tableName

Basic Query

fields @timestamp, ip, resourcePath, status, errorMessage 
| filter status~= /4\d\d/
| sort @timestamp desc
| limit 80

Filter

Boolean

fields f1, f2, f3 | filter (duration>2000)

And & Or & Not

filter @message ~= 'MIDDLEWARE' and @message not like 'iOS'

filter statusCode in [300,400,500]
filter statusCode not in [200,300]

fields @timestamp, @message
| filter @message like /(?i)error|(?i)exception/

Average

parse @message "user=*, method:*, latency := *" as @user, @method, @latency 
| stats avg(@latency) by @method, @user

LogLevel

aws logs filter-log-events 
--log-group-name /aws/lambda/directory-service-worker-update-user-product \
--filter-pattern '[timestamp,logId,logLevel=ERROR,msg]' \
--start-time 1604658473982 \
--end-time 1604658533982  # 1 min from start time

Remove Null Records

fields ispresent(`headers.X-User-Id`) as exists
| filter exists != 0

Group By

fields @timestamp, deviceInfo.thingName as thingName, @message, @logStream
| filter @message like 'ERROR'
| sort @timestamp desc
| stats count (*) by thingName

Starts With

filter `headers.X-User-Id` =~ 'ab7'

Contains

filter @message like /ab735336-fad7-4df9-9576-48143cb0d6da/
# OR
filter @message ~= 'ab735336-fad7-4df9-9576-48143cb0d6da'

Exact Match

filter `headers.X-User-Id` = 'ab735336-fad7-4df9-9576-48143cb0d6da'

Finding Text or ERROR

fields @timestamp, `context.userAgent` as `User-Agent`, @message, @logStream
| filter @message like 'ERROR' | sort @timestamp desc

Specific Services

LAMBDA

@timestamp, @logStream, @message, @requestId, @duration, @billedDuration, @type, @maxMemoryUsed, @memorySize

Latency

fields @timestamp, @message, @duration as duration | filter duration > 1000

Process Exited

filter @message like /Process exited/
| stats count() by bin(30m)

Lambda Timeout Count

filter @message like /Task timed out/
| stats count() by bin(30m)

Memory!

filter @type = "REPORT"
| stats max(@memorySize / 1024 / 1024) as provisonedMemoryMB,
    min(@maxMemoryUsed / 1024 / 1024) as smallestMemoryRequestMB,
    avg(@maxMemoryUsed / 1024 / 1024) as avgMemoryUsedMB,
    max(@maxMemoryUsed / 1024 / 1024) as maxMemoryUsedMB,
    provisonedMemoryMB - maxMemoryUsedMB as overProvisionedMB

ROUT53

@timestamp, @logStream, @message, edgeLocation, hostZoneId, protocol, queryName, queryTimestamp, queryType, resolverIp, responseCode, version

Other log types

@timestamp, @ingestionTime, @logStream, @message, @log.

Parse

Extracts data from a log field and creates one or more ephemeral fields

Using this single log line as an example:

25 May 2019 10:24:39,474 [ERROR] {foo=2, bar=data} The error was: DataIntegrityException

level has a value of ERROR, config has a value of {foo=2, bar=data}, and exception has a value of DataIntegrityException.

fields @message
| parse @message "[*] * The error was: *" as level, config, exception

Stats & Report

Latency Report Per Hour

filter @type = "REPORT" 
| stats avg(@duration), max(@duration), min(@duration) by bin(60m)

Exception Per Hour

Get a list of the number of exceptions per hour.

filter @message like /Exception/
| stats count(*) as exceptionCount by bin(1h)
| sort exceptionCount desc

Lambda Duration

filter @type = "REPORT"
| stats
    avg(@billedDuration) as Average,
    percentile(@billedDuration, 99) as NinetyNinth,
    percentile(@billedDuration, 95) as NinetyFifth,
    percentile(@billedDuration, 90) as Ninetieth
    by bin(30m)

Duration Report for Timeout

filter @type = "REPORT" |
stats avg(@duration), max(@duration), min(@duration) by bin(5m)
| filter @duration >  5900

Mix

fields @message
| parse @message "* [*] *" as loggingTime, loggingType, loggingMessage
| filter loggingType IN ["ERROR", "INFO"]
| display loggingMessage, loggingType = "ERROR" as isError