Signals #7: From Logs to Signals

TL;DR: Turn raw CloudFront logs into a classified Athena view. A Glue table with partition projection ingests JSON logs; a SQL view adds agent, access_type, and crawler columns that classify every request as human, bot, LLM retrieval, or scanner.

Raw requests are interesting, but how do we distinguish crawlers, retrieval systems, previews and humans?

Last time we established why CloudFront logs are the right foundation for a visibility layer. Now the implementation: how to go from raw JSON log files in S3 to a queryable, classified dataset in Grafana.

---

Step 1: Enable CloudFront Standard Logging v2

CloudFront Standard Logging v2 delivers JSON records — not the legacy TSV format. Enable it in the distribution settings, point it at an S3 bucket, and logs start flowing within minutes.

The partition structure CloudFront uses:

s3://your-cf-logs-bucket/
  AWSLogs/aws-account-id=123456789012/
    CloudFront/
      DistributionId=EDIST1EXAMPLE1/
        year=2026/month=07/day=04/
          EDIST1EXAMPLE1.2026-07-04-03.abc123.json.gz

Each file is gzipped JSON, one record per line. The Hive-style path structure is what makes partition projection work.

Step 2: Glue External Table

Athena needs a schema to read the raw files. A Glue external table provides it — no data movement, no ETL, schema-on-read.

The key configuration is partition projection, which eliminates manual partition registration:

resource "aws_glue_catalog_table" "cf_logs_raw" {
  database_name = "signals"
  name          = "cloudfront_logs_raw"
  table_type    = "EXTERNAL_TABLE"

  parameters = {
    "classification"     = "json"
    "projection.enabled" = "true"

    "projection.year.type"  = "integer"
    "projection.year.range" = "2025,2030"

    "projection.month.type"   = "integer"
    "projection.month.range"  = "1,12"
    "projection.month.digits" = "2"

    "projection.day.type"   = "integer"
    "projection.day.range"  = "1,31"
    "projection.day.digits" = "2"

    "projection.distributionid.type"   = "enum"
    "projection.distributionid.values" = "EDIST1EXAMPLE1"

    "storage.location.template" = "s3://${var.cf_logs_bucket}/AWSLogs/aws-account-id=${local.account_id}/CloudFront/DistributionId=$${distributionid}/year=$${year}/month=$${month}/day=$${day}/"
  }
  ...
}

Partition projection means Athena computes partition paths from the query’s WHERE clause — no MSCK REPAIR TABLE, no Glue crawler, no daily maintenance job. Query WHERE year=2026 AND month=7 AND day=4 and Athena knows exactly which S3 prefix to read.

One mapping detail: CloudFront’s JSON field names use characters that aren’t valid Glue column names (cs(User-Agent), c-country). The JsonSerDe handles this with explicit mappings:

ser_de_info {
  parameters = {
    "mapping.cs_user_agent" = "cs(User-Agent)"
    "mapping.c_country"     = "c-country"
    "mapping.cs_referer"    = "cs(Referer)"
  }
}

Step 3: The Semantic View

The raw table has everything but means nothing. The view is where classification happens.

