Query log does not show hostnames from pihole DHCP

Help
9

That's expected, so not an issue at all (based on OS distribution, lighttpd configuration lives in different places, and the log checks them all).

Back to your issue:
It seems we have received a few similar reports in the past, but weren't able to trace down to a root cause.

Could you please Flush network table via Settings | System once more and rerun the first SELECT?

pihole-FTL sqlite3 /etc/pihole/pihole-FTL.db "SELECT * FROM client_by_id WHERE ip='192.168.2.245';"

along with the following statement:

pihole-FTL sqlite3 /etc/pihole/pihole-FTL.db "SELECT * FROM network_addresses WHERE ip='192.168.2.245';"
10

Yes off course, I would flush it 100 times, if we can get the issue fixed that way :wink:

pihole-FTL sqlite3 /etc/pihole/pihole-FTL.db "SELECT * FROM client_by_id WHERE ip='192.168.2.245';"
5|192.168.2.245|
33|192.168.2.245|LAPTOP-V1HR47PC.lan

and

pihole-FTL sqlite3 /etc/pihole/pihole-FTL.db "SELECT * FROM network_addresses WHERE ip='192.168.2.245';"
8|192.168.2.245|1686384300||
11

Just to make sure:
You ran those after flushing the network table, didn't you?

If so, please share the output of a reverse lookup via your Pi-hole for your IP address:

dig -x 192.168.2.245 @192.168.2.2

What is "SELECT * FROM network_addresses WHERE ip='192.168.2.245';" returning now?

12

Yes, I first pressed the button. Waited for the page, and after I saw the confirmation, I executed the commands.

dig -x 192.168.2.245 @192.168.2.2

; <<>> DiG 9.16.37-Raspbian <<>> -x 192.168.2.245 @192.168.2.2
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 22725
;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 1232
;; QUESTION SECTION:
;245.2.168.192.in-addr.arpa.    IN      PTR

;; ANSWER SECTION:
245.2.168.192.in-addr.arpa. 0   IN      PTR     Dennis-laptop.ip.

;; Query time: 60 msec
;; SERVER: 192.168.2.2#53(192.168.2.2)
;; WHEN: Sat Jun 10 10:31:53 CEST 2023
;; MSG SIZE  rcvd: 85

pihole-FTL sqlite3 /etc/pihole/pihole-FTL.db "SELECT * FROM client_by_id WHERE ip='192.168.2.245';"
5|192.168.2.245|
33|192.168.2.245|LAPTOP-V1HR47PC.lan

and

pihole-FTL sqlite3 /etc/pihole/pihole-FTL.db "SELECT * FROM network_addresses WHERE ip='192.168.2.245';"
8|192.168.2.245|1686385920||

Could it be that the web process is missing access to actually flush the DB?

It is running on a (back then) freshly install pi-zero-w, and then installed pihole directly.

13

No, network_addresses has been flushed - there is no name in there anymore.
But client_by_id still has the old name.

For your .245, please follow the steps as suggested by nprampage in Persistent incorrect client/IP/MAC mappings in network DB - #2 by nprampage.

Then rerun the SQL.

(I'll be offline soon, so won't be able to reply immediately.)

1 Like
14

I noticed the time was off by ~17 minutes, could that be part of the problem (as it seems the flush did not work)?

I have now corrected the time.

Updating the Gravity I have set is pretty heavy. This might have caused the time from changing. I now set a cronjob to update the time after updating the Gravity.

15

Thanks, enjoy your Saturday. :slight_smile:

This did not work, but I also was not able to follow the entire process.

  1. Added the 245 to the DNS list
  2. I was not able to see the delete button (found out later that it was hidden by scrolling) so I flushed the network table. The network table was empty and slowly filled up.
  3. the devices from the hosts file, show the given host name. The rest does not have a host name (including the 245)
  4. Manually removed the 245 device.
  5. Re-appeared in the list, still without a host name

I also tried it with a different device, though it did not work.

Removed the 245 from the DNS table, now. Left the other device there to verify if a couple of hours could do the trick.

