DYNAMIC MATHEMATICAL SPECIFICATION

PowerDNS Telemetry Logic & Dynamic Boundaries

Every metric, gauge, graph, and ranking displayed across the BasementBuild PowerDNS Cluster Dashboard (dns-dashboard.html and guest.html) is governed by strict, deterministic mathematical formulas and verifiable database queries. Below is the complete specification detailing the exact start times, dynamic boundary dates, and calculation formulas used across the system.

1. Queries Today (Time Boundaries & Reset Cycle)

The Queries Today metric measures the cumulative number of DNS queries resolved across the entire 3-node cluster for the current calendar day (Loading date...).

Start Time

00:00:00 AM (Midnight local time) at the beginning of the current calendar day.

End Time

23:59:59 PM (Midnight local time) at the conclusion of the current calendar day.

Reset Cycle

At 00:00:00 AM every night, the counter automatically resets to 0 and begins accumulating fresh queries.

Database Query Syntax (Pi-hole FTL SQLite):

SELECT COUNT(*) FROM queries WHERE timestamp >= strftime('%s', 'now', 'start of day');

2. 90-Day Rolling Queries (Dynamic Window Mechanics)

The 90-Day Rolling Queries metric calculates total resolution throughput across a continuous 90-day trailing window.

Starting Date (90 Days Ago)

Calculating...

Ending Date (Today)

Calculating...

Rolling Window Mechanics

As a new day begins at midnight, the 91st oldest day drops off the back of the calculation, and today's new total is added to the front.

Database Query Syntax (Pi-hole FTL SQLite):

SELECT COUNT(*) FROM queries WHERE timestamp >= strftime('%s', 'now', '-90 days');

3. Current QPS (Queries Per Second) Formula

Real-time query workload (QPS) is calculated dynamically over 3-second polling intervals by sampling total query counters across the load balancer and backend triad:

Current QPS = (Queries_t2 - Queries_t1) / (t2 - t1)

4. RAM Cache Hitrate Formula

Efficiency is measured using dnsdist-lb's 30,000-entry in-RAM packet cache:

RAM Cache Hitrate (%) = (Cache_Hits / (Cache_Hits + Cache_Misses)) * 100

5. Top 15 Blocked Domains Ranking Logic

Blocked requests are parsed from Pi-hole FTL query logs (where status IN (1, 4, 5, 6, 7, 8, 9, 10, 11)) and grouped by domain, sorted in strict descending order:

SELECT domain, COUNT(*) AS block_count FROM queries
WHERE status IN (1, 4, 5, 6, 7, 8, 9, 10, 11)
GROUP BY domain ORDER BY block_count DESC LIMIT 15;

6. Metric Model Comparison (Calendar Day vs. 24-Hour Sliding Window)

To ensure total transparency when cross-referencing telemetry across different management interfaces, the table below highlights how the BasementBuild Cluster Reporting Engine compares against standard Pi-hole and dnsdist defaults:

Reporting Model	Time Window	Reset Behavior	Primary Use Case
Calendar Day Model (Official)	00:00:00 AM to 23:59:59 PM	Resets to 0 at Midnight (00:00)	BasementBuild Dashboard, XLS & PDF Audit Reports
24-Hour Sliding Window	Trailing 24 hours (e.g. 4 PM to 4 PM)	Continuous rolling total	Standard Pi-hole Web UI Default (/admin)
Process Uptime Cumulative	Since last service reboot	Resets only on daemon restart	Raw dnsdist-lb Process Statistics

Engineering Rationale: The Calendar Day model was selected for the BasementBuild Cluster Engine because it produces fixed, predictable, and immutable 24-hour totals for every date. This eliminates floating numbers and provides 100% mathematical auditability for daily PDF and XLS exports.

7. Mathematical Proof & Audit Pipeline

Every single query resolved across ns1, ns2, and ns3 is logged in immutable FTL SQLite database files (/etc/pihole/pihole-FTL.db) with exact timestamps, client IPs, domain names, and block statuses. These raw logs provide 100% mathematical proof verifying all dashboard metrics beyond a doubt for daily downloadable XLS (Excel) and PDF audit reports.