Deleting satellite mesh node IPs from database - did work, but not with FTL v5.14

[tristanc]

This post contains scripts, or links to scripts, that are untested by the Pi-hole team. We encourage users to be creative, but we are unable to support any potential problems caused by running these scripts

I have a Linksys Velop mesh system with 3 nodes. I use pihole as the DNS server attached by ethernet to the master node. The two satellite nodes fill my logs and dashboard with 600-odd requests every 10 mins as shown below for the last two time periods:

Expected Behaviour:

Up to FTL v5.13 I was able to run a version of the script contained in this post to delete these entries by their IP from the database queries table via a cron job running periodically (to get the more meaningful values / display shown to the left of the above screenshot) and restarting the pihole-FTL service.

Deleting a client from the database

This post contains scripts, or links to scripts, that are untested by the Pi-hole team. We encourage users to be creative, but we are unable to support any potential problems caused by running these scripts Have you ever wondered how to delete a client from Pi-hole? Just follow these simple steps: FOR EXPERTS sqlite3 /etc/pihole/pihole-FTL.db sqlite> DELETE from queries WHERE client = 'clientIP'; Verify that you deleted the client sqlite> SELECT * FROM queries WHERE client = 'clientIP'; …

Actual Behaviour:

Updating to FTL v5.14 breaks the existing script as the database structure / workings has changed.

I altered the script to be as below. The change is to delete the specific IP from the client_by_id table instead of the queries table.

#!/bin/bash
# rmclient
## Set Global Variables
db="/etc/pihole/pihole-FTL.db"
log="/var/log/pihole.log"

# Pre Run Checks
if [[ $(id -u) -ne 0 ]]; then
    echo "Please run as root"
    exit 1
elif [ "$#" -lt 1 ]; then
    exit 1
elif [ "$#" -gt 1 ]; then
    echo "ONLY ONE ARGUMENT PERMITTED"
    exit 1
elif [ ! -f "$db" ]; then
    echo "NO DATABASE"
    exit 1
fi

# Verify the IP Address
ip="$1"
if [[ "$ip" =~ ^(([1-9]?[0-9]|1[0-9][0-9]|2([0-4][0-9]|5[0-5]))\.){3}([1-9]?[0-9]|1[0-9][0-9]|2([0-4][0-9]|5[0-5]))$ ]]; then
    # Delete the client from the database
    echo "DELETING CLIENT $1 FROM DATABASE" | systemd-cat -t pi-hole-rm-client
    sqlite3 "$db" "DELETE from client_by_id WHERE ip = '$ip';" && sed -i '/"$ip"/d' "$log"
    if [ $? = 0 ]; then
        echo "CLIENT $1 SUCCESSFULLY DELETED" | systemd-cat -t pi-hole-rm-client
        #service pihole-FTL reload
    fi
else
    echo "INVALID IP ADDRESS"
    exit 1
fi

This doesn't work as expected; the IPs remain and are displayed. Pausing pihole and manually running the script seems to have an impact after a reload of pihole-FTL, but there is something unusual going on when run via cron.

Am I missing something / is there a better way to achieve the same result - ie remove specific client IPs from the database?

Debug Token:

https://tricorder.pi-hole.net/UNHcUe3n/

[Bucking_Horn]

The script you are using is neither devised nor maintained by Pi-hole.
You could consider to contact the user who wrote the script by PM.

But I also noticed your clients are issuing requests for .localdomains.

Note that .local is reserved for usage by the mDNS protocol and should not be used for plain DNS.

You should consider changing your local domain to something different.
This may or may not affect the volume of your clients.
If it does have an effect, then you may not need your script at all, but it's certainly worth doing anyway.

[tristanc]

Many thanks - if I understand correctly I may have inadvertently exposed myself to this issue by setting "Pi-hole domain name" to "local". I changed that as a matter of habit as I switched from an openwrt set-up to using the Linksys / pi-hole one.

And understood on contacting the original poster.

[Bucking_Horn]

@tristanc

if I understand correctly I may have inadvertently exposed myself to this issue by setting "Pi-hole domain name" to "local".

Yes, maybe - it's difficult to tell if those excessive requests would indeed be related to stray mDNS requests. In any case, it would be better to avoid using .local.

If that also fixes your issue, all the better.

[tristanc]

Sadly not... I cleared the database, switched .local to .lan and still have the two Linksys satellites as the top two clients.

Also seeing the "###.###.##.#.in-addr.arpa" addresses in top permitted domains.

This is exactly why I was wanting the two nodes removed from the databases... I'm hoping the Linksys devices are operating correctly - this behaviour has been noted in other forums. Perhaps this 'exclusion' capability for the reporting is a feature request?

[Bucking_Horn]

Since you redacted those top permitted domains:
Are they realted to regular PTR (for reverse IP address lookups) or DNS-SD related requests (starting with an underscore _)?

[tristanc]

Apologies, they are PTR (e.g., 54.2.168.192.in-addr.arpa).

Here's a reddit post on the same issue: https://www.reddit.com/r/pihole/comments/sy09s0/workaround_for_excessive_linksys_velop_ptr/

[SpookB]

Hi, just a quick 2 cents. I also have a velop system. 1 node being the router, 2 nodes being wireless repeaters. I had this problem with lots of ptr queries. I had 1 node Ethernet and 1 node wireless. I read for hours and hours about why there were so many queries coming from one of my "child" nodes. Then it hit me. Only the wireless node was causing the problems. 100 feet of cat6 cable solved the problem. As soon as I connected the node to ethernet, problem stopped.

[yubiuser]

@tristanc

I altered the script to be as below. The change is to delete the specific IP from the client_by_id table instead of the queries table.

While your script does delete the client from this table it does not delete the queries the client has made. Additionally, if the client makes the next query, it will be re-added, only with an increased id.

Have a look here how to really delete the queries with the new database schema:

Delete queries error

With the latest version the database schema has changed. The actual data is stored in the new table query_storage. The queries is a view only from which you can't delete queries anymore. Furthermore, the client information is not stored in query_storage as IP but rather an ID linked to the new client_by_id table. You need to do two things now: sqlite3 /etc/pihole/pihole-FTL.db "DELETE FROM query_storage WHERE client='192.168.10.51';" To delete the remaining queries for 192.168.10.51 before th…

[tristanc]

Many thanks - that's what I needed to return my script to functioning.

As an aside, I also updated the firmware on the Velop devices to a version released a couple of week ago. In the last couple of hours of testing the ~600 PTR requests every 10 mins is down to ~300 requests every 10 mins. That still fills the logs / dashboard with junk though...

[Bucking_Horn]

@tristanc

That still fills the logs / dashboard with junk though...

Note that by manually deleting those requests from the database, you are only concealing the actual issue, and you are distorting your statistics, i.e. they are no longer accurate for your network anymore.

The Linksys nodes will still send those PTRs all the time - so it is the Linksys Velop machines that produce that junk.

Did you consider to bring this misbehaviour to the attention of Linksys's support?

@tristanc

they are PTR (e.g., 54.2.168.192.in-addr.arpa)

Is Pi-hole able to answer them or are they NXDOMAIN?
In the latter case, have you tried whether creating a Local DNS record for 192.168.2.54 would satisfy the Velops and quiten them down?