syslog-server/terraform/athena.tf

resource "aws_athena_workgroup" "this" {
  name = local.athena_workgroup

  configuration {
    enforce_workgroup_configuration    = true
    publish_cloudwatch_metrics_enabled = false

    result_configuration {
      output_location = "s3://${aws_s3_bucket.unifi.bucket}/${local.athena_results_prefix}"

      encryption_configuration {
        encryption_option = "SSE_S3"
      }
    }
  }
}

resource "aws_athena_named_query" "recent_denies" {
  name      = "unifi-recent-denies"
  workgroup = aws_athena_workgroup.this.id
  database  = aws_glue_catalog_database.unifi.name
  query     = <<-SQL
    SELECT timestamp, site, hostname, src, dst, proto, action, raw
    FROM iptables
    WHERE dt >= date_format(current_date - interval '7' day, '%Y-%m-%d')
      AND action = 'deny'
    ORDER BY timestamp DESC
    LIMIT 200
  SQL
}

resource "aws_athena_named_query" "src_dst_lookup" {
  name      = "unifi-src-dst-lookup"
  workgroup = aws_athena_workgroup.this.id
  database  = aws_glue_catalog_database.unifi.name
  query     = <<-SQL
    SELECT timestamp, format, site, hostname, src, dst, proto, action, raw
    FROM iptables
    WHERE dt >= date_format(current_date - interval '1' day, '%Y-%m-%d')
      AND (src = 'x.x.x.x' OR dst = 'x.x.x.x')
    ORDER BY timestamp DESC
    LIMIT 200
  SQL
}

resource "aws_athena_named_query" "cef_security" {
  name      = "unifi-cef-security"
  workgroup = aws_athena_workgroup.this.id
  database  = aws_glue_catalog_database.unifi.name
  query     = <<-SQL
    SELECT timestamp, site, hostname, src, dst, proto, action, raw
    FROM cef
    WHERE dt >= date_format(current_date - interval '7' day, '%Y-%m-%d')
      AND (
        lower(raw) LIKE '%security%'
        OR lower(raw) LIKE '%intrusion%'
        OR lower(raw) LIKE '%blocked%'
        OR lower(raw) LIKE '%threat%'
      )
    ORDER BY timestamp DESC
    LIMIT 200
  SQL
}