Are recent new hostnames available via API?

Feature Requests
1

To be able figure out if the blocklists need adjustments i regularly check the last lines of pihole-ftl.db to see what hostnames where recently queried on my network that i don't know about yet.
Copying the db over from my pihole and checking them manually with a sql lite GUI is a bit annoying.
It would be cool if i had a hostname feed in my home-assistant integration.
Therefore, does the API allow me to query this information directly?

2

The short answer is: No.

The longer one: It could surely be added so I moved your post into the Feature Requests section.

I have a few questions at this point:

  1. With "hostnames" you mean "queries domains", right? Or do you really mean hostnames of clients within your network like new devices showing up?

  2. How do you determine if you have seen domains/hostnames before? This may become clearer when we have the answer to no. 1.

If you want to see, e.g., the ten most recently added (= never seen before) domains/hostnames to the database, you could easily run, e.g.,

$ pihole-FTL sql /etc/pihole/pihole-FTL.db "SELECT content from addinfo_by_id WHERE type = 1 ORDER BY id DESC LIMIT 10;"

Example output from my Pi-hole:

gaug.es
track.customer.io
alldcs.outbrain.org
pixel-eu.rubiconproject.net.akadns.net
alt5.mobile-gtalk.l.google.com
www.rfaweb.org
kp4-server-kp4-prd-ext-5841688415.eu-west-1.elb.amazonaws.com
rt.marphezis.com
rr4.sn-986fsnf5.googlevideo.com
rr4.sn-4g5e6nz7.googlevideo.com

Or you can get fancier and do something like

SELECT a.content AS domain, q.timestamp as timestamp FROM addinfo_by_id a
    JOIN query_storage q ON q.domain = a.id
    WHERE a.type = 1 AND timestamp > unixepoch() - 300
    GROUP BY domain HAVING MIN(timestamp)
    ORDER BY a.id DESC;

to get the same information but this time together with the first timestamp this query showed up, considering only new domain in the last five minutes (300 seconds) and without a limit on the number of domains to be reported. While being much more work than then easier query above, it is still sufficiently fast as Pi-hole organizes its data in a way that makes such queries yield good performance.[1]

You see, everything is easily possible if you interact with the database directly. However, with great power comes great responsibility, so we cannot add every corner-case to the API. You will have to convince us that this can be made a feature that is used by more than three people :slight_smile:

  1. This particular query will first search q by using the index idx_queries_timestamp in mode timestamp>?, then use a Bloom filter on a.id and then searches a on its primary key (id). It creates two temporary B-trees to efficiently implement first the GROUP BY (to get only the minimum matching timestamp) and then the ORDER BY (to get the most recent result at the top). ↩︎

3

You don't need to use the database. This information is also contained in the dnsmasq log at /var/log/pihole/pihole.log in text format.

Example:

sudo grep query /var/log/pihole/pihole.log | grep "[A]" | tail -n10
Jan 20 13:55:49 dnsmasq[7445]: query[A] swallow.apple.com from 192.168.0.121
Jan 20 13:55:49 dnsmasq[7445]: query[A] ocsp2.apple.com from 192.168.0.121
Jan 20 14:19:43 dnsmasq[7445]: query[AAAA] ipv4only.arpa from 192.168.0.121
Jan 20 14:26:18 dnsmasq[7445]: query[A] gspe1-ssl.ls.apple.com.edgesuite.net from 192.168.0.121
Jan 20 14:26:21 dnsmasq[7445]: query[A] bag-cdn.itunes-apple.com.akadns.net from 192.168.0.121
Jan 20 14:26:21 dnsmasq[7445]: query[A] e673.dsce9.akamaiedge.net from 192.168.0.121
Jan 20 14:27:09 dnsmasq[7445]: query[A] www.apple.com from 192.168.0.121
Jan 20 14:29:53 dnsmasq[7445]: query[A] time.g.aaplimg.com from 192.168.0.121
Jan 20 14:44:28 dnsmasq[7445]: query[A] gateway.icloud.com from 192.168.0.121
Jan 20 14:44:29 dnsmasq[7445]: query[A] ocsp2-lb.apple.com.akadns.net from 192.168.0.121
4

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.