In CloudWatch Insights, how do I build a histogram of an aggregation function (second level query)?

Question

I'm not sure I'm asking this correctly which is probably why I can't find the solution. So I'll provide an example.

Suppose I have a log of employees hired by managers in a given time period. I can create a query that groups by manager and shows the number of employees hired

stats count() as numEmployees by managerId
| filter @message like /hired employee/
| sort numEmployees desc

Let's suppose that generates the following table

Mngr | numHires
Jack | 4
Judy | 3
May  | 3
John | 2
Jake | 2
Mary | 1
Sam  | 1
Alan | 1

I'd like to further refine my result so that I can produce another histogram of numHires and count like so

This table means there was 1 instance of 4 hires, 2 instances of 3 hires, 2 instances of 2 hires, and 3 instances of 1 hire.

Is there a way to do this?

ps - I know I can download the csv and do this in excel. However, there is a limit of 10000 results returned in cloudwatch

subquery at cloudwatch insights is not possible, i tried several but aliasing the query(making it sub) and then querying it outside doesn't look possible. — Ersoy, Apr 18 '20 at 11:47

score 1 · Answer 1 · edited Feb 24 '21 at 16:03

I needed to do the same type of aggregation and raise a support case with AWS to ask how this could be done. The response from the AWS team was that unfortunately at the moment it is not possible using Insights.

Insights does not have the capabilities for second level aggregations currently.

So an alternate workaround is to use AWS Quick Sights or MS Excel to plot the required graphs.

In my case Excel is not an option because my the resulting dataset for a day has millions of records. That being said in the end my solution was to sample over just a few minutes of data, export this to Excel, and generate a pivot table to aggregate the data. This allowed me to get a rough idea of my system.

I have not looked into AWS Quick Sights.

There may be other third-party solutions besides AWS Insights, such as Datadog, that provide more powerful log analysis functionality. I have not used Datadog personally so cannot vouch for it but have read good things about it.

References: [1] https://docs.aws.amazon.com/quicksight/latest/user/histogram-charts.html

In CloudWatch Insights, how do I build a histogram of an aggregation function (second level query)?

1 Answers1