CODEWITHSUNDEEP
amazon-cloudwatchamazon-cloudwatchlogsaws-cloudwatch-log-insights
Unbreakable
Unbreakable

Reputation: 8084

How to run a group by in AWS Cloud Watch Logs Insights

I have CWL Entries as below. Showing entries in SQL Type for clarity

Name City
1     Chicago
2     Wuhan
3     Chicago
4     Wuhan
5     Los Angeles

Now I want to get below output

City         Count
Chicago        2
Wuhan          2
Los Angeles    1

Is there a way I can run GROUP BY in CWL Insights.

Pseudo Query

Select Count(*), City From {TableName} GROUP BY City

Upvotes: 32

Views: 74281

Answers (1)

Dejan Peretin
Dejan Peretin

Reputation: 12089

You can use the aggregation function count with the by statement: https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/CWL_QuerySyntax.html

Here is a full example for your case, assuming the logs contain the entries exactly as you have in the example (regex for city name is very simple, you may want to refine that).

fields @timestamp, @message
| parse @message /^(?<number>\d+)\s+(?<city>[a-zA-Z\s]+)$/
| filter ispresent(city)
| stats count(*) by city

Result:

---------------------------
|     city     | count(*) |
|--------------|----------|
| Chicago      | 2        |
| Wuhan        | 2        |
| Los Angeles  | 1        |
---------------------------

Upvotes: 84

Related Questions