In this article, I will describe how to use Athena and QuickSight to make BI/DataViz of your current Loadbalancer Access Logs on AWS: ELB and ALB.
From AWS: “Amazon Athena is an interactive query service that makes it easy to analyze data in Amazon S3 using standard SQL. Athena is serverless, so there is no infrastructure to manage, and you pay only for the queries that you run.”
In few minutes, you will be able to query your access log, even if you have a huge amount of logs with this serverless service. I will show you how to use AWS DataViz product called QuickSight to get some great visuals.
In my case, Athena ran over 250GB of sample logs in only 50s.
Loadbalancer: Enable Access Logs
First, you’ll need to activate access logs on your loadbalancers. These logs will be stored in S3 bucket of your choice.
Load Balancers ->
Edit attributes ->
Athena: Create Database and table
To begin, go the AWS Athena, then create
Then, we will use the following SQL command to create the proper Athena table.
Choice the one corresponding to your actual logs:
Don’t forget to edit the last line with the
location of your S3 bucket.
|ref: ELB Docs||ALB Docs|
Athena: Run Queries
You will find in this section few examples of what you can get with standard SQL queries.
This query will show you the number of the same IP requesting your backend with HTTP response code 200:
This one will show you which company behind a proxy are using on default browser “Firefox”
Find out which company are using macOS by replacing
QuickSight: Create your visuals
Create your QuickSight account
In your AWS Console, Go to
QuickSight and follow the quick sign-up process.
Create your dataset
Athena dataset type and choice your Athena DB and your ELB/ALB table.
Create visuals / restitution
You will need to play with visuals, different graph type to meet your goals, it could take some time, but for example, you can get this kind of restitution:
That’s all folks, I hope you enjoyed this quick howto.
Don’t hesitate to ping me with any question.