Create an alarm based on a CloudWatch insight query

Question

My problem:

I would like to blacklist IPs which are accessing my public AWS API Gateway endpoint more than 5 times a hour.

My proposed solution:

• Requests are logged to CloudWatch
• Requests are counted and grouped by IP
• An alarm monitors IPs send a message to a SNS topic in case the threshold is met
• Lambda is triggered by the message and blacklists the IP

I am able to log and count the IPs by using the Insight query below:

fields ip
  | stats count() as ipCount by ip
  | filter ispresent(ip)
  | sort ipCount desc

What I am struggling to accomplish is getting an CloudWatch Alarm based on this query.

I have searched a lot but no success. Any ideas on how to create such a metric / alert?

Answer 1

I know you planned to do a custom Lambda, but check if WAF already fulfills your use case. For example, the rate limit section in this article here clearly allows you to define the rate per 5-minutes for a given IP:

https://docs.aws.amazon.com/waf/latest/developerguide/classic-web-acl-rules-creating.html

If you are not doing anything else, a custom Lambda function may not be needed.

EDIT

If you want to go down the path of CloudWatch alarms, I think you can define a metric filter to create a CloudWatch metric. Then you can create the alarm based on the metric.

https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/MonitoringLogData.html