[SOLVED] Sorting by count total in AWS CloudWatch Log Insights when aggregating two fields

Sorting by count total in AWS CloudWatch Log Insights when aggregating two fields

I am trying to create an AWS CloudWatch Log Insights query that will give me the top 5 classes logging error messages from a Java log file. The query below is able to parse my log file format, and provide a count of errors by className.

fields @timestamp, @message, @logStream
| parse @message '* * * [*][*:*] *' as date, time, logLevel, threadName, className, codeLineNumber, logMessage
| filter logLevel == "ERROR"
| stats count(*) by @logStream, className as errorsByClassName
| sort errorsByClassName desc
| limit 5

However, sorting is not working as expected.

Expected outout:

@logStream   || className      || errorsByClassName
server2      || MyClass1       || 1000
server4      || MyOtherClass   || 500
server1      || ARandomClass   || 20

Actual outout:

@logStream   || errorsByClassName || count(*)
server4      || MyOtherClass      || 500
server2      || MyClass1          || 1000
server1      || ARandomClass      || 20

It seems like the as keyword is being applied to the className field and not to the count(*). Modifying the query to only aggregate by a single field does produce the expected results.

How can I set an alias for count when aggregating two fields in order to sort the results?

Thanks!

Solution

You can move the as to next to the stats method, so the correct query should be:

fields @timestamp, @message, @logStream
    | parse @message '* * * [*][*:*] *' as date, time, logLevel, threadName, className, codeLineNumber, logMessage
    | filter logLevel == "ERROR"
    | stats count(*) as errorsByClassName by @logStream, className 
    | sort errorsByClassName desc
    | limit 5