In this tutorial, you'll learn how to query Amazon VPC flow logs in S3 using Amazon AWS Athena service, CREATE TABLE, ALTER TABLE and SELECT queries.

Store VPC Flow Logs In S3: [ Ссылка ]
AWS User Guide: [ Ссылка ]

Amazon Virtual Private Cloud flow logs capture information about the IP traffic going to and from network interfaces in a VPC. Use the logs to investigate network traffic patterns and identify threats and risks across the VPC network.

-
Video Transcript:
_
Hi guys, this is Abhi from Gokcedb. In this video, you're going to learn how to query the VPC flow logs using the Athena service in AWS. Let's get into it. Let's start by navigating to the VPC service and then clicking on your VPC.

Here I have two VPCs. I'm going to select the one ending in 876 which is my default VPC. Navigate to the flow logs tab and ensure that you have an S3 destination. Configure your VPC flow log.

Check out my video. I'm also going to leave a link in the description below. Next click on the destination name then navigate inside your lock folder structure until you reach the log.gz files. Now that we have located our VPC flow logs.

Let's head to the Athena service then click on the plus button to start a new query. Next, I'm going to copy the create table statement from this AWS user guide and then update the S3 location. Grab the S3 URI from the properties tab then paste it next to the location in the query editor.

Keep your phone. Remove the date folder structure from the very end then hit the Run command to execute the create table statement. Looks like our query was completed successfully and I see a new table called VPC underscore flow underscore logs in the left menu.

Next, we're going to copy the altered table statement, which will create a single partition for a specified date. Let's update the S3 location as well by copying S3 URI but this time we're also going to update the date. Hit the run to execute.

The altered table statement and now our table should be ready to run. Select queries. Let's start by running a select query that selects query that selects everything from the VPC flow logs for the current date but limits the results to 100 rows.

Looks like the query worked as expected. if you wanted to further filter out the results. Say to where the action is equal to reject.

You can add it to the where Clause with the operator. Let's look at one more. Select a query where we want to list all the rejected TPC connections and extract the day of the week for which these events occur.

There you have it. If you have any questions, leave them in the comments section below. Don't forget to like, subscribe, and turn on your notification bell. Until next time.

свернуть