Klickhaus - CDN Analytics Dashboard

February 12, 2026 · View on GitHub

A real-time analytics dashboard for CDN log analysis, built with ClickHouse and vanilla JavaScript.

CDN Analytics Dashboard

Features

  • Real-time request monitoring - Stacked area chart showing requests over time, color-coded by status (2xx/3xx green, 4xx yellow, 5xx red)
  • Multi-dimensional breakdowns - Analyze traffic by:
    • Status codes and ranges
    • Hosts and forwarded hosts
    • Content types
    • Cache status (HIT, MISS, etc.)
    • Paths and referers
    • User agents and IP addresses
    • Request types and backend types
    • HTTP methods and datacenters
    • ASN (Autonomous System Numbers)
  • Interactive filtering - Click to filter or exclude any dimension value
  • Copy to spreadsheet - Copy any facet's data as TSV with one click (copy button) for analysis in Excel/Sheets
  • Flexible time ranges - Last hour, 12 hours, 24 hours, or 7 days
  • Dark mode support - Automatic theme based on system preference
  • Query caching - Intelligent cache TTLs based on time range
  • Fast facet queries - Pre-aggregated facet table with proportional sampling for longer time ranges

Architecture

The dashboard queries a ClickHouse database containing unified CDN logs from Cloudflare and Fastly:

Cloudflare Logpush ──► cloudflare_http_requests (1-day TTL)
                              │
                    cloudflare_http_ingestion (MV)
                              │
                              ▼
                     cdn_requests_v2 (2-week TTL)
                              ▲
                    fastly_ingestion (MV)
                              │
Fastly HTTP Logging ─► fastly_logs_incoming2 (1-day TTL)

Both CDN sources use direct HTTP logging to ClickHouse with async inserts for high-throughput ingestion.

Usage

General Dashboard

  1. Open dashboard.html in a browser (or visit klickhaus.aemstatus.net, fallback: maisonclic.aemstatus.net)
  2. Log in with your ClickHouse credentials
  3. Use the time range selector and host filter to narrow down results
  4. Click on any breakdown value to filter, or use the "Exclude" button to exclude it

Delivery Dashboard

A focused dashboard for core delivery monitoring is available at delivery.html. It excludes backend, admin, RUM, and docs services (15 hosts) via hard-coded SQL filters, showing only user-facing delivery traffic. All main dashboard facets are available; a curated subset is shown by default. See js/delivery-main.js for the exclusion list.

Copy Facet Data

Click the "copy" button on any facet header to copy its data as TSV. Paste directly into Excel, Google Sheets, or Numbers.

URL Parameters

The dashboard state can be controlled via URL parameters for bookmarking and sharing:

