Analyzing Transit Gateway Flow Logs

1. Tokenize Fields

Selects all available fields from Transit Gateway Flow Log records. Useful as a starting point to understand the log schema and confirm data is flowing.

SQL
SELECT version,
       resourcetype,
       accountid,
       tgwid,
       tgwattachmentid,
       tgwsrcvpcaccountid,
       tgwdstvpcaccountid,
       tgwsrcvpcid,
       tgwdstvpcid,
       tgwsrcsubnetid,
       tgwdstsubnetid,
       tgwsrceni,
       tgwdsteni,
       tgwsrcazid,
       tgwdstazid,
       tgwpairattachmentid,
       srcaddr,
       dstaddr,
       srcport,
       dstport,
       protocol,
       packets,
       bytes,
       start,
       `end`,
       logstatus,
       type,
       packetslostnoroute,
       packetslostblackhole,
       packetslostmtuexceeded,
       packetslostttlexpired,
       tcpflags,
       region,
       flowdirection,
       pktsrcawsservice,
       pktdstawsservice
FROM `LogGroup`

2. Filter for Flows with TCP RST Flag

Returns flow records where the TCP RST flag (value 4) is set. Indicates connections being forcibly terminated — often a sign of rejected connections or timeouts.

SQL
SELECT `@timestamp`,
       tgwid,
       tgwattachmentid,
       tgwpairattachmentid,
       srcaddr,
       dstaddr,
       srcport,
       dstport,
       protocol,
       tcpflags,
       region,
       flowdirection
FROM `LogGroup`
WHERE tcpflags = 4
LIMIT 5

3. Filter for Flows with PacketLostNoRoute

Surfaces flows where packets were dropped due to no matching route. Identifies source VPC, ENI, and IP pairs experiencing routing failures.

SQL
SELECT tgwsrcvpcaccountid AS Source_VPC_Account_ID,
       tgwattachmentid AS TGW_Attachment_ID,
       tgwsrcvpcid AS Source_VPC,
       tgwsrceni AS Source_ENI,
       srcaddr AS Source_IP,
       dstaddr AS Dest_IP,
       protocol AS Protocol,
       dstport AS Dest_Port,
       flowdirection AS FlowDirection
FROM `LogGroup`
WHERE packetslostnoroute = 1
ORDER BY `@timestamp` DESC

4. Top Flows with PacketLostNoRoute (by Data Transferred)

Aggregates flows with no-route drops by source VPC, ENI, and IP pair, ordered by total bytes transferred. Identifies the largest routing failures by volume.

SQL
SELECT tgwsrcvpcaccountid AS Source_VPC_Account_ID,
       tgwattachmentid AS TGW_Attachment_ID,
       tgwsrcvpcid AS Source_VPC,
       tgwsrceni AS Source_ENI,
       srcaddr AS Source_IP,
       dstaddr AS Dest_IP,
       protocol AS Protocol,
       flowdirection AS FlowDirection,
       SUM(bytes) AS DataTransferred
FROM `LogGroup`
WHERE packetslostnoroute = 1
GROUP BY tgwsrcvpcaccountid, tgwattachmentid, tgwsrcvpcid, tgwsrceni, srcaddr, dstaddr, protocol, flowdirection
ORDER BY DataTransferred DESC

5. Top Flows with PacketLostNoRoute (by Flow Count)

Counts the number of flows with no-route drops per TGW, attachment, source IP, and destination IP. Identifies the most frequent routing failures.

SQL
SELECT tgwid AS TGW_ID,
       tgwattachmentid AS TGW_Attachment_ID,
       srcaddr AS Source_IP,
       dstaddr AS Destination_IP,
       COUNT(*) AS NumberofFlows
FROM `LogGroup`
WHERE packetslostnoroute != '0'
GROUP BY tgwid, tgwattachmentid, srcaddr, dstaddr
ORDER BY NumberofFlows DESC

6. Top Talkers by Inbound Data Transferred & Attachment ID Pair

Identifies the attachment pairs generating the most inbound traffic for a specific TGW. Replace the tgwid value with your Transit Gateway ID.

SQL
SELECT tgwattachmentid AS TGW_Attachment_ID,
       tgwpairattachmentid AS Pair_TGW_Attachment_ID,
       SUM(bytes) AS Data_Transferred
FROM `LogGroup`
WHERE tgwid = 'tgw-0d23b252d62a9fdc5'
  AND flowdirection = 'ingress'
GROUP BY tgwattachmentid, tgwpairattachmentid
ORDER BY Data_Transferred DESC

7. Top Inbound Protocols & Ports by Traffic Volume

Aggregates inbound traffic by protocol and source port, ordered by data transferred. Shows which protocols and ports are generating the most transit traffic.

SQL
SELECT protocol,
       srcport AS Source_Port,
       SUM(bytes) AS Data_Transferred
FROM `LogGroup`
WHERE flowdirection = 'ingress'
GROUP BY protocol, srcport
ORDER BY Data_Transferred DESC
LIMIT 10

8. Top Inbound HTTP Clients by Traffic Volume

Identifies the top source IPs sending HTTP traffic (TCP port 80) inbound through the TGW, grouped by destination VPC and IP.

SQL
SELECT srcaddr AS Source_IP,
       tgwdstvpcid AS Destination_VPC,
       dstaddr AS Destination_IP,
       SUM(bytes) AS Data_Transferred
FROM `LogGroup`
WHERE protocol = 6
  AND dstport = 80
  AND flowdirection = 'ingress'
GROUP BY srcaddr, tgwdstvpcid, dstaddr
ORDER BY Data_Transferred DESC

Usage Notes

  • Replace `LogGroup` with the name of your Transit Gateway Flow Log log group.
  • Replace tgw-0d23b252d62a9fdc5 with your actual Transit Gateway ID.
  • These queries use the SQL query language — ensure your Log Insights console is set to SQL mode.
  • Protocol 6 = TCP, 17 = UDP, 1 = ICMP.
  • TCP flags: 2 = SYN, 4 = RST, 1 = FIN, 18 = SYN-ACK.
  • Results can be visualized directly in a CloudWatch Dashboard using Log Insights widgets.