Reading Your Pi-hole Query Log: What Your Devices Actually Contact

A flat plateau of queries across the small hours, on a night when everybody in the house was asleep, is one of the more useful things a home filter will ever show you. People sleep; devices do not. The usual culprit is something like a cheap Wi-Fi smart plug hammering a vendor endpoint that no longer exists, over and over, all night, every night, quite possibly for months. Nothing looks broken from the outside, either: the plug switches its lamp on and off perfectly well. It is simply generating more lookups than every phone and laptop in the house put together, and the only reason anybody notices is that somebody glanced at a graph. That is what a query log is good for. Not watching the people you live with, which is the use most articles hint at, but seeing what your own hardware does when nobody is looking.

what the log shows, and what it does not

Each row is a name lookup: when it happened, which client asked, what name was requested, the record type, and what your filter did about it. That is the whole extent of it, and the limits matter as much as the contents.

The log tells you	The log cannot tell you
A device asked for a domain, at a time	Which page or URL was opened
Whether the answer was blocked, cached or forwarded	What was sent or received afterwards
Which client IP made the request	Who was holding the device
How often a name is requested	Whether a human or a background service asked

That second column is the honest part. A domain in the log means something on the device resolved a name. It could be a person tapping a link, an app refreshing in the background, a preloaded suggestion in a browser’s address bar, or an email client fetching a remote image. People draw confident conclusions from query logs that the data does not support, and I would rather you did not.

The status column is where the real information sits. Pi-hole distinguishes blocked by a list, blocked by a regex rule, answered from cache, and forwarded upstream. Reading those correctly saves a lot of confusion, because a domain appearing repeatedly with “cached” against it is not being contacted repeatedly at all.

finding the chattiest device in the house

The dashboard’s top-clients panel answers this in one glance, and it is usually enough. When I want something more specific I go at the database directly, since Pi-hole stores everything in SQLite.

# Which clients asked the most in the last day?
sudo sqlite3 /etc/pihole/pihole-FTL.db \
  "SELECT client, COUNT(*) AS n FROM queries
   WHERE timestamp > strftime('%s','now','-1 day')
   GROUP BY client ORDER BY n DESC LIMIT 10;"

# Which domains does one device keep asking for?
sudo sqlite3 /etc/pihole/pihole-FTL.db \
  "SELECT domain, COUNT(*) AS n FROM queries
   WHERE client='192.168.1.42'
     AND timestamp > strftime('%s','now','-1 day')
   GROUP BY domain ORDER BY n DESC LIMIT 20;"

# Watch queries live while you reproduce something
pihole -t

Expect the ranking to surprise you. A typical order is the television first, then whichever phone gets the most use, then a robot vacuum or similar appliance, then everything else a long way behind. The television is not doing anything sinister; smart TV platforms simply check in constantly, and a device that cannot run any software you choose has no way to be told otherwise.

recognising telemetry when you see it

Telemetry has a shape. Once you have seen it a few times you can spot it in seconds.

• Metronomic timing. Requests at an exact interval, all night, are a background service. Humans are irregular and they sleep.
• Vendor-shaped names. Subdomains built from words like metrics, events, logs, collect, beacon or telemetry under a manufacturer’s domain.
• Activity with the screen off. A phone lookup burst at four in the morning is an app syncing, not a person.
• Regional analytics hosts. Plenty of devices sold here talk to analytics endpoints in Singapore or Mumbai belonging to the platform rather than the brand on the box.

Some of that is load-bearing. Refuse the wrong endpoint on a streaming device and an app will hang on a blank screen, because the developer never handled the case where its analytics host does not resolve. So change one thing at a time, then use the thing normally for a day before deciding you have improved anything.

the retrying appliance, and other loops

The pattern behind that overnight plateau is worth learning properly, because it is common and easy to fix. A device asks for a name, gets NXDOMAIN or a blocked answer, treats that as a transient failure, and retries immediately. Forever.

# Names asked for far more often than any human activity would explain
sudo sqlite3 /etc/pihole/pihole-FTL.db \
  "SELECT client, domain, COUNT(*) AS n FROM queries
   WHERE timestamp > strftime('%s','now','-6 hours')
   GROUP BY client, domain HAVING n > 500
   ORDER BY n DESC;"

