This is a short on my journey to do some simple log processing. My client had a backend issue with a particular request path, and I was able to identify the source of errors and the source of slowness pretty easily with this.<\/p>\n\n\n\n
First, you’ve got to identify where your S3 logs are. This is really easy from the console; Click the load balancer, check the attributes section, and see where it is. <\/p>\n\n\n\n
Alternatively, from the cli:<\/p>\n\n\n\n
# List load balancers\naws elbv2 describe-load-balancers\naws elbv2 describe-load-balancers | jq -r '.LoadBalancers[] | (.LoadBalancerName + \" \" + .LoadBalancerArn)'\n# Look for access_logs.s3.bucket and access_logs.s3.prefix\naws elbv2 describe-load-balancer-attributes --load-balancer-arn ARN\naws elbv2 describe-load-balancer-attributes --load-balancer-arn ARN | jq '.Attributes[] | select( (.Key == \"access_logs.s3.bucket\" or .Key == \"access_logs.s3.prefix\"))'\n<\/code><\/pre>\n\n\n\nOnce you’ve got the location, you can copy it down with<\/p>\n\n\n\n
aws s3 sync s3:\/\/lb-logs\/prefix\/date\/ scratch\/alblogs<\/code><\/pre>\n\n\n\nUnpack and Concat<\/h2>\n\n\n\n
Now you’ve got a big directory full of small logfiles. Unpack them and put them all in one big file for sqlite import:<\/p>\n\n\n\n
zcat * > all.log<\/code><\/pre>\n\n\n\nCreate SQL<\/h2>\n\n\n\n
First, create the schema for your ALBLogs. <\/p>\n\n\n\n
CREATE TABLE IF NOT EXISTS \"alblogs\"(\n \"type\" TEXT, \"time\" TEXT, \"elb\" TEXT, \"client_port\" TEXT,\n \"target_port\" TEXT, \"request_processing_time\" int, \"target_processing_time\" int, \"response_processing_time\" int,\n \"elb_status_code\" TEXT, \"target_status_code\" TEXT, \"received_bytes\" int, \"sent_bytes\" int,\n \"request\" TEXT, \"user_agent\" TEXT, \"ssl_cipher\" TEXT, \"ssl_protocol\" TEXT,\n \"target_group_arn\" TEXT, \"trace_id\" TEXT, \"domain_name\" TEXT, \"chosen_cert_arn\" TEXT,\n \"matched_rule_priority\" TEXT, \"request_creation_time\" TEXT, \"actions_executed\" TEXT, \"redirect_url\" TEXT,\n \"error_reason\" TEXT, \"target_port_list\" TEXT, \"target_status_code_list\" TEXT, \"classification\" TEXT,\n \"classification_reason\" TEXT, \"conn_trace_id\" TEXT,\n \"transformed_host\" TEXT, \"transformed_uri\" TEXT, \"request_transform_status\" TEXT\n);\n<\/code><\/pre>\n\n\n\nThen, import your logfile into a table:<\/p>\n\n\n\n
.separator \" \"\n.import \"all.log\" alblogs<\/code><\/pre>\n\n\n\nFor really useful work, you’ll likely need a regex implementation. Depending on your distro, you might already have one installed. I specifically installed sqlite3-pcre. You activate one like:<\/p>\n\n\n\n
.load \/usr\/lib\/libsqlite3-pcre.so<\/code><\/pre>\n\n\n\nThat’s it! You’re now ready to query your logs.<\/p>\n\n\n\n
select request, target_processing_time from alblogs order by target_processing_time desc limit 10;<\/code><\/pre>\n\n\n\n<\/p>\n","protected":false},"excerpt":{"rendered":"
This is a short on my journey to do some simple log processing. My client had a backend issue with a particular request path, and I was able to identify the source of errors and the source of slowness pretty easily with this. Identify S3 bucket and copy locally First, you’ve got to identify where … Continue reading Sqlite over ALBLogs<\/span>