ANALYZING VPC FLOW LOGS USING ANTHENA
Tags: aws, awscertifiedadvancednetworking
Published: 2023-05-13 11:40:12

Flow Logs is a unique feature that enables you to capture traffic inbound and outbound from your AWS network interfaces. There are three types of flow logs:
a. VPC flow logs
b. Subnet Flow Logs
c. Elastic Network Interface Flow logs. 

Our focus will be on VPC flow logs. As the name implies, we will capture traffic entering and leaving VPC interfaces. VPC flow logs can be stored in either Cloudwatch logs or Amazon S3.

The logs that will be captured for this scenario will be stored in Amazon S3 and queryable on the Anthena platform for simplified output.

Kindly follow the steps below:

STEP 1

Create the VPC flow logs on the existing VPC 


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ovjalgl6uwdox8xxs1rz.png)

STEP 2
Give the flowlog a name and specify the destination where the logs will be stored. In our case, we are storing the logs captured inside the S3 bucket. For this reason,specify the S3 bucket ARN where you want to store your log.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ajn8crxqek9fd80ofxdy.png)

STEP 3
Log files have been stored in an S3 bucket.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0lfk8p1r55imebow1gmw.png)
STEP 4
Create a work group on Anthena.
https://docs.aws.amazon.com/athena/latest/ug/vpc-flow-logs.html

```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/72kcbk1u93zdpt6rjv1x.png)

```
STEP 5.

Create a table in the default database. 


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e72ks0fszcdj9nfn995i.png)

CREATE EXTERNAL TABLE IF NOT EXISTS `vpc_flow_logs` (
  version int,
  account_id string,
  interface_id string,
  srcaddr string,
  dstaddr string,
  srcport int,
  dstport int,
  protocol bigint,
  packets bigint,
  bytes bigint,
  start bigint,
  `end` bigint,
  action string,
  log_status string,
  vpc_id string,
  subnet_id string,
  instance_id string,
  tcp_flags int,
  type string,
  pkt_srcaddr string,
  pkt_dstaddr string,
  region string,
  az_id string,
  sublocation_type string,
  sublocation_id string,
  pkt_src_aws_service string,
  pkt_dst_aws_service string,
  flow_direction string,
  traffic_path int
)
PARTITIONED BY (`date` date)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' '
LOCATION 's3://s3flowlog1/AWSLogs/003985890001/vpcflowlogs/us-east-1/'
TBLPROPERTIES ("skip.header.line.count"="1");



STEP 6.

Alter the table and add a partition.

ALTER TABLE vpc_flow_logs
ADD PARTITION (`date`='2023-05-11')
LOCATION 's3://s3flowlog1/AWSLogs/003985890001/vpcflowlogs/us-east-1/2023/05/131';


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z579roaehtid6v86t8s0.png)


STEP 7 

Query the database and analyze your output.

a.
SELECT * FROM vpc_flow_logs . 
b.
SELECT
       interface_id,
       srcaddr,
       action,
       protocol
FROM vpc_flow_logs
WHERE action = 'REJECT' AND protocol = 6 
LIMIT 10
c.
SELECT
       interface_id,
       srcaddr,
       action,
       protocol
FROM vpc_flow_logs
WHERE action = 'REJECT' AND protocol = 6  


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7uyt7ti5dksuox8qizll.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5tii2t4zf6e2w4xpb73w.png)