{"id":65,"date":"2024-11-29T21:55:45","date_gmt":"2024-11-29T21:55:45","guid":{"rendered":"https:\/\/mybuddyhoward.com\/?p=65"},"modified":"2024-11-29T22:01:17","modified_gmt":"2024-11-29T22:01:17","slug":"query-aws-load-balancer-logs-over-80-queries-from-my-book-for-free","status":"publish","type":"post","link":"https:\/\/mybuddyhoward.com\/?p=65","title":{"rendered":"Query AWS Load Balancer Logs: Over 80 Queries from My Book for FREE!"},"content":{"rendered":"\n

In my previous blog post, I shared the story behind writing and publishing my first book. Today, I want to extend some of that hard work to the world, free of charge. I’ve decided to publish all of the example queries from my book in this blog post. Obviously, incorporating the entire content of the book into a blog would lead to an insane amount of scrolling. Therefore, I will focus on sharing the core elements. In my book, each SQL query is accompanied by helpful explanations and tips\u2014details that I can’t fully replicate in this blog post. Nonetheless, I believe these SQL queries will provide a solid starting point for anyone embarking on their journey to analyze AWS load balancer logs using Amazon Athena. For those who find themselves deep in the trenches and in need of more comprehensive details for each example, my book may be worth exploring. Check out my book here: https:\/\/a.co\/d\/9x8YPLF<\/a>.

So, without further ado, let’s dive into the examples.<\/p>\n\n\n\n

<\/h3>\n\n\n\n
\n\n\n\n

1. Analyzing High-Latency Targets<\/strong><\/h3>\n\n\n\n

Category:<\/strong> Performance Optimization
Objective:<\/strong> Identify targets with the highest average response time to help optimize backend performance.<\/p>\n\n\n\n

SELECT target_ip, AVG(response_processing_time) AS avg_latency\nFROM alb_logs\nGROUP BY target_ip\nORDER BY avg_latency DESC\nLIMIT 10;<\/code><\/pre>\n\n\n\n
Application:<\/strong>
Focus on these targets for performance optimization to reduce latency and improve user experience.<\/p>\n\n\n\n
\n\n\n\n
2. Detecting Frequent 5xx Errors<\/strong><\/h3>\n\n\n\n
Category:<\/strong> Error Analysis
Objective:<\/strong> Identify the top 5 targets generating 5xx errors.<\/p>\n\n\n\n
SELECT target_ip, COUNT(*) AS error_count\nFROM alb_logs\nWHERE elb_status_code LIKE '5%'\nGROUP BY target_ip\nORDER BY error_count DESC\nLIMIT 5;<\/code><\/pre>\n\n\n\n
Application:<\/strong>
Fix issues with these targets to enhance reliability and reduce downtime.<\/p>\n\n\n\n
\n\n\n\n
3. Identifying Top Referrers<\/strong><\/h3>\n\n\n\n
Category:<\/strong> Business Insights
Objective:<\/strong> List top 10 referrers generating traffic.<\/p>\n\n\n\n
SELECT referrer, COUNT(*) AS traffic_count\nFROM alb_logs\nGROUP BY referrer\nORDER BY traffic_count DESC\nLIMIT 10;\n<\/code><\/pre>\n\n\n\n
Application:<\/strong>
Understand referral sources and focus on strengthening partnerships or campaigns.<\/p>\n\n\n\n
\n\n\n\n
4. Classifying Request Types<\/strong><\/h3>\n\n\n\n
Category:<\/strong> Business Insights
Objective:<\/strong> Count requests based on HTTP methods (GET, POST, etc.).<\/p>\n\n\n\n
SELECT request_verb, COUNT(*) AS request_count\nFROM alb_logs\nGROUP BY request_verb\nORDER BY request_count DESC;\n<\/code><\/pre>\n\n\n\n
Application:<\/strong>
Evaluate API usage patterns for optimization and scaling.<\/p>\n\n\n\n
\n\n\n\n
5. Tracking Large Downloads<\/strong><\/h3>\n\n\n\n
Category:<\/strong> Security & Compliance
Objective:<\/strong> Identify responses with high sent_bytes<\/code>.<\/p>\n\n\n\n
SELECT request_url, sent_bytes\nFROM alb_logs\nWHERE sent_bytes > (SELECT approx_percentile(sent_bytes, 0.99) FROM alb_logs)\nORDER BY sent_bytes DESC;\n<\/code><\/pre>\n\n\n\n
Application:<\/strong>
Spot potential unauthorized data downloads or performance bottlenecks.<\/p>\n\n\n\n
\n\n\n\n
6. Peak Hour Analysis<\/strong><\/h3>\n\n\n\n
Category:<\/strong> Performance Optimization
Objective:<\/strong> Determine hours with the most traffic.<\/p>\n\n\n\n
SELECT HOUR(time) AS hour, COUNT(*) AS request_count\nFROM alb_logs\nGROUP BY HOUR(time)\nORDER BY request_count DESC;\n<\/code><\/pre>\n\n\n\n
Application:<\/strong>
Prepare for peak loads by scaling resources during high-traffic periods.<\/p>\n\n\n\n
\n\n\n\n
7. Monitoring Target Utilization<\/strong><\/h3>\n\n\n\n
Category:<\/strong> Business Insights
Objective:<\/strong> Find the top 5 most accessed targets.<\/p>\n\n\n\n
SELECT target_ip, COUNT(*) AS hit_count\nFROM alb_logs\nGROUP BY target_ip\nORDER BY hit_count DESC\nLIMIT 5;\n<\/code><\/pre>\n\n\n\n
Application:<\/strong>
Balance traffic to prevent overloading critical resources.<\/p>\n\n\n\n
\n\n\n\n
8. Identifying Long-Lived Sessions<\/strong><\/h3>\n\n\n\n
Category:<\/strong> Performance Optimization
Objective:<\/strong> Find sessions with extended durations.<\/p>\n\n\n\n
SELECT session_id, MAX(session_duration) AS max_duration\nFROM alb_logs\nGROUP BY session_id\nORDER BY max_duration DESC\nLIMIT 10;\n<\/code><\/pre>\n\n\n\n
Application:<\/strong>
Optimize resource usage for long sessions.<\/p>\n\n\n\n
\n\n\n\n
9. Average Payload Size<\/strong><\/h3>\n\n\n\n
Category:<\/strong> Business Insights
Objective:<\/strong> Calculate the average size of requests.<\/p>\n\n\n\n
SELECT AVG(received_bytes) AS avg_request_size\nFROM alb_logs;\n<\/code><\/pre>\n\n\n\n
Application:<\/strong>
Adjust payload size limits to align with typical usage.<\/p>\n\n\n\n
\n\n\n\n
10. Request Patterns by Day<\/strong><\/h3>\n\n\n\n
Category:<\/strong> Business Insights
Objective:<\/strong> Analyze request volume trends over days.<\/p>\n\n\n\n
SELECT DATE(time) AS request_date, COUNT(*) AS request_count\nFROM alb_logs\nGROUP BY request_date\nORDER BY request_date;\n<\/code><\/pre>\n\n\n\n
Application:<\/strong>
Plan maintenance and campaigns around high-traffic days.<\/p>\n\n\n\n
\n\n\n\n
11. TLS Protocol Distribution<\/strong><\/h3>\n\n\n\n
Category:<\/strong> Security & Compliance
Objective:<\/strong> Count requests based on SSL\/TLS protocols.<\/p>\n\n\n\n
SELECT ssl_protocol, COUNT(*) AS protocol_count\nFROM alb_logs\nGROUP BY ssl_protocol\nORDER BY protocol_count DESC;\n<\/code><\/pre>\n\n\n\n
Application:<\/strong>
Identify and deprecate outdated protocols for improved security.<\/p>\n\n\n\n
\n\n\n\n
12. Client Browser Distribution<\/strong><\/h3>\n\n\n\n
Category:<\/strong> Business Insights
Objective:<\/strong> Determine which browsers your users are using.<\/p>\n\n\n\n
SELECT user_agent, COUNT(*) AS usage_count\nFROM alb_logs\nGROUP BY user_agent\nORDER BY usage_count DESC\nLIMIT 10;\n<\/code><\/pre>\n\n\n\n
Application:<\/strong>
Prioritize compatibility testing for popular browsers.<\/p>\n\n\n\n
\n\n\n\n
13. Analyzing Redirects<\/strong><\/h3>\n\n\n\n
Category:<\/strong> Error Analysis
Objective:<\/strong> List the most common redirect URLs.<\/p>\n\n\n\n
SELECT redirect_url, COUNT(*) AS redirect_count\nFROM alb_logs\nGROUP BY redirect_url\nORDER BY redirect_count DESC\nLIMIT 10;\n<\/code><\/pre>\n\n\n\n
Application:<\/strong>
Optimize redirect rules to improve user navigation.<\/p>\n\n\n\n
\n\n\n\n
14. Unusual Response Times<\/strong><\/h3>\n\n\n\n
Category:<\/strong> Performance Optimization
Objective:<\/strong> Detect requests with extreme response times.<\/p>\n\n\n\n
SELECT *\nFROM alb_logs\nWHERE response_processing_time > (SELECT approx_percentile(response_processing_time, 0.99) FROM alb_logs);\n<\/code><\/pre>\n\n\n\n
Application:<\/strong>
Investigate and resolve outliers causing delays.<\/p>\n\n\n\n
\n\n\n\n
15. Finding Top File Uploads<\/strong><\/h3>\n\n\n\n
Category:<\/strong> Business Insights
Objective:<\/strong> Identify the largest uploaded files.<\/p>\n\n\n\n
SELECT request_url, received_bytes\nFROM alb_logs\nORDER BY received_bytes DESC\nLIMIT 10;\n<\/code><\/pre>\n\n\n\n
Application:<\/strong>
Spot trends or anomalies in file upload activity.<\/p>\n\n\n\n
\n\n\n\n
16. Regional Traffic Distribution<\/strong><\/h3>\n\n\n\n
Category:<\/strong> Business Insights
Objective:<\/strong> Count traffic by geographical regions.<\/p>\n\n\n\n
SELECT region, COUNT(*) AS request_count\nFROM alb_logs\nGROUP BY region\nORDER BY request_count DESC;\n<\/code><\/pre>\n\n\n\n
Application:<\/strong>
Optimize regional content delivery for better user experience.<\/p>\n\n\n\n
\n\n\n\n
17. Backend Health Check<\/strong><\/h3>\n\n\n\n
Category:<\/strong> Performance Optimization
Objective:<\/strong> Monitor unhealthy target status codes.<\/p>\n\n\n\n
SELECT target_status_code, COUNT(*) AS count\nFROM alb_logs\nGROUP BY target_status_code\nORDER BY count DESC;\n<\/code><\/pre>\n\n\n\n
Application:<\/strong>
Fix failing backend services promptly.<\/p>\n\n\n\n
\n\n\n\n
18. Correlating User Sessions<\/strong><\/h3>\n\n\n\n
Category:<\/strong> Business Insights
Objective:<\/strong> Track the number of requests per session.<\/p>\n\n\n\n
SELECT session_id, COUNT(*) AS request_count\nFROM alb_logs\nGROUP BY session_id\nORDER BY request_count DESC\nLIMIT 10;\n<\/code><\/pre>\n\n\n\n
Application:<\/strong>
Understand user activity and session behavior.<\/p>\n\n\n\n
\n\n\n\n
19. HTTP Version Usage<\/strong><\/h3>\n\n\n\n
Category:<\/strong> Business Insights
Objective:<\/strong> Analyze traffic by HTTP version.<\/p>\n\n\n\n
SELECT http_version, COUNT(*) AS request_count\nFROM alb_logs\nGROUP BY http_version\nORDER BY request_count DESC;\n<\/code><\/pre>\n\n\n\n
Application:<\/strong>
Evaluate HTTP\/2 or HTTP\/3 adoption rates for performance improvements.<\/p>\n\n\n\n
\n\n\n\n
20. Frequent Error Reasons<\/strong><\/h3>\n\n\n\n
Category:<\/strong> Error Analysis
Objective:<\/strong> List top error reasons from requests.<\/p>\n\n\n\n
SELECT error_reason, COUNT(*) AS error_count\nFROM alb_logs\nGROUP BY error_reason\nORDER BY error_count DESC\nLIMIT 10;\n<\/code><\/pre>\n\n\n\n
Application:<\/strong>
Address common causes of request failures to enhance reliability.<\/p>\n\n\n\n
I hope these examples give you a solid starting point for exploring the power of Amazon Athena in analyzing AWS ALB logs. Whether you’re optimizing performance, troubleshooting errors, or gaining business insights, these queries can help you uncover valuable patterns and actionable data from your logs.<\/p>\n\n\n\n
If you find these examples helpful and want to dive deeper, my book, Analyzing AWS Load Balancer Logs: A Quick Guide with Over 80 Essential Queries Using Amazon Athena<\/a><\/em>, offers a comprehensive collection of strategies and tips to take your log analysis skills to the next level.<\/p>\n\n\n\n
As always, I\u2019d love to hear your feedback, questions, or any creative ways you\u2019ve used these queries in your projects. Feel free to leave a comment below or connect with me.<\/p>\n\n\n\n
\n\n\n\n\n\n\t
\n\t\t
Leave a Reply Cancel reply<\/a><\/small><\/h3>
Your email address will not be published.<\/span> Required fields are marked *<\/span><\/span><\/p>