# 使用 Athena 快速的查找 AWS VPC Flow Log 方式

## Ref
* [將 VPC Flow Log 傳到 S3 內](https://docs.aws.amazon.com/vpc/latest/userguide/flow-logs-s3.html#flow-logs-s3-create-flow-log)

## Brief Steps
1. 啟用 AWS VPC Flow Log 並且存放到 S3 bucket 內
2. (承上) 如果不是自己的 VPC Flow Log 的話，也請先把他們上傳到自己的 S3 bucket 內
3. 打開 Athena Console 選擇 Query editor
* 首先可以先建立一個資料庫

        CREATE database vpcflowlogs;

* 接著，從建立資料表，來源選擇 S3 的檔案讀取到自己定義的 Schema 中
* 下面這個是 自己放 Log 到 's3://mylog-cloudwatch/logs/' 的版本

        CREATE EXTERNAL TABLE IF NOT EXISTS vpc_flow_logs (
          version int,
          account string,
          interfaceid string,
          sourceaddress string,
          destinationaddress string,
          sourceport int,
          destinationport int,
          protocol int,
          numpackets int,
          numbytes bigint,
          starttime int,
          endtime int,
          action string,
          logstatus string
        )
        ROW FORMAT DELIMITED
        FIELDS TERMINATED BY ' '
        LOCATION 's3://mylog-cloudwatch/logs/'
        TBLPROPERTIES ("skip.header.line.count"="1");

* 下面這個是 AWS VPC Flow Log 被放到指定路徑下的版本

        CREATE EXTERNAL TABLE IF NOT EXISTS vpc_flow_logs (
          version int,
          account string,
          interfaceid string,
          sourceaddress string,
          destinationaddress string,
          sourceport int,
          destinationport int,
          protocol int,
          numpackets int,
          numbytes bigint,
          starttime int,
          endtime int,
          action string,
          logstatus string
        )
        PARTITIONED BY (`date` date)
        ROW FORMAT DELIMITED
        FIELDS TERMINATED BY ' '
        LOCATION 's3://mylog-cloudwatch/AWSLogs/你的AWS_ID共12碼/vpcflowlogs/ap-northeast-1/'
        TBLPROPERTIES ("skip.header.line.count"="1");

* 詳情可以參考 https://docs.aws.amazon.com/athena/latest/ug/vpc-flow-logs.html
* 如此即可將資料表格建好，不過如果如果等一下撈不到資料的話，就要刪掉重建一張
* 最後使用下列 query 查詢

        SELECT * from vpc_flow_logs;