SQL for getting cross-az traffic cost from AWS VPC flow logs

cross-az-bytes-simplified.sql

SELECT 
      srcaddr, 
      dstaddr,
      srcsubnet.az_id AS srcaz,
      dstsubnet.az_id AS dstaz,
      SUM(bytes) AS total_bytes
  FROM flowlogs
  LEFT JOIN subnets AS srcsubnet 
      ON srcsubnet.cidr = CAST(NETWORK(CAST((srcaddr || '/21') AS inet)) AS VARCHAR)
  LEFT JOIN subnets AS dstsubnet 
      ON dstsubnet.cidr = CAST(NETWORK(CAST((dstaddr || '/21') AS inet)) AS VARCHAR)
  WHERE
      srcaddr != '-' AND
      dstaddr != '-' AND
      srcaz != dstaz
  GROUP BY ALL
  ORDER BY total_bytes DESC
  LIMIT 10;

cross-az-bytes.sql

with
    src as (
        select
            srcaddr,
            cast(network(cast((srcaddr || '/21')  as inet)) as varchar) as network
        from flowlogs
        where
            flowlogs.srcaddr != '-'
    ),
    srcwaz as (
        select 
            srcaddr,
            src.network as srcnetwork,
            subnets.az_id as srcaz
        from src
        left join subnets on subnets.cidr = src.network
    ),
    dst as (
        select 
            dstaddr,
            cast(network(cast((dstaddr || '/21')  as inet)) as varchar) as network
        from flowlogs
        where
            flowlogs.srcaddr != '-'
    ),
    dstwaz as (
        select 
            dstaddr,
            dst.network as dstnetwork,
            subnets.az_id as dstaz
        from dst
        left join subnets on subnets.cidr = dst.network
    ),
    bytes as (
        select 
            srcaddr,
            dstaddr,
            sum(bytes) as total_bytes
        from flowlogs
        where
            flowlogs.srcaddr != '-' and
            flowlogs.dstaddr != '-'
        group by srcaddr, dstaddr
    )
select * from srcwaz, dstwaz, bytes
where
    srcwaz.srcaddr = bytes.srcaddr and
    dstwaz.dstaddr = bytes.dstaddr
group by all
limit 10;

flowlogs.txt

> describe flowlogs;
┌─────────────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│     column_name     │ column_type │  null   │   key   │ default │  extra  │
│       varchar       │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ account_id          │ VARCHAR     │ YES     │         │         │         │
│ action              │ VARCHAR     │ YES     │         │         │         │
│ az_id               │ VARCHAR     │ YES     │         │         │         │
│ bytes               │ BIGINT      │ YES     │         │         │         │
│ dstaddr             │ VARCHAR     │ YES     │         │         │         │
│ dstport             │ INTEGER     │ YES     │         │         │         │
│ end                 │ BIGINT      │ YES     │         │         │         │
│ flow_direction      │ VARCHAR     │ YES     │         │         │         │
│ instance_id         │ VARCHAR     │ YES     │         │         │         │
│ interface_id        │ VARCHAR     │ YES     │         │         │         │
│ log_status          │ VARCHAR     │ YES     │         │         │         │
│ packets             │ BIGINT      │ YES     │         │         │         │
│ pkt_dst_aws_service │ VARCHAR     │ YES     │         │         │         │
│ pkt_dstaddr         │ VARCHAR     │ YES     │         │         │         │
│ pkt_src_aws_service │ VARCHAR     │ YES     │         │         │         │
│ pkt_srcaddr         │ VARCHAR     │ YES     │         │         │         │
│ protocol            │ INTEGER     │ YES     │         │         │         │
│ region              │ VARCHAR     │ YES     │         │         │         │
│ reject_reason       │ VARCHAR     │ YES     │         │         │         │
│ srcaddr             │ VARCHAR     │ YES     │         │         │         │
│ srcport             │ INTEGER     │ YES     │         │         │         │
│ start               │ BIGINT      │ YES     │         │         │         │
│ subnet_id           │ VARCHAR     │ YES     │         │         │         │
│ tcp_flags           │ INTEGER     │ YES     │         │         │         │
│ traffic_path        │ INTEGER     │ YES     │         │         │         │
│ type                │ VARCHAR     │ YES     │         │         │         │
│ version             │ INTEGER     │ YES     │         │         │         │
│ vpc_id              │ VARCHAR     │ YES     │         │         │         │
│ aws-account-id      │ BIGINT      │ YES     │         │         │         │
│ aws-region          │ VARCHAR     │ YES     │         │         │         │
│ aws-service         │ VARCHAR     │ YES     │         │         │         │
│ day                 │ VARCHAR     │ YES     │         │         │         │
│ hour                │ VARCHAR     │ YES     │         │         │         │
│ month               │ BIGINT      │ YES     │         │         │         │
│ year                │ BIGINT      │ YES     │         │         │         │
├─────────────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┤
│ 35 rows                                                         6 columns │
└───────────────────────────────────────────────────────────────────────────┘

subnets.txt

> describe subnets;
┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ subnet_id   │ VARCHAR     │ YES     │         │         │         │
│ az_id       │ VARCHAR     │ YES     │         │         │         │
│ name        │ VARCHAR     │ YES     │         │         │         │
│ cidr        │ VARCHAR     │ YES     │         │         │         │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