16

What about the SQL results?

17

I've seen a similar situation where Pi-hole has the wrong hostname for an IP because it previously saw the hostname with that IP, even though the hostname has now changed. Flushing the network table and restarting the DNS resolver didn't fix it. I posted an example in a different thread.

In my case I think it was a PTR request against the IP of the wrongly named host which updated the name in Pi-hole and made it display correctly from that point onwards.

Your situation looks slightly different because it relates to your static entry for Dennis-laptop seemingly not being picked up. Nevertheless it would be interesting if you could do a PTR request against the IP and see if that changes anything. Perhaps some similar naming update mechanism is at play. You can use the dig or nslookup command to do this, from any client which is using Pi-hole for its DNS.

(Edit : assuming that your static entry is still present, if not then please try adding it back again)

dig -x 192.168.2.245

nslookup 192.168.2.245

Does that return Dennis-laptop or LAPTOP-V1HR47PC? And if it returns the correct name, is it now showing as the correct name in the Query Log too?

1 Like
18

What about the SQL results?

Sorry, I forgot to do it. Redid it, here are they.

 pihole-FTL sqlite3 /etc/pihole/pihole-FTL.db "SELECT * FROM client_by_id WHERE ip='192.168.2.245';"
5|192.168.2.245|
33|192.168.2.245|LAPTOP-V1HR47PC.lan

and

 pihole-FTL sqlite3 /etc/pihole/pihole-FTL.db "SELECT * FROM network_addresses WHERE ip='192.168.2.245';"
36|192.168.2.245|1686474600||

(Edit : assuming that your static entry is still present, if not then please try adding it back again)

These are the results wth the static DHCP entry, and the DNS entry present.

dig -x 192.168.2.245

; <<>> DiG 9.16.37-Raspbian <<>> -x 192.168.2.245
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NXDOMAIN, id: 9994
;; flags: qr aa rd ra ad; QUERY: 1, ANSWER: 0, AUTHORITY: 0, ADDITIONAL: 1

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 4096
;; QUESTION SECTION:
;245.2.168.192.in-addr.arpa.    IN      PTR

;; Query time: 10 msec
;; SERVER: fe80::1%2#53(fe80::1%2%2)
;; WHEN: Sun Jun 11 11:12:24 CEST 2023
;; MSG SIZE  rcvd: 55

and

 nslookup 192.168.2.245
** server can't find 245.2.168.192.in-addr.arpa: NXDOMAIN

Also, all static DHCP entries are present in /etc/dnsmasq.d/04-pihole-static-dhcp.conf

By the way, The example device has IPV6 disabled, though it also happens for devices that have IPV6 enabled.

19

