Unleashing the power of AWS Athena on Transit Gateway Flow Logs

AWS Athena
Photo by Hert Niks / Unsplash

AWS Transit Gateway Flow Logs provide valuable insights into the traffic flowing through your network. However, analyzing this data can be challenging, especially if you have many logs to sift through.

In this article, we will show you how to use AWS Athena to quickly ingest and query your Transit Gateway Flow Logs, providing a convenient and cost-effective way to gain valuable insights into your network traffic.

Setting up Transit Gateway Flow Logs to S3

First, let's complete the prerequisites to get our flow logs into S3.

  1. To setup Transit Gateway Flow Logs to S3, follow these steps:
  2. Open the Amazon VPC console at https://console.aws.amazon.com/vpc/.
  3. In the left navigation pane, choose Transit Gateways.
  4. Select the transit gateway that you want to enable flow logs.
  5. On the details pane, select Flow Logs.
  6. Choose Create flow log.
  7. Give it a meaningful name.
  8. For the destination, choose S3.
  9. For the S3 bucket, add the arn of the bucket where you want to store the flow logs.
  10. Select the default format.
  11. Turn on Hive-compatible S3 prefix.
  12. Enable Partition logs by the hour.
  13. Add any Tags required.
  14. In the Create Flow Log dialogue box, choose to Create.

Wait for the flow log to be created and associated with your transit gateway.

After about 5 minutes thn traffic will start to be delivered to your S3 bucket in the following format.

s3://s3bucket/AWSLogs/aws-account-id=12345678910/aws-service=vpcflowlogs/aws-region=ap-southeast-2/year=2022/month=12/day=05/hour=23/12345678910_vpcflowlogs_ap-southeast-2_fl-03411bdc344d21124_20221205T2330Z_3271e488.log.gz

Querying the data

Now we create our table using AWS Athena to query the logs.

  1. Open the Amazon Athena console at https://console.aws.amazon.com/athena/.
  2. In the left navigation pane, choose Query Editor.
  3. In the Query Editor, past the following, updating the S3 location with the destination above.
  1. Choose Run query. This will create your table. which should now show Data navigation to the left.

Querying the data

Now we are all set to get access to our data!

To query for the top ten destination addresses by total bytes, you can use the following query:

SELECT dstaddr, SUM(bytes) AS total_bytes
FROM twgflowlogs.awslogs
GROUP BY dstaddr
ORDER BY total_bytes DESC
LIMIT 10;

This query groups the data by

  • dstaddr column,
  • calculates the total number of bytes for each group,
  • sorts the results in descending order by the total_bytes column.

The LIMIT clause returns only the top ten results.

As our table is partitioned, we can specify partition values in the FROM clause to limit queried data.

For example:

SELECT dstaddr, SUM(bytes) AS total_bytes
FROM twgflowlogs.awslogs
WHERE `aws-account-id` = '123456789012'
  AND `aws-region` = 'ap-southeast-2'
GROUP BY dstaddr
ORDER BY total_bytes DESC
LIMIT 10;

This query only considers data in the aws-account-id=123456789012 and aws-region=ap-southeast-2 partitions, which can improve performance and reduce costs.

If you want to query data for the month of December 2022, you can use the following query:

SELECT dstaddr, SUM(bytes) AS total_bytes
FROM twgflowlogs.awslogs
WHERE year = '2022'
  AND month = '12'
GROUP BY dstaddr
ORDER BY total_bytes DESC
LIMIT 10;

This query only considers data in the year=2022 and month=12 partitions, which corresponds to the data for December 2022.

Note that the flow log format also has start and end columns that can also filter the data by date.

For example:

SELECT dstaddr, SUM(bytes) AS total_bytes
FROM twgflowlogs.awslogs
WHERE start >= 1669852800 AND end < 1670457600
GROUP BY dstaddr
ORDER BY total_bytes DESC
LIMIT 10;

This query only considers data where

  • the start timestamp is greater than or equal to the beginning of "Thursday, 01 December 2022, 12:00:00 AM GMT" (1669852800)
  • and the end timestamp, is less than the end of "Thursday, 08 December 2022, 12:00:00 AM GMT" (1670457600).

Using this method, we can still get very fine-grained windows in the data without creating additional partitions.

Summary

This post shows Athena's ability to access Transit Gateway Flow logs.

Next up, we will look at transforming the data with AWS Glue to make those source and destination addresses more meaningful!

I hope this helps someone else.

Cheers