During my time as a performance engineer who was also involved in operations, I wished AWS had the ability to query Cloudwatch metrics for various AWS services using a SQL-like query language. However, it is never too late to have such an option.
AWS recently introduced a SQL-like capability for querying metrics data in CloudWatch. It is part of the Cloudwatch Metrics Insight feature. No, I'm not referring to the ability to query logs. This is used to query Cloudwatch metrics data. This feature is currently in preview mode.
I haven't used it much except for cost optimization work, but given my previous struggles with visualising metrics data, the SQL capability is a useful feature to have. For example, previously, if I wanted to show only the top 10 EBS volumes based on the amount of data written to them, I would have to query them using the AWS SDK. Other options would have been to use the AWS console, add all the volumes to a dashboard, and then filter them one by one. A time-consuming process to follow. With SQL, however, I can simply execute the following code to obtain the top ten volumes with the most data written to them:
SELECT SUM(VolumeWriteBytes) FROM SCHEMA("AWS/EBS", VolumeId) GROUP BY VolumeId ORDER BY SUM() DESC limit 10
Here is the visualization of executing the above query in Cloudwatch using the query editor.
To access the SQL editor/query builder, navigate to CloudWatch page and select "All Metrics" under Metrics header.
Here's another example, If you simply want to see the top 5 EC2 instances ranked by highest CPU consumption, run the following Cloudwatch query.
SELECT AVG(CPUUtilization) FROM SCHEMA("AWS/EC2", InstanceId) GROUP BY InstanceId ORDER BY AVG() DESC limit 5
I started using this feature when it was introduced late last year to help me with cost optimization activities I was doing.
Since this functionality is in preview mode, there are few limitation that you need to be aware off.
You can query only the most recent three hours of data.
A single query can process no more than 10,000 metrics.
A single query can return no more than 500 time series.
Each GetMetricData operation can have only one query, but you can have multiple widgets in a dashboard that each include a query.
I wish AWS had had this option 5 years ago. Life would have been so much easier. If you want to learn more about this feature, please refer to the following links:
Top comments (0)