steampipe plugin install aws

Table: aws_vpc_flow_log_event - Query AWS VPC Flow Logs using SQL

The AWS VPC Flow Logs is a feature that enables you to capture information about the IP traffic going to and from network interfaces in your VPC. It allows you to log network traffic that traverses your VPC, including traffic that doesn’t reach your application. Capturing this information can help you diagnose overly permissive or overly restrictive security group and network ACL rules.

Table Usage Guide

The aws_vpc_flow_log_event table in Steampipe gives you information about the IP traffic going to and from network interfaces in your Virtual Private Cloud (VPC). With this table, you as a network administrator, security analyst, or DevOps engineer can query details about each traffic flow, including source and destination IP addresses, ports, protocol numbers, packet and byte counts, actions, and more. You can use this table to monitor traffic patterns, troubleshoot connectivity issues, and analyze security incidents. The schema outlines the various attributes of the VPC flow log event, including the event time, log status, and associated metadata.

Important Notes

  • You must specify log_group_name in a where clause in order to use this table.
  • For improved performance, it is suggested that you use the optional qual timestamp to limit the result set to a specific time period.
  • This table supports optional quals. Queries with optional quals are optimized to use CloudWatch filters. Optional quals are supported for the following columns:
    • action
    • dst_addr
    • dst_port
    • event_id
    • filter
    • interface_id
    • log_status
    • log_stream_name
    • region
    • src_addr
    • src_port
    • timestamp

Examples

List events that occurred over the last five minutes

Track recent activity within your virtual private cloud (VPC) by identifying events that have transpired in the last five minutes. This can be useful for real-time monitoring and immediate response to potential issues or anomalies.

select
log_group_name,
log_stream_name,
log_status,
action,
ingestion_time,
timestamp,
interface_id,
interface_account_id,
src_addr,
region
from
aws_vpc_flow_log_event
where
log_group_name = 'vpc-log-group-name'
and timestamp >= now() - interval '5 minutes';
select
log_group_name,
log_stream_name,
log_status,
action,
ingestion_time,
timestamp,
interface_id,
interface_account_id,
src_addr,
region
from
aws_vpc_flow_log_event
where
log_group_name = 'vpc-log-group-name'
and timestamp >= datetime('now', '-5 minutes');

List ordered events that occurred between five to ten minutes ago

Explore the sequence of events that transpired in your virtual private cloud (VPC) within a specific timeframe. This can help you understand the pattern of activity and potential issues within your VPC during that period.

select
log_group_name,
log_stream_name,
log_status,
action,
ingestion_time,
timestamp,
interface_id,
interface_account_id,
src_addr,
region
from
aws_vpc_flow_log_event
where
log_group_name = 'vpc-log-group-name'
and timestamp between (now() - interval '10 minutes')
and (now() - interval '5 minutes')
order by
timestamp asc;
select
log_group_name,
log_stream_name,
log_status,
action,
ingestion_time,
timestamp,
interface_id,
interface_account_id,
src_addr,
region
from
aws_vpc_flow_log_event
where
log_group_name = 'vpc-log-group-name'
and timestamp between (datetime('now', '-10 minutes'))
and (datetime('now', '-5 minutes'))
order by
timestamp asc;

List distinct interface IDs found in all flow logs that occurred over the last hour

Identify unique interface IDs present in all flow logs from the past hour. This can be useful for monitoring activity and identifying unusual or suspicious network events in real-time.

select
distinct(interface_id)
from
aws_vpc_flow_log_event
where
log_group_name = 'vpc-log-group-name'
and timestamp >= now() - interval '1 hour';
select
distinct(interface_id)
from
aws_vpc_flow_log_event
where
log_group_name = 'vpc-log-group-name'
and timestamp >= datetime('now', '-1 hours');

Get details for all rejected traffic that occurred over the last hour

Uncover the details of all denied network traffic within the past hour. This information is crucial in identifying potential security threats and understanding network traffic patterns.

select
log_stream_name,
timestamp,
interface_id,
interface_account_id,
src_addr,
src_port,
dst_addr,
dst_port
from
aws_vpc_flow_log_event
where
log_group_name = 'vpc-log-group-name'
and action = 'REJECT'
and timestamp >= now() - interval '1 hour';
select
log_stream_name,
timestamp,
interface_id,
interface_account_id,
src_addr,
src_port,
dst_addr,
dst_port
from
aws_vpc_flow_log_event
where
log_group_name = 'vpc-log-group-name'
and action = 'REJECT'
and timestamp >= datetime('now', '-1 hour');

Filter examples

For more information on CloudWatch log filters, please refer to Filter Pattern Syntax.