ParameterDescriptionExample
tTime range: 15m, 1h, 12h, 24h, 7d?t=24h
nTop N facet values: 5, 10, 20, 50, 100?n=20
hostFilter by host (substring match)?host=example.com
viewView mode: logs for logs table?view=logs
tsQuery timestamp (ISO 8601)?ts=2025-01-15T12:00:00Z
filtersFacet filters (JSON array)?filters=[{"col":"\request.host`","value":"example.com","exclude":false}]`
pinnedPinned log columns (comma-separated)?pinned=timestamp,response.status,request.url
hideHide UI controls (comma-separated)?hide=timeRange,topN,logout

Hide Parameter Options

The hide parameter accepts these control names:

  • timeRange - Time range selector
  • topN - Top N selector
  • host - Host filter input
  • refresh - Refresh button
  • logout - Logout button
  • logs - Logs/Filters toggle button

Examples

# Lock to 24h view with hidden controls
?t=24h&hide=timeRange,logout

# Show logs view with specific columns pinned
?view=logs&pinned=timestamp,response.status,request.method,request.url

# Pre-filtered view for a specific host
?host=example.com&t=1h&n=10

# Embed-friendly minimal UI
?t=1h&hide=timeRange,topN,host,refresh,logout

User Management

Scripts in scripts/ manage dashboard access (require admin credentials):

# Add a new read-only user
node scripts/add-user.mjs <admin-user> <admin-password> <new-username> [password]

# Rotate a user's password
node scripts/roll-user.mjs <admin-user> <admin-password> <username>

# Remove a user
node scripts/drop-user.mjs <admin-user> <admin-password> <username>

New users receive read-only access (SELECT on cdn_requests_v2).

Local Development

npm install
npm start

This starts a development server with auto-reload. The port is deterministic per worktree and printed on startup. Use node scripts/dev-server.mjs --dry-run to get the port without starting the server.

For ClickHouse CLI access:

clickhouse client --host s2p5b8wmt5.eastus2.azure.clickhouse.cloud \
  --user default --password '<password>' --secure

Data Schema

The primary table cdn_requests_v2 includes:

Column GroupExamples
Coretimestamp, source, request.host
CDNcdn.cache_status, cdn.datacenter, cdn.time_elapsed_msec
Clientclient.ip, client.country_name, client.asn
Requestrequest.url, request.method, request.headers.*
Responseresponse.status, response.body_size, response.headers.*
Helixhelix.request_type, helix.backend_type

Runbook

No data in dashboard (Cloudflare Logpush disabled)

Symptoms:

  • Dashboard shows no data or stale data
  • cloudflare_http_requests table is empty (1-day TTL expires old data)
  • cdn_requests_v2 has no recent rows

Cause: Cloudflare Logpush jobs auto-disable after repeated delivery failures (e.g., ClickHouse instance was stopped, ran out of credits, or network issues).

Diagnosis:

# Check if source tables have recent data
clickhouse client ... --query "
  SELECT count(), max(EdgeStartTimestamp)
  FROM helix_logs_production.cloudflare_http_requests"

# Check Logpush job status via Cloudflare API
curl -s "https://api.cloudflare.com/client/v4/zones/<zone_id>/logpush/jobs" \
  -H "Authorization: Bearer <api_token>" | jq '.result[] | {id, name, enabled, last_complete, last_error, error_message}'

Look for "enabled": false and error_message containing delivery errors.

Resolution:

  1. Ensure ClickHouse instance is running and accessible
  2. Re-enable each Logpush job:
curl -X PUT "https://api.cloudflare.com/client/v4/zones/<zone_id>/logpush/jobs/<job_id>" \
  -H "Authorization: Bearer <api_token>" \
  -H "Content-Type: application/json" \
  -d '{"enabled": true}'
  1. Repeat for all zones (aem.live, aem.page, aem-cloudflare.live, aem-cloudflare.page, aem.network, da.live)
  2. Verify data flow: check last_complete updates and rows appear in ClickHouse

API Token Requirements: Create a Cloudflare API token with Zone → Logs → Edit permission for all relevant zones.

ClickHouse memory limit exceeded (OOM errors)

Symptoms:

  • Dashboard queries fail with MEMORY_LIMIT_EXCEEDED errors
  • Error message shows memory usage near or exceeding the limit (e.g., "would use 30.99 GiB, maximum: 28.80 GiB")
  • Queries against system.query_log or system.asynchronous_metric_log also fail

Cause: High-volume Fastly log ingestion combined with dashboard queries and ClickHouse Cloud internal monitoring can exceed available memory. With 9 Fastly services sending logs every 5 seconds, memory pressure builds from concurrent INSERT operations.

Diagnosis:

# Check current memory usage
clickhouse client ... --query "
  SELECT metric, round(value / 1024 / 1024 / 1024, 2) as gb
  FROM system.asynchronous_metrics
  WHERE metric IN ('CGroupMemoryTotal', 'CGroupMemoryUsed', 'MemoryResident')"

# Check memory trend over time
clickhouse client ... --query "
  SELECT toStartOfMinute(event_time) as minute,
         round(max(CurrentMetric_MemoryTracking) / 1024/1024/1024, 2) as max_mem_gb
  FROM system.metric_log
  WHERE event_time > now() - INTERVAL 30 MINUTE
  GROUP BY minute ORDER BY minute"

# Check INSERT volume by table
clickhouse client ... --query "
  SELECT substring(query, position(query, 'helix_logs_production.') + 22, 40) as tbl,
         count() as inserts,
         round(avg(written_rows), 0) as avg_rows
  FROM system.query_log
  WHERE event_time > now() - INTERVAL 30 MINUTE
    AND type = 'QueryFinish'
    AND query LIKE 'INSERT INTO helix_logs_production.%'
    AND written_rows > 0
  GROUP BY tbl ORDER BY inserts DESC
  SETTINGS max_memory_usage = 500000000"

Resolution options (in order of impact):

  1. Increase ClickHouse Cloud memory (immediate relief):

    • In ClickHouse Cloud console, increase "Minimum memory per replica"
    • Recommended: 64 GB for production workloads with high ingestion volume

  2. Increase Fastly logging batch sizes (reduce INSERT frequency):

    # Clone active version, update logging endpoint, activate
# For each service, increase max_entries and max_bytes:
curl -X PUT -H "Fastly-Key: $FASTLY_TOKEN" \
  "https://api.fastly.com/service/$SERVICE_ID/version/$VERSION/logging/https/Clickhouse" \
  -d '{"request_max_entries": 100000, "request_max_bytes": 50000000}'
    • request_max_entries: 10,000 → 100,000 (10x)
    • request_max_bytes: 5 MB → 50 MB (10x)

  3. Increase Fastly logging period (fewer flushes per POP):

    curl -X PUT -H "Fastly-Key: $FASTLY_TOKEN" \
  "https://api.fastly.com/service/$SERVICE_ID/version/$VERSION/logging/https/Clickhouse" \
  -d '{"period": 60}'
    • period: 5 → 60 seconds (logs delayed up to 60s)
    • Trade-off: increased latency before logs appear in ClickHouse

Fastly services with ClickHouse logging:

ServiceService IDDomain
helix5 (main)In8SInYz3UQGjyG0GPZM42*.aem.page, *.aem.live
configSIDuP3HxleUgBDR3Gi8T24config.aem.page
admin6a6O21m8WoIIVg5cliw7BWadmin.aem.page
www00QRLuuAsVNvsKgNWYVCbbwww.aem.live
APIs2dVksBUsvEKaaYF13wIh6api.aem.live
formUDBDj4zfyNdZEpZApUqhL3form.aem.page
pipelinecHpjIl1WNRu9SFyL1eBSj3pipeline.aem-fastly.page
staticItVEMJu5q2pJE3ejseo0W6static.aem.page
mediaatG7Eq66bH88LhbNq7Fqq2media.aem-fastly.page

ClickHouse HTTP insert limits (for reference):

  • async_insert_max_data_size: 100 MB per query
  • max_insert_block_size: ~1M rows per block

License

Apache-2.0