Criar e consultar uma tabela para logs de fluxo da Amazon VPC - Amazon Athena
Exemplos de consulta para a tabela vpc_flow_logs

Criar e consultar uma tabela para logs de fluxo da Amazon VPC

O procedimento a seguir cria uma tabela da Amazon VPC para os logs de fluxo da Amazon VPC. Ao criar um log de fluxo em um formato personalizado, você cria uma tabela com campos que correspondem aos campos especificados quando criou o log de fluxo, na mesma ordem em que os especificou.

Para criar uma tabela do Athena para logs de fluxo da Amazon VPC

  1. Insira uma instrução DDL como a que se segue no editor de consultas do console do Athena, seguindo as diretrizes na seção Considerações e limitações. A instrução do exemplo cria uma tabela com as colunas de logs de fluxo da Amazon VPC nas versões 2 a 5, conforme documentado em Registros de log de fluxo. Se você usar outro conjunto ou outra ordem de colunas, modifique a instrução conforme apropriado.

    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://amzn-s3-demo-bucket/prefix/AWSLogs/{account_id}/vpcflowlogs/{region_code}/'
TBLPROPERTIES ("skip.header.line.count"="1");

    Observe os seguintes pontos:

    • A consulta especifica ROW FORMAT DELIMITED e omite a especificação de um SerDe. Isso significa que a consulta usa Lazy Simple SerDe para arquivos CSV, TSV e com delimitação personalizada. Nesta consulta, os campos terminam com um espaço.

    • A cláusula PARTITIONED BY usa o tipo date. Isso torna possível usar operadores matemáticos em consultas para selecionar qual é mais antiga ou mais recente em relação a uma determinada data.

      nota

      Como date é uma palavra-chave reservada em instruções DDL, é necessário fazer o escape dela com acentos graves. Para ter mais informações, consulte Escapar palavras-chave reservadas em consultas.

    • Para um log de fluxo da VPC com um formato personalizado diferente, modifique os campos para que correspondam aos campos especificados ao criar o log de fluxo.

  2. Modifique LOCATION 's3://amzn-s3-demo-bucket/prefix/AWSLogs/{account_id}/vpcflowlogs/{region_code}/' para apontar para o bucket do Amazon S3 que contém os dados de log.

  3. Execute a consulta no console do Athena. Depois que a consulta for concluída, o Athena registrará a tabela vpc_flow_logs, preparando os dados dela para você fazer as consultas.

  4. Crie partições para poder ler os dados, conforme o exemplo de consulta a seguir. Esta consulta cria uma única partição para uma data especificada. Substitua os espaços reservados por data e local, conforme necessário.

    nota

    Esta consulta cria apenas uma única partição, para uma data especificada por você. Para automatizar o processo, use um script que execute essa consulta e crie partições dessa maneira para year/month/day ou use uma instrução CREATE TABLE que especifique a projeção das partições.

    ALTER TABLE vpc_flow_logs
ADD PARTITION (`date`='YYYY-MM-dd')
LOCATION 's3://amzn-s3-demo-bucket/prefix/AWSLogs/{account_id}/vpcflowlogs/{region_code}/YYYY/MM/dd';

Exemplos de consulta para a tabela vpc_flow_logs

Use o editor de consultas no console do Athena para executar instruções SQL na tabela criada. É possível salvar as consultas, visualizar consultas anteriores ou baixar os resultados da consulta no formato CSV. Nos exemplos a seguir, substitua vpc_flow_logs pelo nome da tabela. Modifique os valores das colunas e outras variáveis de acordo com os seus requisitos.

A consulta de exemplo a seguir lista um máximo de 100 logs de fluxo para a data especificada.

SELECT * 
FROM vpc_flow_logs 
WHERE date = DATE('2020-05-04') 
LIMIT 100;

A consulta a seguir lista todas as conexões TCP rejeitadas e usa a coluna de partição de data recém-criada, date, para extrair dela o dia da semana em que ocorreram esses eventos.

SELECT day_of_week(date) AS
  day,
  date,
  interface_id,
  srcaddr,
  action,
  protocol
FROM vpc_flow_logs
WHERE action = 'REJECT' AND protocol = 6
LIMIT 100;

Para ver qual dos servidores está recebendo o maior número de solicitações HTTPS, use a consulta a seguir. Ela conta o número de pacotes recebidos na porta HTTPS 443, agrupa-os por endereço IP de destino e retorna os 10 principais da última semana.

SELECT SUM(packets) AS
  packetcount,
  dstaddr
FROM vpc_flow_logs
WHERE dstport = 443 AND date > current_date - interval '7' day
GROUP BY dstaddr
ORDER BY packetcount DESC
LIMIT 10;