I would like the ability to filter out sites from the log. Makes troubleshooting much easier. Sites like dns.msftncsi.com and ring doorbells make alot of noise. I know we have the ability to only show blocks, but a filter would be nice.
Have you checked the settings page?
I am looking on the API Top lists and I see an area. Does this also filter out from the Query Log?
I can't test atm, but it should say "Privacy settings (Statistics / Query Log)"
Yes, there are Privacy Settings. Show permitted domain entries and show blocked. I use those too, but when troubleshooting an ad on a site, I use the Show permitted domains. The issue is when I show permitted domains, there are lots of repetitive entries from ring, etc. I have to fish through to see the site that was allowed. If I can just filter out some of these, it makes it much easier to see the site that was allowed.
Please have a look if this would be something for you:
Yes. This is a nice feature too. On my idea, I think what would be easier is looking at the top lists in the settings, what about another box like this.
Top Lists
Exclude the following Domains from being shown in
Top Domains/Top Advertisers Top Clients
(BOX FOR ENTRIES) (BOX FOR ENTRIES)
And another box for Query Log.
So on the query log, we can have addresses filtered out from the list.
1 Like
I'm not sure why that would be beneficial in comparison to the Audit log. Agreed, you have to do all the approving of blocked and permitted domains once at the beginning but then only once in a while and you will immediately see how many times an yet unknown domain has been queried.
I am often tasked with finding why ads are showing up on pages. So I have to find by time and date stamp in the query log and look for allowed domains. Than try sifting through the sites until I find the ad site.
If you just go ahead and visit it a second time it will be on the top of the Query Log page...
Yes, this works for filtering things I want to see. When I am troubleshooting ads that should not be there I do not know what I am looking for. I usually just look by time stamp..
Just got pi-hole set up, and I'd really like this feature added.
Try reading through one's query log when there are a few instances of syncthing running on the network. One ping per folder, per machine = ~50 log entries per minute.
A way to "exclude *syncthing.net from Query Log" would be very useful.
I'd actually like this as well, but as a way to filter out the "Client" column. My FireTV is extremely loud, as well as my WeMos. Looking for queries that have nothing to do with those clients is very annoying because of how obnoxious they are.
This would be a nice feature for armbian pihole users that enable log2ram on their devices. There's an ongoing problem with /var/log being filled by pihole.log and it eventually running out of space when it's mounted as a tmpfs mount by log2ram.
It's mentioned here: /var easily get full with log2ram - Common issues / peer to peer technical support - Armbian forum
Being able to eliminate accepted "noise" in pihole.log would go some way to solving this problem.
Filtering directly in the dnsmasq resolver is something we won't be able to do.
However, with Pi-hole v4.0 we will make the log file /var/log/pihole.log altogether optional. This log file is not needed any longer as FTLDNS ships with an inbuilt DNS/DHCP server and hence this file is not needed any longer.
It will probably stay enabled by default but you will be free to disable it at any point (however, it might be useful in the very early days of FTLDNS in case of any issues).
Making /var/log/pihole.log optional sounds interesting. I found the blog post that includes some more details about FTLDNS and it sounds like a nice development but I'm not sure this alone will enable zero pihole data storage on an SD card.
Things said in the blog post that gives me a doubt is where it first says:
"Due to the direct integration into the resolver (using “FTL hooks”), we can compute statistics without any need for a log file. Instead of a “must have”, the log file will become an “opt-in” feature!"
which is positive and then it goes on to say
"After the historic information has been imported from the database, we start the DNS/DHCP resolver"
Am I right to assume that the historic data would be loaded from the sqlite DB? If so then it would appear that making /var/log/pihole.log optional is a step in the right direction but personally I'd like the option to store the sqlite db on some other box that has HDD storage if that's at all possible.
I guess another option would be to revert back to syslog logging in dnsmasq/FTLDNS and offload the entire log to some other system i.e. something like ELK stack which comes with Kibana to enable a UI.
Perhaps going way into the future, the pihole web UI might eventually support reading data directly from a back end persistence store like elasticsearch.
This is already possible since the introduction of the database. You can put the database whereever you like to have it using FTL's config file (see FTL's README).
One of the reasons why Pi-hole is a very nice project also for Raspberry (and possibly also Linux) first-timers, is its simplicity. We really like to avoid additional gears that could break. This is also the main reason for why we decided to completely integrate SQLite into FTL instead of using some database server like MySQL. This is also the direction in which Pi-hole is currently developed:
- Currently:
dnsmasq(DNS) +FTL(statistics + database) +lighttpd(webserver) +PHP(API) - Next step:
FTL(DNS + statistics + database) +lighttpd(webserver) +PHP(API) - Future (already in active development):
FTL(DNS + statistics + database) + new API (web server + API)
As you can see, the number of gears that can break reduces and at the same time we ensure that we depend on less and less external software where we also cannot predict which versions will be provided by the various distributions out there.
1 Like
Thank you for your reply
That's a really good call thank you. Up until now I'd disabled the sqlite db completely but I'll take another look to see how well it will work if I drop a copy of the db onto my NAS box and use sshfs to mount that location, effectively making it look like it's still a local db.
I like the look of this roadmap and I agree it is a sensible approach. I can see the sqlite db isn't going away and although you didn't confirm when I asked if I was correct to assume that historic data would be loaded from the sqlite DB when FTLDNS comes along, I'm now certain this is the case. On this basis I'll have to get the sqlite db working acceptably well one way or another.
For the record I did manage to get the pihole sqlite db working over an sshfs mount to my NAS box (linux based). I'm including the script here just in case it is useful for anyone else who wants to do something similar. There were a couple of pitfalls along the way and I am no linux expert so there will be things to be frowned upon, one of them being use of a password in the sshfs command rather than the use of ssh keys, but it's working well enough for me.
I'm running pihole on a FriendlyArm NanoPi Neo2 with the Ubuntu 16.04.3 flavour of Armbian. Once I'd configured the /etc/pihole/pihole-FTL.conf DBFILE entry to point to where I've located the sqlite db file on my NAS, most of the trick to getting this to work is in the following script which I've saved as a file called nas_ssh_mount_neo2, dropped it into the /etc/network/if-up.d directory on the Neo2 and made it executable.
As this runs on boot it needs to wait for the eth0 network interface to come up and it takes control of stopping / starting pihole-FTL so that once the sshfs mount has been established, pihole-FTL can successfully initialize it's connection to the db. I've added in some delays and retries to help it be reliable and some logging to the existing pihole-FTL.log to make it semi-easy to debug:
#!/bin/bash
# depends on sshfs: sudo apt install sshfs
[ "$IFACE" == "eth0" ] || exit 0
FTL_LOG=/var/log/pihole-FTL.log
FTL_COMMAND=/etc/init.d/pihole-FTL
sleep 10
$FTL_COMMAND stop
echo "[$(date '+%Y-%m-%d %H:%M:%S.%3N')] nas_ssh_mount_neo2 stopped pihole-FTL on if-up" >> $FTL_LOG
counter=1
nas_ssh_home_fs=$(df -P -T /media/nas_ssh_home | tail -n +2 | awk '{print $2}')
echo "[$(date '+%Y-%m-%d %H:%M:%S.%3N')] admin@192.168.0.2:/volume1/home sshfs mount status is $nas_ssh_home_fs" >> $FTL_LOG
while [ "$nas_ssh_home_fs" != "fuse.sshfs" ] && [ $counter -le 10 ] #not yet mounted
do
echo "[$(date '+%Y-%m-%d %H:%M:%S.%3N')] Mount attempt #$counter to admin@192.168.0.2:/volume1/home" >> $FTL_LOG
((counter++))
echo MY_PASSWORD | sshfs -o nonempty -o follow_symlinks,allow_other,password_stdin admin@192.168.0.2:/volume1/home /media/nas_ssh_home
nas_ssh_home_fs=$(df -P -T /media/nas_ssh_home | tail -n +2 | awk '{print $2}')
echo "[$(date '+%Y-%m-%d %H:%M:%S.%3N')] admin@192.168.0.2:/volume1/home sshfs mount status is $nas_ssh_home_fs" >> $FTL_LOG
sleep 10
done
echo "[$(date '+%Y-%m-%d %H:%M:%S.%3N')] nas_ssh_mount_neo2 is starting pihole-FTL on if-up" >> $FTL_LOG
$FTL_COMMAND start #restart pihole to ensure it's correctly wired to the db