aws nfw firewall logs athena setup

nfw logs athena table create

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