CREATE OR REPLACE VIEW cloudfront_requests AS
SELECT
  cast(from_iso8601_timestamp(date || 'T' || time || 'Z') AT TIME ZONE 'UTC' AS timestamp) AS ts,
  "cs-uri-stem"   AS path,
  "sc-status"     AS status,
  cs_user_agent   AS user_agent,
  cs_referer      AS referrer,
  "c-country"     AS country,
  "x-edge-result-type" AS cache_result,

  -- normalize distribution ID to a human name
  CASE distributionid
    WHEN 'EDIST1EXAMPLE1' THEN 'cloudless'
    ELSE distributionid
  END AS site,

  -- who made the request (normalized vendor)
  CASE
    WHEN cs_user_agent LIKE '%GPTBot%'
      OR cs_user_agent LIKE '%ChatGPT-User%'
      OR cs_user_agent LIKE '%OAI-SearchBot%'  THEN 'openai'
    WHEN cs_user_agent LIKE '%ClaudeBot%'
      OR cs_user_agent LIKE '%Claude-User%'    THEN 'anthropic'
    WHEN cs_user_agent LIKE '%Googlebot%'
      OR cs_user_agent LIKE '%Google-Extended%' THEN 'google'
    WHEN cs_user_agent LIKE '%bingbot%'        THEN 'bing'
    WHEN cs_user_agent LIKE '%PerplexityBot%'
      OR cs_user_agent LIKE '%Perplexity-User%' THEN 'perplexity'
    WHEN cs_user_agent LIKE '%SemrushBot%'
      OR cs_user_agent LIKE '%SiteAuditBot%'   THEN 'semrush'
    WHEN cs_user_agent LIKE '%AhrefsBot%'
      OR cs_user_agent LIKE '%AhrefsSiteAudit%' THEN 'ahrefs'
    WHEN cs_user_agent LIKE '%AmazonBot%'
      OR cs_user_agent LIKE '%Amazonbot%'      THEN 'amazon'
    WHEN cs_user_agent LIKE '%Xpanse%'
      OR cs_user_agent LIKE '%paloaltonetworks%' THEN 'scanner'
    WHEN cs_user_agent LIKE '%CensysInspect%'
      OR cs_user_agent LIKE '%InternetMeasurement%' THEN 'scanner'
    ELSE 'other'
  END AS agent,

  -- why they accessed
  CASE
    WHEN cs_user_agent LIKE '%ChatGPT-User%'
      OR cs_user_agent LIKE '%Claude-User%'
      OR cs_user_agent LIKE '%Perplexity-User%' THEN 'retrieval'
    WHEN cs_user_agent LIKE '%Slackbot%'
      OR cs_user_agent LIKE '%WhatsApp%'
      OR cs_user_agent LIKE '%facebookexternalhit%' THEN 'preview'
    WHEN cs_user_agent LIKE '%CensysInspect%'
      OR cs_user_agent LIKE '%InternetMeasurement%'
      OR cs_user_agent LIKE '%Xpanse%'
      OR cs_user_agent LIKE '%paloaltonetworks%'
      OR cs_user_agent LIKE '%Scrapy%'          THEN 'scan'
    WHEN cs_user_agent LIKE '%python-httpx%'
      OR cs_user_agent LIKE '%Python/%'         THEN 'tool'
    ELSE 'unknown'
  END AS access_type,

  -- specific bot token (NULL for humans)
  CASE
    WHEN cs_user_agent LIKE '%GPTBot%'              THEN 'GPTBot'
    WHEN cs_user_agent LIKE '%ChatGPT-User%'        THEN 'ChatGPT-User'
    WHEN cs_user_agent LIKE '%OAI-SearchBot%'       THEN 'OAI-SearchBot'
    WHEN cs_user_agent LIKE '%ClaudeBot%'           THEN 'ClaudeBot'
    WHEN cs_user_agent LIKE '%Claude-User%'         THEN 'Claude-User'
    WHEN cs_user_agent LIKE '%Googlebot%'           THEN 'Googlebot'
    WHEN cs_user_agent LIKE '%Google-Extended%'     THEN 'Google-Extended'
    WHEN cs_user_agent LIKE '%bingbot%'             THEN 'bingbot'
    WHEN cs_user_agent LIKE '%PerplexityBot%'       THEN 'PerplexityBot'
    WHEN cs_user_agent LIKE '%Perplexity-User%'     THEN 'Perplexity-User'
    WHEN cs_user_agent LIKE '%SemrushBot%'          THEN 'SemrushBot'
    WHEN cs_user_agent LIKE '%SiteAuditBot%'        THEN 'SiteAuditBot'
    WHEN cs_user_agent LIKE '%AhrefsBot%'           THEN 'AhrefsBot'
    WHEN cs_user_agent LIKE '%AhrefsSiteAudit%'     THEN 'AhrefsSiteAudit'
    WHEN cs_user_agent LIKE '%AmazonBot%'
      OR cs_user_agent LIKE '%Amazonbot%'           THEN 'Amazonbot'
    WHEN cs_user_agent LIKE '%Xpanse%'
      OR cs_user_agent LIKE '%paloaltonetworks%'    THEN 'Xpanse'
    WHEN cs_user_agent LIKE '%CensysInspect%'       THEN 'CensysInspect'
    WHEN cs_user_agent LIKE '%Scrapy%'              THEN 'Scrapy'
    ELSE NULL
  END AS crawler,

  year, month, day
FROM cloudfront_logs_raw

Two derived columns do the heavy lifting:

agent — normalized identity from user-agent pattern matching. CloudFront URL-encodes user agents, so patterns match on encoded strings. SiteAuditBot catches Mozilla/5.0%20(compatible;%20SiteAuditBot....

access_type — the intent classification. The key distinction: retrieval (AI acting for a user, right now) vs crawler (autonomous indexing, building a future corpus). A retrieval event is a human consuming your content through an AI interface. A crawler event is infrastructure work.

The view is provisioned as an Athena named query in Terraform and run once after deploy:

resource "aws_athena_named_query" "cf_requests_view" {
  name      = "create_cloudfront_requests_view"
  database  = "signals"
  workgroup = module.athena.workgroup_id
  query     = "CREATE OR REPLACE VIEW cloudfront_requests AS ..."
}

Step 4: Grafana

With the view in place, Grafana’s Athena datasource queries it directly. No intermediate storage. No scheduled exports. Query on demand, results in seconds for day-level granularity.

A few panels that immediately become useful:

Traffic by agent over time:

SELECT date_trunc('day', ts) AS day, agent, count(*) AS requests
FROM cloudfront_requests
WHERE year >= 2026 AND status = 200
  AND path LIKE '/log/%'
GROUP BY 1, 2
ORDER BY 1

Retrieval events — LLM consumption trend:

SELECT date_trunc('day', ts) AS day, agent, count(*) AS requests
FROM cloudfront_requests
WHERE year >= 2026 AND access_type = 'retrieval'
GROUP BY 1, 2
ORDER BY 1

Top pages by crawler:

SELECT path, agent, count(*) AS requests
FROM cloudfront_requests
WHERE year >= 2026 AND access_type = 'crawler'
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 20

The dashboard that emerges shows the full picture: who reaches your content, through which channel, and which pages attract which actors.

The Stack

Component	Purpose
CloudFront Standard Logging v2	Raw request data → S3
Glue external table	Schema-on-read + partition projection
Athena view	Classification: agent + access_type
Grafana Athena datasource	Visualization

No streaming pipeline. No Lambda enrichment. No database. Logs land in S3, Athena reads them in place, Grafana queries Athena. The classification logic lives in a SQL view — cheap to update, no reprocessing needed.

---

Part 7 of the Signals series — small systems that observe larger systems.

Previous: Signals #6: Build a Visibility Layer | Next: Signals #8: Measuring Publication