Querying Amazon Network Firewall logs - Amazon Athena
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Querying Amazon Network Firewall logs

Amazon Network Firewall is a managed service that you can use to deploy essential network protections for your Amazon Virtual Private Cloud instances. Amazon Network Firewall works together with Amazon Firewall Manager so you can build policies based on Amazon Network Firewall rules and then centrally apply those policies across your VPCs and accounts. For more information about Amazon Network Firewall, see Amazon Network Firewall.

You can configure Amazon Network Firewall logging for traffic that you forward to your firewall's stateful rules engine. Logging gives you detailed information about network traffic, including the time that the stateful engine received a packet, detailed information about the packet, and any stateful rule action taken against the packet. The logs are published to the log destination that you've configured, where you can retrieve and view them. For more information, see Logging network traffic from Amazon Network Firewall in the Amazon Network Firewall Developer Guide.

Create a table for alert logs

  1. Modify the following sample DDL statement to conform to the structure of your alert log. You may need to update the statement to include the columns for the latest version of the logs. For more information, see Contents of a firewall log in the Amazon Network Firewall Developer Guide.

    CREATE EXTERNAL TABLE network_firewall_alert_logs ( firewall_name string, availability_zone string, event_timestamp string, event struct< timestamp:string, flow_id:bigint, event_type:string, src_ip:string, src_port:int, dest_ip:string, dest_port:int, proto:string, app_proto:string, tls_inspected:boolean, alert:struct< alert_id:string, alert_type:string, action:string, signature_id:int, rev:int, signature:string, category:string, severity:int, rule_name:string, alert_name:string, alert_severity:string, alert_description:string, file_name:string, file_hash:string, packet_capture:string, reference_links:array<string> >, src_country:string, dest_country:string, src_hostname:string, dest_hostname:string, user_agent:string, url:string > ) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' LOCATION 's3://DOC-EXAMPLE-BUCKET/path_to_alert_logs_folder/';
  2. Modify the LOCATION clause to specify the folder for your logs in Amazon S3.

  3. Run your CREATE TABLE query in the Athena query editor. After the query completes, Athena registers the network_firewall_alert_logs table, making the data that it points to ready for queries.

Alert log sample query

The sample alert log query in this section filters for events in which TLS inspection was performed that have alerts with a severity level of 2 or higher.

The query uses aliases to create output column headings that show the struct that the column belongs to. For example, the column heading for the event.alert.category field is event_alert_category instead of just category. To customize the column names further, you can modify the aliases to suit your preferences. For example, you can use underscores or other separators to delimit the struct names and field names.

Remember to modify column names and struct references based on your table definition and on the fields that you want in the query result.

SELECT firewall_name, availability_zone, event_timestamp, event.timestamp AS event_timestamp, event.flow_id AS event_flow_id, event.event_type AS event_type, event.src_ip AS event_src_ip, event.src_port AS event_src_port, event.dest_ip AS event_dest_ip, event.dest_port AS event_dest_port, event.proto AS event_protol, event.app_proto AS event_app_proto, event.tls_inspected AS event_tls_inspected, event.alert.alert_id AS event_alert_alert_id, event.alert.alert_type AS event_alert_alert_type, event.alert.action AS event_alert_action, event.alert.signature_id AS event_alert_signature_id, event.alert.rev AS event_alert_rev, event.alert.signature AS event_alert_signature, event.alert.category AS event_alert_category, event.alert.severity AS event_alert_severity, event.alert.rule_name AS event_alert_rule_name, event.alert.alert_name AS event_alert_alert_name, event.alert.alert_severity AS event_alert_alert_severity, event.alert.alert_description AS event_alert_alert_description, event.alert.file_name AS event_alert_file_name, event.alert.file_hash AS event_alert_file_hash, event.alert.packet_capture AS event_alert_packet_capture, event.alert.reference_links AS event_alert_reference_links, event.src_country AS event_src_country, event.dest_country AS event_dest_country, event.src_hostname AS event_src_hostname, event.dest_hostname AS event_dest_hostname, event.user_agent AS event_user_agent, event.url AS event_url FROM network_firewall_alert_logs WHERE event.alert.severity >= 2 AND event.tls_inspected = true LIMIT 10;

Create a table for netflow logs

  1. Modify the following sample DDL statement to conform to the structure of your netflow logs. You may need to update the statement to include the columns for the latest version of the logs. For more information, see Contents of a firewall log in the Amazon Network Firewall Developer Guide.

    CREATE EXTERNAL TABLE network_firewall_netflow_logs ( firewall_name string, availability_zone string, event_timestamp string, event struct< timestamp:string, flow_id:bigint, event_type:string, src_ip:string, src_port:int, dest_ip:string, dest_port:int, proto:string, app_proto:string, netflow:struct< pkts:int, bytes:bigint, start:string, `end`:string, age:int, min_ttl:int, max_ttl:int, tcp_flags:struct< syn:boolean, fin:boolean, rst:boolean, psh:boolean, ack:boolean, urg:boolean >, tls_inspected:boolean > > ) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' LOCATION 's3://DOC-EXAMPLE-BUCKET/path_to_netflow_logs_folder/';
  2. Modify the LOCATION clause to specify the folder for your logs in Amazon S3.

  3. Run the CREATE TABLE query in the Athena query editor. After the query completes, Athena registers the network_firewall_netflow_logs table, making the data that it points to ready for queries.

Netflow log sample query

The sample netflow log query in this section filters for events in which TLS inspection was performed.

The query uses aliases to create output column headings that show the struct that the column belongs to. For example, the column heading for the event.netflow.bytes field is event_netflow_bytes instead of just bytes. To customize the column names further, you can modify the aliases to suit your preferences. For example, you can use underscores or other separators to delimit the struct names and field names.

Remember to modify column names and struct references based on your table definition and on the fields that you want in the query result.

SELECT event.src_ip AS event_src_ip, event.dest_ip AS event_dest_ip, event.proto AS event_proto, event.app_proto AS event_app_proto, event.netflow.pkts AS event_netflow_pkts, event.netflow.bytes AS event_netflow_bytes, event.netflow.tcp_flags.syn AS event_netflow_tcp_flags_syn, event.netflow.tls_inspected AS event_netflow_tls_inspected FROM network_firewall_netflow_logs WHERE event.netflow.tls_inspected = true