Some AWS Athena Queries

Image source google

To know

1. Top agent users
2. Top Blocked Rules
3. Top 10 IPs Block count

Amazon Athena is a serverless query service in Amazon Athena that allows you to analyze data in Amazon S3 using SQL. If you’re interested in finding the top users or agents for Athena queries.

Amazon Athena makes it easy to run interactive queries against data directly in Amazon S3 without having to format data or manage infrastructure. Athena is useful if you want to run a quick query on web logs to troubleshoot a performance issue on your site. With Athena, you can get started fast: you just define a table for your data and start querying using standard SQL.

Here some examples

Top agent users

SELECT
httprequest.headers[1].value AS User_Agent,
COUNT(*) AS Request_Count
FROM waf_logs_2024_01
WHERE action = 'BLOCK'
GROUP BY httprequest.headers[1].value
ORDER BY Request_Count DESC
LIMIT 10;

Top Blocked Rules

SELECT
*
FROM waf_logs_2024
WHERE action = 'BLOCK'
LIMIT ALL;

Top 10 IPs block count

SELECT
to_iso8601(from_unixtime(timestamp / 1000)) AS time_ISO_8601,
httprequest.clientip AS Client_IP,
httprequest.country AS Country,
terminatingruleid AS WAF_Rule_ID,
terminatingrulematchdetails AS Match_Details,
httprequest.uri AS URI,
httprequest.httpmethod AS request_method,
labels AS Blocked_Rule
FROM waf_logs_202_01_02
WHERE action = 'BLOCK'
LIMIT all;

Happy Learning! :)

Thanks for reading. I hope you find this article useful.

Don’t forget to follow me to read more articles related to AWS and DevOps.

Shreya Kale

Click here to check the https://www.linkedin.com/in/shreyakale25/