# code::core DNS Analytics Course Guide ## Lesson-Based Playbook from Home Lab to ISP Scale Version: 2.0 Last Updated: May 2026 Audience: Network Engineers, DNS Admins, DevOps, Homelab Builders --- ## Course Promise This repository teaches you how to build and operate a DNS analytics platform as a sequence of lessons. You will not just run commands. You will understand what each command changes, why it matters, and how to verify it. --- ## Platform Context Core pipeline: 1. Resolver layer: BIND9 answers DNS and logs every query. 2. Ingest layer: Python parser writes structured records to PostgreSQL. 3. Aggregation layer: rollups produce daily, weekly, monthly summaries. 4. Decision layer: SQL reports convert DNS traffic into operational decisions. --- ## Lesson 0: Pre-Lab Baseline Objective: verify your host is ready before installation. Interpret: - This lesson teaches baseline validation before installation. - You verify host readiness so later troubleshooting is based on service behavior, not missing prerequisites. Lab: - OS: Ubuntu 24.04 LTS - Minimum: 8 GB RAM, 100 GB SSD - Privilege: sudo/root - Time sync: timedatectl status Run: - Confirm Ubuntu version and package manager health. - Check available memory and disk. - Confirm time synchronization status. Checkpoint: - Package manager works. - Disk and memory are sufficient. - Clock is synchronized. --- ## Lesson 1: Resolver Foundation (BIND9) Objective: build a trusted recursive resolver that can emit reliable query telemetry. Interpret: - This lesson teaches that resolver policy defines both data quality and security posture. - You verify recursion controls and logging behavior before trusting analytics outputs. Run: - Install bind9, bind9utils, dnsutils. - Configure ACL and forwarders. - Enable logging with timestamps. Checkpoint: - named-checkconf returns clean. - dig to known domains succeeds. - /var/log/named/queries.log receives new lines. --- ## Lesson 2: Data Model (PostgreSQL) Objective: store raw DNS events and analytics summaries with predictable query performance. Interpret: - This lesson teaches separation between raw event storage and reporting aggregates. - You verify that schema design supports both forensic detail and fast analytics queries. Run: - Create dns_analytics DB and dns_user role. - Create raw and rollup tables. - Add indexes aligned to query patterns. Checkpoint: - Schema objects exist. - Seed data inserts cleanly. - Basic SELECT queries return quickly. --- ## Lesson 3: Ingestion Service (Python + systemd) Objective: build a resilient log-to-database ETL loop. Interpret: - This lesson teaches resilient ingestion through batching and managed service execution. - You verify that write throughput and auto-restart behavior keep data flowing continuously. Run: - Create virtual environment. - Install psycopg2-binary. - Deploy ingestor.py and systemd unit. Checkpoint: - Service is active. - Journal logs show periodic flushes. - dns_queries row count increases during DNS activity. --- ## Lesson 4: Aggregation and Scheduling Objective: transform high-volume raw data into stable business metrics. Interpret: - This lesson teaches idempotent aggregation as the foundation of reliable reporting. - You verify that scheduled reruns update metrics safely without creating duplicate summaries. Run: - Configure rollup_daily.sql, rollup_weekly.sql, rollup_monthly.sql. - Add cron schedule. Checkpoint: - Re-running rollups does not duplicate data. - New daily stats appear on schedule. --- ## Lesson 5: Analytics for Decisions Objective: answer network questions, not just generate tables. Interpret: - This lesson teaches how analytics outputs map to concrete network decisions. - You verify that domain concentration and failure patterns translate into actionable tuning priorities. Run: - Top domains, category distribution, peak-hour heatmap, error-rate analysis. Checkpoint: - Reports align with observed user behavior. - Weekly deltas are explainable. --- ## Lesson 6: MikroTik Home Network Lab (10.10.10.0/24) Objective: run the full platform in a homelab with local authoritative DNS. Interpret: - This lesson teaches hybrid operation of local authoritative DNS and recursive internet analytics. - You verify both intended zone inventory and real client query usage from the same data platform. Lab assumptions: - Network: 10.10.10.0/24 - Router and DNS gateway: 10.10.10.10 - Local zone: codeandcore.home Run: - Configure MikroTik DHCP network domain to codeandcore.home. - Set clients to DNS 10.10.10.10. - Add BIND zone codeandcore.home and host records. - Mirror zone records into dns_zone_records table. - Query local zone usage from dns_queries. Checkpoint: - dig @10.10.10.10 nas.codeandcore.home A succeeds. - Local-zone queries appear in analytics. - Zone inventory and zone usage can both be proven from PostgreSQL. --- ## Lesson 7: Scale Transition (toward 2M subscribers) Objective: preserve schema meaning while changing transport and compute topology. Interpret: - This lesson teaches scale migration without changing metric meaning. - You verify that transport and compute upgrades preserve report comparability over time. Run: - Replace text logs with dnstap stream. - Replace single-process ingest with distributed stream workers. - Move to TimescaleDB patterns for scale. Checkpoint: - Reports before and after migration remain comparable. --- ## Lesson 8: Operational Discipline Objective: make this platform maintainable for teams. Interpret: - This lesson teaches operations discipline through repeatable runbooks and controls. - You verify that monitoring, backups, and access reviews sustain long-term data reliability. Run: - Backups for rollup tables. - Monitoring for ingestor health, rollup completion, partition creation. - Access control reviews. Checkpoint: - You can recover from failures using runbooks, not ad-hoc fixes. --- ## Suggested Study Order 1. index.html first (interactive teaching manual) 2. install.sh second (automation walkthrough) 3. SQL_QUERIES.md third (guided analytics labs) 4. CLOUDFLARE_DEPLOYMENT.md fourth (publishing and edge integration) --- ## Fast Start Use this block as a pipeline smoke test in sequence: install, resolve one public domain, then watch ingestor logs. The first command builds services, the second confirms resolver behavior, and the third confirms telemetry flow into ingestion. Run them in order so each result explains the next layer. ```bash sudo bash install.sh dig @127.0.0.1 google.com A sudo journalctl -u dns-ingestor -f ``` --- ## Success Criteria You are done when all are true: - Resolver answers queries and logs them. - Ingestor writes continuously to dns_queries. - Rollups complete on schedule. - You can explain traffic, failures, and peak windows from SQL. - In MikroTik lab mode, codeandcore.home records resolve and are visible in analytics.