Anything in that output is either badly written or pointed at something that no longer exists. Three fixes, in the order I would try them: update the device’s firmware, in case the vendor moved the endpoint; put the device on a list that stops it retrying by giving it an immediate clean refusal; or accept it and move on, because a few thousand wasted lookups a day on a local resolver costs you very little.

One thing this teaches you about dashboard percentages. A single retrying appliance can dominate your blocked-query figure completely, which is why that number tells you about your hardware rather than the quality of your filtering.

diagnosing breakage, which is the real job

Nine times out of ten, the reason to open the log at all is that somebody said a site is not working. The routine takes two minutes.

1. Filter the log by that device’s IP address, then reproduce the failure while watching.
2. Look at the blocked rows in the seconds around it. The offender is usually a plausible-looking name near the site’s own domain.
3. Confirm with pihole -q on that domain to see which of your lists carries it.
4. Allowlist that single domain, reload, and stop as soon as it works.
5. Write down why. Six months later you will not remember what a bare allowlist entry was for.

If the log shows no blocked queries at all around the failure, your filter is innocent and you can go and look at the site, the ISP, or the device. Ruling yourself out is worth as much as finding the culprit, and it takes the same two minutes. The list-side half of this, including how to keep your list set small enough to debug, is in choosing and tuning DNS blocklists.

the log is not a window into your family

I want to be direct about this, because the tooling makes it easy and the temptation is real. You can sit at that admin page and read, device by device, what the people you live with have been looking at. Do not.

Partly because the data is weak evidence, as the table above showed. A domain does not tell you what was read, and background activity is indistinguishable from intent. And partly because the trust you spend is not recoverable. If you have set up filtering for a household, the arrangement works when everyone knows what is filtered and what is kept, which is the whole argument I made in the guide to filtering for a family home.

A rule I hold to: open the log to answer a question about a machine, never to answer a question about a person.

privacy hygiene for the log itself

That database is the most sensitive file on your network. Treat it accordingly.

• Shorten retention. Pi-hole keeps 91 days of queries by default (database.maxDBdays in its FTL configuration); a few days is plenty for diagnosis. Long history mostly creates risk.
• Use the privacy levels. Pi-hole can anonymise clients, hide domains, or log nothing at all while still filtering normally. AdGuard Home has comparable settings.
• Password the admin page and keep it on the LAN. Never expose it to the internet. If you need it remotely, reach it over a VPN.
• Remember your backups. A snapshot of the Pi includes the query database, so the retention you chose applies to the backup copy too.
• Harden the host. The machine holding this deserves the basics: keys instead of passwords, a firewall, automatic updates. My Ubuntu server security checklist is the short version.

turning findings into a few sensible rules

A week of watching should produce a handful of changes, not a project. Two is a realistic number, and a set that size barely needs touching afterwards.

1. A short group of telemetry domains refused for the television and the vacuum, and nothing else.
2. Two allowlist entries with comments attached, both for payment flows that must never break again.

Then close the dashboard. A filter you check daily is a hobby; a filter you check when something breaks is infrastructure, and the second one is what you actually want in a home.

FAQ

why does one device barely appear in the log?

Because it is not asking you. Either it has a resolver address of its own configured or compiled in, or an app on it is using encrypted DNS directly. A suspiciously quiet device is the signal to check what resolver it is using, not evidence that it is well behaved.

how long should i keep query history?

Long enough to debug something reported a few days late, so a week is a reasonable default and a month is generous. Anything longer is a liability sitting on an SD card. If you only ever look at the live tail, set the privacy level so almost nothing is stored and keep the filtering exactly as it was.

does a big database slow down pi-hole?

Filtering stays quick, because that check happens in memory. What gets slower is the admin interface, particularly the long-range graphs on a Pi with an older SD card. If the dashboard has become sluggish, shortening retention usually fixes it, and it reduces what you are storing about your household at the same time.

Find the two devices doing something daft, and then leave the log alone. The value is in what you learn about your own hardware, not in watching a graph.

Update history: named Pi-hole’s retention setting and its default, removed a cache rule that overrode published expiry times, and updated two Pi-hole documentation links.

Sources

• Pi-hole FTL database documentation — the queries table, status values and retention settings used in the examples above.
• Pi-hole command reference — the tail, query and allowlist subcommands.
• RFC 1035 — Domain Names — what a DNS query actually contains, and therefore the ceiling on what any log can show.