Since the data is all there, I was interested to get all the unique domains hit by a client. Can someone help with the command if possible? Or say top 100 hit by the client? The UI doesn't show that and would be great if I can extract that info somehow
I think Telnet API - Pi-hole documentation has everything you need.
Thansk @DanSchaper
New to this FTLDNS. So I am running PiHole in a Docker on a NAS on port 8877
Docker is running on Synology NAS so it doesn't have telnet or nc command ootb.
So from my PC should I do?
telnet 192.168.1.199 8877 and then run command? gives errors and quits.. and connecting to port 4711 doesn't work as i believe its not open to outside nas
Also I can see commands like "top-domains" but can't really see which combination will give me top-domain per client or all domains of a client
Thanks for your help!!! 
(apologies in advance, currently mobile so probably lacking accuracy!)
I was doing something similar to this the other day. I ended up making a copy of the ftl database onto my windows machine and using an sqlite ide to sort through the data.
Similar can also be achieved by directly running the queries inside the container, something like
docker exec -it [container name] sqlite3 /etc/pihole/pihole-FTL.db "select domain, count(1) from queries group by domain order by count(1) desc"
(Might have the dB name wrong there...)
That should get you the most queried domains, in descending order. Obviously you can expand on that if you want to do it by client
Also, from what I recall, the telnet interface for FTL only listens on 127.0.0.1, so using this remotely is not possible.
Awesome @PromoFaux this is great and works.. not great at SQL but can you help with a command to list them desc with count for a particular client?
You can set a pihole-FTL.conf flag to open the 4711 to all interfaces. In this case that may not do much as you'd still need to open the port on Docker for the container.
Thanks @DanSchaper noted... can you help with the sql query modification ? would appreciate it
This will give you the same, but counted by client
SELECT Domain, client, count(1)
FROM queries
GROUP BY domain, client
ORDER BY count(1) desc
if you want to make that variable, then for example to get all counts for 192.168.1.1 do this:
SELECT Domain, count(1)
FROM queries
WHERE client = '192.168.1.1'
GROUP BY domain
ORDER BY count(1) desc
Awesome @PromoFaux
Thanks .. worked like a charm...
would be so nice to see this type of easy filter in UI or say a top 10 page which we can sort in client.. will be really helpful
Just a curious question.. how many days worth of data is in the DB?
Glad that worked! This is a quite a nice interactive tutorial for learning some SQL basics:
Maybe with your new found SQL skills you could take a stab at it 
2 Likes
The default is 365 days of data in the database. This can be changed with a user configurable option, MAXDBDAYS
1 Like
Would it be possible to enhance the audit page? The 'hits' entry currently doesn't have a hyperlink. It would be great to turn this into a link, showing a result for the select domain, listing all the clients that queried the domain.
something like this, as the result from a click on the 'hits' hyperlink:
Ultimately, anything is possible, it's "just" a case of someone being willing to trick the silicone into to doing it for them (and others). Contributions, as always, are welcome.
That's not to say it's a bad idea, jpg, just that it might be a while before someone picks it up.
This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.