That request went to fe80::1 (your router's link-local IPv6, in all likelihood), indicating that your router is advertising its own IPv6 address as local DNS resolver.
That is a separate issue altogether, and a more severe one, as it would allow your IPv6-capable clients to by-pass Pi-hole completely.

While that should be addressed, it wouldn't affect your current difficulties with resolution of DHCP client hostnames.

Your SQL output demonstrates that flushing or deleting an entry from the network table removes entries from network_addresses: When the entry reappears, it has a different record id (was 8, is now 36),
Entries in client_by_id, however, would remain untouched.

I'll ask development to take a deeper look.

Very unlikely, as Pi-hole itself holds the definitions for DHCP client name directly.

That should not be necessary - usually, your OS reliably keeps track of that (click for more)

One notable exception would be Raspberry Pi OS coming online after a prolongued powerdown.

RPis lack a battery-backuped RTC, so they read in a last known time from a file during boot, and then resume regularly syncing time with a time server once boot is complete.

If that time would be off by too far, the time-sync service would refuse to update the clock, so you'd have to update it manually.

17 minutes would not be enough to trigger that refusal, though.
That may suggest that your RPi may not have been able to sync with a time-server for a rather long time, as the RPi's system clock could be expected to lose or gain only a few seconds per day.

Is your RPi Zero aware of and allowed to sync with a time server?

21

Flushing FTL's internal memory isn't possible right now, so what we see here is very likely just that FTL knows the old name from the database (after starting) and caches this name until it gets an update. However, since,

it never gets an update and preserves the old name in memory. You flush the database and on the next query dumping (every minute), FTL re-adds the old name into the database.

i.e. the real issue we need to address is: Why can FTL not determine the new name (to replace the old)?

The name resolution workflow inside Pi-hole is as follows (remaining steps are skipped once we get a reply somewhere):

  1. We send a PTR to FTL
  2. We send a PTR to the system-configured resolver (e.g. docker container names)
  3. If NAMES_FROM_NETDB is true, we try
    3.1. to get an exact match IP <-> hostname
    SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;
    3.2. to get a host name associated with the same device (but another IP address)
    SELECT name FROM network_addresses 
            WHERE name IS NOT NULL AND "
                  network_id = (SELECT network_id FROM network_addresses
                                                  WHERE ip = ?)
            ORDER BY lastSeen DESC LIMIT 1

And only if this all fails, we have no host name. I don't see how client_by_id could be involved here and just verified this flow in the v5.23 code. I also furthermore confirmed that we never read from client_by_id - we only ever store stuff in there.

Unfortunately, the debug tokens already expired by now, they could have given some further clues here looking at if pihole.log would have contained log lines of the names - but most likely the debug log wouldn't have had them anyway as it is always only a rather short excerpt.

What er can do to go on from here would be:

Please add

DEBUG_RESOLVER=true

to the file /etc/pihole/pihole-FTL.conf (create if it does not exist) and run

pihole restartdns

This should include rather extensive information about every (internal) name resolution process in var/log/pihole/FTL.log which should be pretty self-explanatory and should show us where it is going wrong. Then, Pi-hole also has a setting REFRESH_HOSTNAMES and a few more friends which could influence this behavior, but I suspect everything is default settings where everything should "just work".

22

That specific dig reply should be disregarded - it was not answered by Pi-hole, but by fe80::1 (most likely the router, judging by that interface identifier).

A previous direct lookup through Pi-hole returned successfully:

This is also the expected result, as Pi-hole is acting as their DHCP server, and the MAC for 192.168.2.245 listed the hostname as returned by dig above, in both debug logs.

A sequence of steps as suggested by nprampage (involving creating and deleting Local DNS records) was able to resolve the issue for some users with similar persistent observations of old hostnames.

23

Is the mixture of .ip and .lan private TLDs relevant here? The fact that the latter name appears as default makes me think that the .ip suffix needs to be .tld to align with wherever this is being set, in order to be usable.

24

The right order for flushing all the database memory should be

  1. stop FTL,
  2. flush the network database, and
  3. start FTL.

The network database flushing in the current v6.0 development code already takes care of this internally as FTL is now responsible for flushing the tables.

Thanks, I've overseen this very detail. In this case, my suggestion of enabling the DEBUG_RESOLVER mode is even more interesting as we already know that FTL does know the new name and we merely have to find out why the old name is not replaced as it should have been.

25

Heey, sorry for the late reply.

Sadly I am aware of this, though need to convince my wife about the investment in new hardware here :slight_smile:

Good catch. I changed the DHCP domain from the default lan to ip. So it is really strange that lan is used.

How can I do this manually? As when I stopped FTL the web GUI was gone, and the SQL statement complained that I tried modifying a locked table.

Results from var/log/pihole/FTL.log:
log.txt (70.8 KB)

26

Interestingly enough, your Pi-hole does not seem to be able to reply

[2023-06-15 16:46:18.185 19868/T19886] Trying to resolve 192.168.2.245
[2023-06-15 16:46:18.186 19868/T19886] Setting nameservers to:
[2023-06-15 16:46:18.186 19868/T19886]  EXT 0: fe80::1:53 (IPv6)
[2023-06-15 16:46:18.189 19868/T19886]  ---> "" (not found internally: Name or service not known
[2023-06-15 16:46:18.190 19868/T19886] Setting nameservers back to default:
[2023-06-15 16:46:18.190 19868/T19886]  EXT 0: fe80::1:53 (IPv6)
[2023-06-15 16:46:18.194 19868/T19886]  ---> "" (not found externally: Name or service not known)
[2023-06-15 16:46:18.199 19868/T19886]  ---> not found
[2023-06-15 16:46:18.200 19868/T19886] Client 192.168.2.245 -> "" is new

even when

works ...

The obvious difference here is the address of the nameserver being asked. Just for completeness, could you also try

dig -x 192.168.2.245 @fe80::1

on your Pi-hole?

27

Today something strange happened.

I was going through the query log, to see if something was blocked that caused the issue that I was seeing with spotify.
I saw IPs as usual.

Though as I could not spot a related blocked host, I disabled blocking for 5 minutes using the WEB UI with periods of blocking in between from up-to 5 minutes as well, I disabled blocking 3 times.

Then suddenly I realized that there were no longer IPs listed, but the DHCP device names.
At first not all (Funny enough, the test IP 245 stayed) though then also that one was showing the hostname.

All are also using the ip domain prefix.

I have disabled the blocking earlier also, though back then it did not change a thing.

The only thing I also changed 36 hours ago (though did not have effect until now, but might be related) was to enable IPv6 in the DHCP settings. The strange thing is that the DHCP lease is set to 24 hours, and most devices renewed their DHCP lease this morning the second time already after I changed this in the settings.

I hope this helps you to debug the issue further. If there is anything you want me to try on my device, please tell me. Happy to run some more tests if it helps you debug this.

Here are the results of both dig commands.

 dig -x 192.168.2.245 @192.168.2.2

; <<>> DiG 9.16.37-Raspbian <<>> -x 192.168.2.245 @192.168.2.2
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 60334
;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 1232
;; QUESTION SECTION:
;245.2.168.192.in-addr.arpa.    IN      PTR

;; ANSWER SECTION:
245.2.168.192.in-addr.arpa. 0   IN      PTR     dennis-laptop.ip.

;; Query time: 39 msec
;; SERVER: 192.168.2.2#53(192.168.2.2)
;; WHEN: Sat Jun 17 09:43:16 CEST 2023
;; MSG SIZE  rcvd: 85

and

dig -x 192.168.2.245 @fe80::1

; <<>> DiG 9.16.37-Raspbian <<>> -x 192.168.2.245 @fe80::1
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NXDOMAIN, id: 9977
;; flags: qr aa rd ra ad; QUERY: 1, ANSWER: 0, AUTHORITY: 0, ADDITIONAL: 1

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 4096
;; QUESTION SECTION:
;245.2.168.192.in-addr.arpa.    IN      PTR

;; Query time: 9 msec
;; SERVER: fe80::1%2#53(fe80::1)
;; WHEN: Sat Jun 17 09:43:37 CEST 2023
;; MSG SIZE  rcvd: 55

Edit
I looked through the long term data, and the query log seems to be containing the correct hostnames since the new lease on yesterday already (first new lease after enabling IPv6). That does not explain why I did see IPs this morning...

28

Thanks for you report. I reviewed the related code yesterday again but forgot to come back here and reply to you. What I found is that there is an issue with FTL not being able to resolve hostnames when no IPv4 name servers were defined. I think this matches your observation that changing IPv6-related settings improved the situation. It furthermore supports what we have seen above:

which shows that Pi-hole did not actually try to use itself (fe80::1 is the router). And, as expected, your second dig you've just shown above confirms that the router does not know a hostname for this address.

This is an interesting edge case. The machine running your was not configured with IPv6 upstream connectivity, however, that does not stop it from using link-local (fe80:...) IPv6 communication. The sole and only configured DNS server was an IPv6 server at fe80::1 (your router on the link). Due to an incorrect assumption (namely that there will always be IPv4 DNS servers configured), FTL failed to self-assign itself into the name resolution chain and, hence, you did not get the host name. Whatever your change did, apparently, FTL is now able to self-assign and was able to refresh the hostnames.

I added this now to the currently under development v6 code so we have this covered in the future:

1 Like
31

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.