Let's make one thing very clear: I highly value all comments and ideas that DL6ER submits. This is NOT in any way an attempt to disrespect, offend or insult DL6ER. I want to thank him for his time, effort and great work he contributes to making pihole a better product.
As requested by DL6ER, I'm starting a discussion regarding this automated cleanup. This was discussed earlier in this topic.
As a result of the brief discussion, a change was entered by DL6ER:
the change implies all client entries, not seen for seven days are simply removed.
I don't have access to a lot of router/firewall devices, but seem to remember from my old days that a lot of DHCP servers have a default lease time of 14 days. I'm aware this is NOT the case for dnsmasq (=pihole-FTL), the default dnsmasq lease times are one hour for IPv4 and one day for IPv6 (/dnsmasq-2.82/src/config.h).
Personally, I'm not very happy with this (the change suggested by DL6ER) approach, it implies that query log entries, older than seven days, would no longer show a hostname, but the IP address.
The argument DL6ER uses to justify this:
quote
Imagine DHCP servers handing out the IP address 192.168.1.2 today to your phone, tomorrow your laptop, and then your whatever. The network table only stores the most recent address<->device information so abusing it for long-term information can be problematic.
/quote
Although this is true, I feel this is only a cosmetic issue for those users who fail to make static DHCP entries, based on the MAC address of their devices.
Some of you will argue that this is NOT done by the majority of the users. Personally, I think users will eventually be forced to do (learn) this, for the simple reason setting up group management, using clients that get a different IP address from the DHCP server, as soon as the lease expires, would be near impossible. The group assignments would simply apply to a different device, as soon as the address changes. The same applies to piholes Local DNS Records, fairly pointless to enter data in this hostsfile, if the addresses change all the time.
I suggested the following:
- The MAXDBDAYS setting (default if not in /etc/pihole-FTL.conf = 365). implies there is a database cleanup routine, that removes entries from the queries table, if the number of days specified, is exceeded. It's fairly easy, without severe performance impact on sqlite3, to get the first entry in the queries table:
firstQuery=$(sqlite3 ${piholeFTLdb} ".timeout = 20000" \
"SELECT timestamp FROM "queries" ORDER BY ROWID ASC LIMIT 1;")
As soon as the MAXDBDAYS routine has completed (again, I assume this routine is executed once a day), the time of the first entry in the queries table will have changed (if the system has been up and running long enough)
- Now we can get the id and lastQuery fields from the network table for all entries in the network table:
mapfile -t networkArray < <(sqlite3 ${piholeFTLdb} ".timeout = 20000" \
"SELECT id, lastQuery FROM network \
ORDER BY id;")
- Finally, loop over the entries in the network table, excluding the entries that don't have a valid lastQuery entry (usually the router MAC address) and remove the entries that have a lastQuery entry, older than the firstQuery entry in the queries table.:
for (( i=0; i<${#networkArray[@]}; i++ )); do
IFS='|' read -r id lastQuery <<< ${networkArray[i]}
if [ \( "${firstQuery}" -gt "${lastQuery}" \) -a \( "${lastQuery}" -gt "0" \) ]; then
sudo sqlite3 ${piholeFTLdb} ".timeout = 20000" \
"DELETE from network
WHERE id = \"${id}\";"
sudo sqlite3 ${piholeFTLdb} ".timeout = 20000" \
"DELETE from network_addresses
WHERE id = \"${id}\";"
fi
done
This is, of course the bash solution, unfortunately, I'm not smart enough to write the required FTL code.
This solution is, in my view, the perfect solution for those who have assigned a static DHCP address for their devices. I agree that it might be confusing (cosmetic) for users that don't do that, but also assume they are not using the full potential of pihole (group management), they will sooner or later be forced to use static DHCP assignments, if they ever want group management to be reliable.
The solution also respects the MAXDBDAYS setting, users my have configured (or the 365 days default), and will allow them to identify queries, using the hostname, for queries older than seven days.
DL6ER: You have repeatedly indicated you don't use Windows 10, thus not having experienced the problems (cosmetic) with ever changing temporary IPv6 addresses. Might I suggest you apply these settings, although complete useless (the conclusion of the topic) to one of your clients, just to provide you with a first hand experience of the results of this (default) Windows 10 behavior. Again, just as the majority of the users don't assign static DHCP addresses, I assume the same majority will never disable the Windows 10 privacy extensions.