List flow logs with traffic between specific IP addresses that occurred over the last hour

Determine the instances of network traffic between specific IP addresses within the last hour. This can be useful for monitoring unusual activity or troubleshooting network issues.

select
log_group_name,
log_stream_name,
log_status,
action,
ingestion_time,
timestamp,
interface_id,
interface_account_id,
src_addr,
region
from
aws_vpc_flow_log_event
where
log_group_name = 'vpc-log-group-name'
and log_stream_name = 'eni-1d47d21d-all'
and (
src_addr = '10.85.14.210'
or dst_addr = '10.85.14.213'
)
and timestamp >= now() - interval '1 hour'
order by
timestamp;
select
log_group_name,
log_stream_name,
log_status,
action,
ingestion_time,
timestamp,
interface_id,
interface_account_id,
src_addr,
region
from
aws_vpc_flow_log_event
where
log_group_name = 'vpc-log-group-name'
and log_stream_name = 'eni-1d47d21d-all'
and (
src_addr = '10.85.14.210'
or dst_addr = '10.85.14.213'
)
and timestamp >= datetime('now', '-1 hours')
order by
timestamp;

List flow logs with source IP address in a specific range that occurred over the last hour

This query is useful for identifying potential security threats by pinpointing the instances where network traffic originated from a specific IP address range within the last hour. It helps in timely detection of suspicious activity and aids in maintaining network security.

select
log_group_name,
log_stream_name,
log_status,
action,
ingestion_time,
timestamp,
interface_id,
interface_account_id,
src_addr,
region
from
aws_vpc_flow_log_event
where
log_group_name = 'vpc-log-group-name'
and log_stream_name = 'eni-1d47d21d-all'
and src_addr << '10.0.0.0/8' :: inet
and timestamp >= now() - interval '1 hour'
order by
timestamp;
Error: SQLite does not support CIDR operations.

Schema for aws_vpc_flow_log_event

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The AWS Account ID in which the resource is located.
actiontext=The action that is associated with the traffic: ACCEPT — The recorded traffic was permitted by the security groups and network ACLs. REJECT — The recorded traffic was not permitted by the security groups or network ACLs.
bytesbigintThe number of bytes transferred during the flow.
dst_addrinet=The destination address for outgoing traffic, or the IPv4 or IPv6 address of the network interface for incoming traffic on the network interface. The IPv4 address of the network interface is always its private IPv4 address. See also pkt-dstaddr.
dst_portbigint=The destination port of the traffic.
endtimestamp with time zoneThe time when the last packet of the flow was received within the aggregation interval. This might be up to 60 seconds after the packet was transmitted or received on the network interface.
event_idtext=The ID of the event.
filtertext=Filter pattern for the search.
ingestion_timetimestamp with time zoneThe time when the event was ingested.
interface_account_idtextThe AWS account ID of the owner of the source network interface for which traffic is recorded. If the network interface is created by an AWS service, for example when creating a VPC endpoint or Network Load Balancer, the record may display unknown for this field.
interface_idtext=The ID of the network interface for which the traffic is recorded.
log_group_nametext=The name of the log group to which this event belongs.
log_statustext=The logging status of the flow log: OK — Data is logging normally to the chosen destinations. NODATA — There was no network traffic to or from the network interface during the aggregation interval. SKIPDATA — Some flow log records were skipped during the aggregation interval. This may be because of an internal capacity constraint, or an internal error.
log_stream_nametext=The name of the log stream to which this event belongs.
packetsbigintThe number of packets transferred during the flow.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
protocolbigintThe IANA protocol number of the traffic. For more information, see Assigned Internet Protocol Numbers.
regiontext=The AWS Region in which the resource is located.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
src_addrinet=The source address for incoming traffic, or the IPv4 or IPv6 address of the network interface for outgoing traffic on the network interface. The IPv4 address of the network interface is always its private IPv4 address. See also pkt-srcaddr.
src_portbigint=The source port of the traffic.
starttimestamp with time zoneThe time when the first packet of the flow was received within the aggregation interval. This might be up to 60 seconds after the packet was transmitted or received on the network interface.
timestamptimestamp with time zone>, >=, =, <, <=The time when the event occurred.
versionbigintThe VPC Flow Logs version. If you use the default format, the version is 2. If you use a custom format, the version is the highest version among the specified fields. For example, if you specify only fields from version 2, the version is 2. If you specify a mixture of fields from versions 2, 3, and 4, the version is 4.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- aws

You can pass the configuration to the command with the --config argument:

steampipe_export_aws --config '<your_config>' aws_vpc_flow_log_event