VPC flow log format

${version} ${account-id} ${interface-id} ${srcaddr} ${dstaddr} ${srcport} ${dstport} ${protocol} ${packets} ${bytes} ${start} ${end} ${action} ${log-status}

2 715371302281 eni-0d8c6a51023a35370 172.31.85.78 172.31.24.254 48838 3306 6 212 11254 1649821310 1649821326 ACCEPT OK

Aurora audit log format

| timestamp | aurora serverhost | username | source host | connectionid | queryid | operation | database | object | retcode|

1649825205246080,aiml-db1-instance-1, admin, 172.31.85.78, 11, 12214, QUERY, order-service,'update orders_backup set symbol=\'HOT-ETH2\' where id=\'5e3ae683cfbd03000975f392\'', 0

通过Athena查询vpc flow log

Athena 配置VPC flow log 查询 记得修改成自己的S3地址 参考 https://docs.aws.amazon.com/zh_cn/athena/latest/ug/vpc-flow-logs.html

CREATE EXTERNAL TABLE IF NOT EXISTS `vpc_flow_logs` ( `version` int, `account_id` string, `interface_id` string, `srcaddr` string, `dstaddr` string, `srcport` int, `dstport` int, `protocol` bigint, `packets` bigint, `bytes` bigint, `start` bigint, `end` bigint, `action` string, `log_status` string, `vpc_id` string, `subnet_id` string, `instance_id` string, `tcp_flags` int, `type` string, `pkt_srcaddr` string, `pkt_dstaddr` string, `region` string, `az_id` string, `sublocation_type` string, `sublocation_id` string, `pkt_src_aws_service` string, `pkt_dst_aws_service` string, `flow_direction` string, `traffic_path` int ) PARTITIONED BY (`date` date) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' LOCATION 's3://vpcflowlog-1389/AWSLogs/{accountid}/vpcflowlogs/us-east-1/' TBLPROPERTIES ("skip.header.line.count"="1");

创建分片

ALTER TABLE vpc_flow_logs ADD PARTITION (`date`='2022-04-13') LOCATION 's3://vpcflowlog-1389/AWSLogs/{accountid}/vpcflowlogs/us-east-1/2022/04/13';

通过aurora audit log 日志跟vpc flow log来进行定位

SELECT * FROM vpc_flow_logs WHERE date = DATE('2022-04-13') and srcaddr='172.31.85.78' and dstport=3306 and start < 1649825205 and "end" > 1649825205