25

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
Dejan Peretin
  • 10,891
  • 1
  • 45
  • 54
Unbreakable
  • 7,776
  • 24
  • 90
  • 171

1 Answers1

68

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        |
---------------------------
Dejan Peretin
  • 10,891
  • 1
  • 45
  • 54
  • Good query! Is there a way to give an alias to count(*), similar to sql where aliasing can be used? – Nida Sep 09 '20 at 01:13
  • 3
    You can use `as` in a similar way you would in sql: https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/CWL_QuerySyntax.html#CWL_QuerySyntax-alias – Dejan Peretin Sep 09 '20 at 07:08
  • 1
    @Nida you just do | stats count(*) as counts by city – Naveen Margan Apr 08 '22 at 07:33
  • is there any way of simple group by query. I mean I do not want the aggregation like count, sum, avg etc. Like taking above example it will only return data grouped by cities. So that I can get all the names of from each city. – Arshad Ali Jun 14 '22 at 09:53
  • 1
    You can do the aggregation and the just drop the numbers. Like this: https://stackoverflow.com/questions/72309353/how-to-show-only-unique-rows-in-cloudwatch-output/72330000#72330000 – Dejan Peretin Jun 15 '22 at 06:04