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
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.
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.
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.
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 |
---------------------------
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With