Force update of IP / hostname / clientname relationship

thanks again for looking into this, much appreciated.

does fix/read_should_not_bind also contain the code from new/tre-regex or do I have to remove the regex (.*;querytype=!A) before switching?

any idea why query log and dashboard have a very long update time (switch from displaying IP to hostname) when using restartdns reload-lists as opposed to restartdns (long-term data / query log doesn't have the problem)?

Just leave it, it will be ignored. I also switched from new/tre-regex to an other branch without disabling or removing it.

@jpgpi250 @yubiuser is right, just leave it and ignore the regex warning about invalid syntax (if any).

No, this seems separate, the corresponding debug mode (I think it is DEBUG_RESOLVER=true) should show when FTL tries to resolve IP addresses and what the outcome is.

  • fairly easy to get the error when running new/tre-regex, a few restarts and bingo
  • impossible to get the error when running fix/read_should_not_bind, force rotated the log and ran (still running):
while : ;do
	sleep 60
	grep "SQLite3 message" /var/log/pihole/pihole-FTL.log
	pihole restartdns
done

must of restarted over 30 times by now, no errors in the log (yes, my log isn't in the default location), so the fix works. thank you!!!

Thank your for testing it so quickly. It is scheduled to still make it into v5.2

Appreciate your work, but again, just like the new/mac-clients, the new/super-clients branch doesn't solve my, and I assume other users, problem.

You're always assuming that arp -a and / or ip neigh (the equivalent code in pihole-FTL) is able to figure out the MAC address of the clients. My pi is isolated on a separated physical network, and doesn't see anything but the router/firewall (4 port) interface.

I assume neither branches will solve this problem, unless you claim this to be otherwise.

One of the reasons my dashboard, query log and long term data views are a total mess is, as explained earlier here (but that topic unfortunately turned into a FTL problem and fix) is, again I assume, the missing data in the pihole-FTL database

FTL sees, and ads the clients to the network table, but fails to update the name field, example screenshot below

FTL sometimes inserts the hostname into the database, but often fails to enter this info, even if the data is available (separate configuration file, added to dnsmasq). If that field contains a value, the dashboard and the query log display the correct info (hostname) after a while, if the field is (remains) blank, the query log displays the ipv6 address, all different because of the windows 10 privacy extensions ( all the IPs in the screenshot are from a single client, db up and running (new v5.1.2) for almost 2 days)

currently, I'm investigating a safe way to inject the names my self, that is one of the reasons I asked for a version indication in pihole-FTL.db,

Maybe because it wasn't designed to?

Have you confirmed (using dig -x) that the information is really available? Maybe there is some other problem.

Looking at your description and your screenshot, I don't think this affects a lot of users. They typically don't separate (V)LANs at home. This is not to say this is not an important thing, it is just maybe less common than you'd expect.

I think this is a good assumption. How should it work differently? As long as Pi-hole does not know the foreign MAC address, it cannot know which devices belong together.

Yes, I guess that's the issue. If Pi-hole has thousands of users, maybe tens of thousands (we don't know because of the anonymity of the downloads from Github and no Pi-hole telemetry), a lot of unusual network configurations will show up. If the information is not available, how should Pi-hole (automatically) detect which address belong to which device.

As always, just oppnons: I disagree with pseudoclients. It somehow seems to suggest they are not really there. While one might argue that this is the case, they actually physically exist as devices and are no pseudo. In my understanding the term pseudo would cause more confusion. super may not be optimal, either, but right now at least it seems more descriptive for the "it is more than one ordinary client".


Otherwise this is really a cool thing. I just tried it in my network and it seems to work just as expected. I even configured all my devices now and have not seen anything odd.

the database table, current content

the hosts file

2a02:1810:4d02:6903:49b3:cc94:cd8:dd0e y50ipv6 y50ipv6.localdomain # Mon 10 Aug 08:00:01 CEST 2020
2a02:1810:4d02:6903:d849:7563:3852:ae93 y50ipv6 y50ipv6.localdomain # Mon 10 Aug 11:40:02 CEST 2020
2a02:1810:4d02:6903:280c:5ca8:438:f73 y50ipv6 y50ipv6.localdomain # Mon 10 Aug 14:40:01 CEST 2020
2a02:1810:4d02:6903:a8ed:9058:a230:39fe y50ipv6 y50ipv6.localdomain # Mon 10 Aug 15:30:01 CEST 2020
2a02:1810:4d02:6903:40e9:69:98c0:b990 y50ipv6 y50ipv6.localdomain # Mon 10 Aug 16:21:02 CEST 2020
2a02:1810:4d02:6903:194e:5c8d:14d7:1cc9 y50ipv6 y50ipv6.localdomain # Mon 10 Aug 17:40:02 CEST 2020
2a02:1810:4d02:6903:1d92:155:e2e2:94b2 y50ipv6 y50ipv6.localdomain # Mon 10 Aug 21:50:02 CEST 2020
2a02:1810:4d02:6903:b4ac:bb3f:83d6:6894 y50ipv6 y50ipv6.localdomain # Mon 10 Aug 22:40:01 CEST 2020
2a02:1810:4d02:6903:49b8:e421:9a56:b2c8 y50ipv6 y50ipv6.localdomain # Tue 11 Aug 08:00:01 CEST 2020
2a02:1810:4d02:6903:9910:ed01:952c:e35c y50ipv6 y50ipv6.localdomain # Tue 11 Aug 14:40:01 CEST 2020
2a02:1810:4d02:6903:18f7:fd2f:c2cc:454c y50ipv6 y50ipv6.localdomain # Tue 11 Aug 22:12:01 CEST 2020
2a02:1810:4d02:6903:506c:1507:19d1:a873 y50ipv6 y50ipv6.localdomain # Wed 12 Aug 08:00:01 CEST 2020
2a02:1810:4d02:6903:d9e3:deb:2516:3f09 y50ipv6 y50ipv6.localdomain # Wed 12 Aug 10:20:01 CEST 2020

the dig -x result (last IPv6 entry, = current windows 10 temporary address)

dig -x 2a02:1810:4d02:6903:d9e3:deb:2516:3f09

; <<>> DiG 9.11.5-P4-5.1+deb10u1-Raspbian <<>> -x 2a02:1810:4d02:6903:d9e3:deb:2516:3f09
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 38820
;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 4096
;; QUESTION SECTION:
;9.0.f.3.6.1.5.2.b.e.d.0.3.e.9.d.3.0.9.6.2.0.d.4.0.1.8.1.2.0.a.2.ip6.arpa. IN PTR

;; ANSWER SECTION:
9.0.f.3.6.1.5.2.b.e.d.0.3.e.9.d.3.0.9.6.2.0.d.4.0.1.8.1.2.0.a.2.ip6.arpa. 2 IN PTR y50ipv6.localdomain.

;; Query time: 1 msec
;; SERVER: 127.0.0.1#53(127.0.0.1)
;; WHEN: Wed Aug 12 10:34:29 CEST 2020
;; MSG SIZE  rcvd: 134

yes I'm sure, pihole-FTL doesn't always pickup the name of the host.
My original question, in the topic that drifted off topic (FTL error message) was: How do I force FTL to update the IP / hostname relationship.

There should be no need to, it automatically does once an hour. You can see PTR requests coming in ont he Query Log. Maybe try DEBUG_RESOLVER=true in pihole-FTL.conf + pihole restartdns ?

Extensive information about hostname resolution like which DNS servers are used in the first and second hostname resolving tries (only affecting internally generated PTR queries).

Configuration - Pi-hole documentation

Then watch pihole-FTL.log for the messages related to trying to resolve these IP addresses you added to the HOSTS file.

edit

One other thing that came to my mind: Have you reloaded FTL after adding these lines to your HOSTS file? Not sure if this is needed. They seem to have been added only over two days.

@Coro brought up some good points, the advanced resolver debug logging should show why no host names are picked up. I also updated the original discussion. What you are asking for should now be possible by adding the corresponding superclient_id to all of your ip-[...] MAC address entries.

PLUS: It is also possible to transport the MAC address over your router into FTL as FTL v5.2 is processing EDNS(0) information to get the IP and MAC addresses of the original requestors even if hidden behind a NAT (and enabled on the router).

See

Here is what is happening.

A windows 10 computer (with privacy extensions, thus ever switching temporary ipv6 address, on reboot) is running

pihole-FTL says every minute:

[2020-08-13 12:13:00.283 28371/T28377] 0 / 14 client host names resolved
[2020-08-13 12:13:00.283 28371/T28377] 0 / 2 upstream server host names resolved

14 clients !!!

now the windows 10 computer is restarted, thus new temporary IPv6 address, forcing the computer to use IPv6 (browse to https://dnssec.vs.uni-due.de/)

pihole-FTL says:

[2020-08-13 12:19:00.367 28371/T28377] 0 / 14 client host names resolved
[2020-08-13 12:19:00.367 28371/T28377] 0 / 2 upstream server host names resolved
[2020-08-13 12:20:00.807 28371/T28377] Trying to resolve 2a02:1810:4d02:6903:6da2:6487:fd09:9eb7
[2020-08-13 12:20:00.808 28371/T28377] Setting nameservers to:
[2020-08-13 12:20:00.809 28371/T28377]  0: 127.0.0.1:53
[2020-08-13 12:20:00.810 28371/T28377]  1: 0.0.0.0:0
[2020-08-13 12:20:00.811 28371/T28377]  2: 0.0.0.0:0
[2020-08-13 12:20:00.898 28371/T28377] Setting nameservers back to default:
[2020-08-13 12:20:00.899 28371/T28377]  0: 127.0.0.1:53
[2020-08-13 12:20:00.899 28371/T28377]  1: 0.0.0.0:0
[2020-08-13 12:20:00.900 28371/T28377]  2: 0.0.0.0:0
[2020-08-13 12:20:00.902 28371/T28377]  ---> "" (N/A)
[2020-08-13 12:20:00.904 28371/T28377] 1 / 15 client host names resolved
[2020-08-13 12:20:00.904 28371/T28377] 0 / 2 upstream server host names resolved

The initial name resolution fails: ---> "" (N/A)
pihole-FTL says 15 client host names resolved (count has increased)

pihole-FTL keeps saying:

[2020-08-13 12:22:00.034 28371/T28377] 0 / 15 client host names resolved
[2020-08-13 12:22:00.034 28371/T28377] 0 / 2 upstream server host names resolved

15 clients !!!

I update the hosts file every 15 minutes (cron), adding new IPv6 and hostname entries to a dnsmasq file (hostsdir= no restart required !!! see dnsmasq man)

however, after the message from pihole-FTL (detected hostsdir change):

Aug 13 12:28:01 dnsmasq[28371]: inotify, new or changed file /etc/pfsense/neighbour
Aug 13 12:28:01 dnsmasq[28371]: read /etc/pfsense/neighbour - 21 addresses

and a test (in the script), name resolution works immediately (tested with dig -x)

Aug 13 12:28:02 dnsmasq[28371]: query[PTR] 7.b.e.9.9.0.d.f.7.8.4.6.2.a.d.6.3.0.9.6.2.0.d.4.0.1.8.1.2.0.a.2.ip6.arpa from 127.0.0.1
Aug 13 12:28:02 dnsmasq[28371]: /etc/pfsense 2a02:1810:4d02:6903:6da2:6487:fd09:9eb7 is y50ipv6.localdomain

pihole-FTL simply keeps saying:

[2020-08-13 12:30:00.313 28371/T28377] 0 / 15 client host names resolved
[2020-08-13 12:30:00.314 28371/T28377] 0 / 2 upstream server host names resolved

What I see: pihole-FTL never attempts a retry on the failed name resolution, the client count is increased, the hostname is never added to the network table (pihole-FTL.db)

So the problem is that when FTL sees the client and tries to resolve the host names, it is still unknown (12:22). Only later, your script picks up the host name, FTL is aware of it (12:28).

It shouldn't. Imagine all the devices which do not register a proper hostname. I wouldn't want my Pi-hole statistics to be spammed by PTR requests for all these devices every minute. How could it know that resolving the client would now succeed (without doctoring the hosts file itself)?

What I don't understand here is that my FTL seems to re-query the hostnames for all clients again at every full hour - presumably to check for updated or new host names (like in your case).

@jpgpi250 Do you have such logging at 13:00 ?
@DL6ER For confirmation of rechecking all host names once every full hour.


edit

Found confirmation in a user report here:

Putting Pi-hole's update frequencies aside:
There could be an alternative to pushing IPv6 PE addresses to Pi-hole.

This would require configuring IPv6 prefix policies for your Windows client machines.

If you are well aware of IPv6 prefix policies and have already tried them unsuccessfully, don't click here

A preliminary warning:
I am not entirely sure if that can be made to work, as I couldn't get it running with Windows 7, and have never bothered retrying it since.

You can view current prefix policies with the following command:

netsh interface ipv6 show prefixpolicies

This will produce an output similar to the following (on W8/10):

Precedence  Label  Prefix
----------  -----  --------------------------------
        50      0  ::1/128
        40      1  ::/0
        35      4  ::ffff:0:0/96
        30      2  2002::/16
         5      5  2001::/32
         3     13  fc00::/7
         1     11  fec0::/10
         1     12  3ffe::/16
         1      3  ::/96

This table is used whenever your system tries to determine which of your devices configured source addresses and which of a target server's available destination addresses should be used for communication.
Without going into too much detail, note that a precedence value impacts source address selection, while a label value influences selection of a destination address. In case you are seriously interested, RFC 6724 has all the petty details.

You don't just bump the generic entry for ULA prefixes, because that would apply to remote ULA networks you try to connect to as well. Preferring them over public IPv6 may not be what you want.

You should instead expand the preference table by a supplemental entry for your local ULA prefix (assumed as fd00:1234:5678):

netsh interface ipv6 add prefixpolicy fd00:1234:5678::/48 45 14

If you distribute and/or advertise Pi-hole's ULA address as DNS server, your client's should now prefer to send DNS requests using their ULA addresses.
A big caveat: This did not work for me with Windows 7 because that used PE for ULA prefixes as well.

I've never tested this with Windows10.
Im a bit apprehensive of the possibility that Windows 10 still may enable PE over all IPv6 prefixes, instead of limiting it to publics only, or making it configurable.
(Note that you cannot readily tell this from looking at the IPv6 address alone, you'd need to know if it was temporary or just randomised.)

If that's the case, changing ULA preference won't do anything for you, as the interface identifier would still be temporary.

Instead, you could try to bump IPv4 preference for same level results (i.e. make your client chose IPv over IPv6 even when results are otherwise equally suited otherwise).

netsh interface ipv6 set prefixpolicy ::/96 99 4

If this works, it should make your clients send DNS requests to Pi-hole's IPv4 address instead of IPv6.

In the absence of any ULA prefix, bumping IPv4 would be your only option.
So try this one first if you don't use ULA, or if you've confirmed that Win10 would apply PE to ULA as well.

I haven't done any research on how to fill this into a group policy so you wouldn't have to configure clients individually, and I am also unware of routers supporting RFC 7078 to do so via DHCPv6.


Before I started trying to solve this IPv6 problem, the reverse lookup sometimes succeeded, however, since pihole-FTL doesn't know anything about this address, the query was forwarded to the configured resolver (= unbound in my case). Somehow, unbound succeeded sometimes in resolving the query, the result being something like ptr-2b6mqshzuu912h87psn.18120a2.ip6.access.telenet.be.
sometimes: probably dependent on the load on my providers DNS servers.

First of all, again I end up with different client names, secondly the names are even more meaningless than the IPv6 address.

Being tired of these hostnames, I decided to configure unbound to simply reply NXdomain for all requests to in-addr.arpa. and ip6.arpa. If I really need to resolve PTR requests, I simply use a different resolver to get an answer.
That change (to unbound) has a positive effect, no more weird hostnames...

I don't understand why pihole-FTL doesn't succeed in updating the hostname, even if it is an hour later, while it's clear there is no name entry in the network table of the database, and the hostsfile contains the required information (confirmed by dig -x)

It doesn't need to make PTR requests for all devices, only for the devices that don't have a name yet, besides, according to your findings, it does that anyway every hour, but for some reason doesn't update the network table, even if a valid result is found.

If you look at the title of the topic, this happens (should happen) every hour, the question is how to force pihole-FTL to do this. Keep in mind that I do not want to restart / reload / or whatever pihole-FTL. The preferred way to do this would be to use a signal

I think you are on a good way to the solution here, I don't know what is exactly happening right now. The procedure should be what you described, in the following temporal order:

  1. Pi-hole sees the client for the first time
  2. It tries to obtain a host name, which fails (expected)
  3. As there is no known host name, nothing will be stored in the database (expected)
  4. At the next full hour, the name resolution should retry and it should succeed (?)
  5. Shortly after, the network table should get updated. However, at the very latest another hour later (?)

So how does a re-resolving try look like at a full hour? I expect FTL to manage to get the host name, so it will also show up in the dash board (?). If it does and it is not updated in the database, my speculation is that this is caused by all your clients being only mock-clients, but I don't really think they are handled much differently.

There is a limitation to when client host names are updated in the database: Only when they made some queries recently. This is to avoid database I/O for clients which may have left the network a long time ago. IMO this makes sense. There is also little point in updating host names in this case.

This may explain why you get host names for some (recently active) but not the other (recently inactive) clients. I have no objections at looking how to improve this algorithm.

Question 1: Does the re-resolving always succeed at the next full hour?
Question 2: Are the corresponding clients active around the full hour?

I know question 2 may be a bit tricky to answer as it may depend on the exact timing. We can set up a special branch which includes dome custom debug logging that'll tell us exactly what is going on and why FTL chooses one or the other strategy to either update or skip some clients. Which branch would you like me to put these messages into for you? You can continue other testing at the same time if you like, this will not disturb anything else.

Pretty sure that the case, checked todays long query log, search PTR, all requests (on the hour) from localhost are OK(cached).
scenario:

startup computer at 23:26:00

I've checked the long term data, when my computer is started, I see two (why two?) forwarded PTR requests (time of startup), expected, no hosts file entry yet.

neighbor detection (my script, get neighbour data from pfsense, the FreeBSD command ndp -na, and process the result, executed every 15 minutes)
hosts entry added (using dnsmasq hostdir=, so no reload, restart, …

Aug 13 23:43:51 dnsmasq[8913]: inotify, new or changed file /etc/pfsense/neighbour
Aug 13 23:43:51 dnsmasq[8913]: read /etc/pfsense/neighbour - 23 addresses

on the hour, I see, a lot of PTR requests, including one for the new ipv6 address (OK cached), expected, hosts file entry available and processed by pihole-FTL

checking the database after 5 minutes, this to ensure database on disk has been updated (using DBINTERVAL=2 in /etc/pihole/pihole-FTL.conf), no hostname added, unexpected

after 30 minutes, forced an IPv6 lookup (browse to https://dnssec.vs.uni-due.de/), checking the database, hostname added to the database, why now?
do we need an IPv6 DNS request after the hostname is available to update the network table?

Sometimes (longer sessions) they are, sometimes they are not (sessions to check mail take only a few minutes)

I'm currently on master (v5.2), trying to get my head around this problem, before focusing on other things.

Thanks for your hard work, much appreciated.

A lot of edits, pictures not visible for some reason...

Yes. If the IPv6 client is sitting around still and not do anything, its data (incl. the host name) is not updated:

I think it's pretty clear now, I will look into the code in a bit and then come back to you here.

Done some more tests this morning, keeping two windows 10 computers on for a longer period, without a changing IPv6, but not a lot of activity.

it really looks like you need a recent query, originating from the temporary IPv6 address, in order to update the name in the network table.

at 09h45, after 1,5 hours of no activity, I looked at the network table.
One of the computers now had a name, the lastQuery field had a recent date (converted using this site.
The other computer still had an empty name field (empty, NOT null), the lastQuery field had a entry from over two hours ago

unfortunately, no screenshots, using the site www.epochconverter.com implied a IPv6 DNS query, this made the lastQuery field change, the name now also appeared.

My assessment: updating the name field should not be subject to performing a query. As soon as there is a hwaddr in the network table, without a name entry (not empty and not null), pihole-FTL should regularly attempt to resolve the address.

Since some users don't like the PTR requests, the frequency should probably be configurable in pihole-FTL.conf.

edit
OR as suggested earlier, triggered by a signal to pihole-FTL
/edit

The problem is caused by windows 10. It looks like, when the computer starts up, a number of DNS queries are performed, using the temporary IPv6 address, which causes pihole-FTL to add the IP to the table, however, once started, the majority of the request are performed, using the IPv4 address of the computer. If your lucky enough to consult a site that is resolved, using the IPv6 address of the computer, in the window that pihole-FTL checks, you get a name entry. If not …

Yesterday, I tried (and succeeded) in adding the name field scripted (next run, 15 minutes) when a hwaddr without a name existed in the database. Unfortunately, this doesn't affect the dashboard and the query log, I assume the web interface gets the info from pihole-FTL, and pihole-FTL never looks for this information in the database, when it's running (only at startup)

I was composing this entry, but you already replied, hope it still adds some clarification.

Yes

This is being done. However, for all clients, not only those without a name. Mind that existing names may change, e.g. on a non-deterministic/sequential DHCP server where 192.168.1.2 is today your desktop machine and tomorrow your phone. Depending on who was switched on first that day.

Hmm, yes, we can do this, there is no reason to hard-code the value.

I don't like this idea. It will only resolve the issue for those who are aware of this signal. Better is removing the current logic of

only update a database entry when there were queries recently

to

only update a database entry when there were queries recently or when the host name changed

This should not increase the number of I/O operations all that much as no write will happen when the read showed that there is nothing to update here.

Okay, so that's why I haven't seen it myself.

Actually, FTL should try to get the host name from the database when an upstream lookup fails. But then only at the next full hour. And only if NAMES_FROM_NETDB is not set to false (it defaults to true) in /etc/pihole/pihole-FTL.conf

I see there is a glitch in the SQL instructions coming from when I redesigned the network tables to support the MAC address, however, that only exists in development, not in master.

I will prepare a test branch with the modified logic (as above) for you, soon. No promises to when I will get around to doing this, but latest sometime next week. I will also add more debub logging here. I will also fix the development name resolution issue.