Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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
like image 515
Unbreakable Avatar asked Feb 27 '20 16:02

Unbreakable


People also ask

How do you query log group in CloudWatch?

In the Select log group(s) drop down, choose one or more log groups to query. You can type the name of log groups you want to query in the search bar. When you select a log group, CloudWatch Logs Insights automatically detects data fields in the group.

How do you make a CloudWatch log group?

To create a log groupOpen the CloudWatch console at https://console.aws.amazon.com/cloudwatch/ . In the navigation pane, choose Log groups. Choose Actions, and then choose Create log group. Enter a name for the log group, and then choose Create log group.

How do I run a query in AWS CloudWatch logs?

To run a CloudWatch Logs Insights sample queryIn the navigation pane, choose Logs, and then choose Logs Insights. On the Logs Insights page, the query editor contains a default query that returns the 20 most recent log events. In the Select log group(s) drop down, choose one or more log groups to query.


1 Answers

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        |
---------------------------
like image 178
Dejan Peretin Avatar answered Oct 13 '22 01:10

Dejan Peretin