Skip to content

Instantly share code, notes, and snippets.

@rdkls
Last active July 13, 2023 00:28
Show Gist options
  • Save rdkls/4d2228795b3a64d9a728f94e1441222f to your computer and use it in GitHub Desktop.
Save rdkls/4d2228795b3a64d9a728f94e1441222f to your computer and use it in GitHub Desktop.
aws nfw firewall logs athena setup
https://docs.aws.amazon.com/network-firewall/latest/developerguide/firewall-logging.html
BUT add "alert" data which is super important when trying to troubleshoot which rule is blocking
CREATE EXTERNAL TABLE `nfw_logs`(
`firewall_name` string COMMENT 'from deserializer',
`availability_zone` string COMMENT 'from deserializer',
`event_timestamp` bigint COMMENT 'from deserializer',
`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,
finish:string,
age:int,
min_ttl:int,
max_ttl:int
>,
alert:struct<
severity:int,
signature_id:int,
rev:int,
signature:string,
action:string,
category:string
>
> COMMENT 'from deserializer')
ROW FORMAT SERDE
'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://xxxxxxxxxxxxxxxx/firewall/AWSLogs'
SELECT event.src_ip, count(1) as num
FROM "aws-accelerator-subscription-database"."nfw_logs"
group by 1
having count(1) > 100
order by count(1) desc
limit 100
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment