Ah, right. No issue then. Changes already pushed. The latest change will likely analyze stuff like
ff02::2 dev eth0 lladdr 33:33:00:00:00:02 NOARP
ff02::16 dev eth0 lladdr 33:33:00:00:00:16 NOARP
and attribute this correctly to device lo. I assume this was missing before as well.
Seems to work.
The first neigh entry for `10.0.1.190 was incomplete but no mock-device was added.
Mo 23. Mär 14:43:45 CET 2020
10.0.1.190 dev eth0 INCOMPLETE
10.0.1.1 dev eth0 lladdr f0:9f:c2:1e:8f:e9 DELAY
0.0.0.0 dev lo lladdr 00:00:00:00:00:00 NOARP
10.0.1.136 dev eth0 lladdr 3c:97:0e:13:36:c0 REACHABLE
ff02::1:ff42:dae4 dev eth0 lladdr 33:33:ff:42:da:e4 NOARP
ff02::2 dev eth0 lladdr 33:33:00:00:00:02 NOARP
ff02::16 dev eth0 lladdr 33:33:00:00:00:16 NOARP
::1 dev lo lladdr 00:00:00:00:00:00 NOARP
Mo 23. Mär 14:44:45 CET 2020
10.0.1.190 dev eth0 lladdr d4:38:9c:01:ac:6c STALE
10.0.1.1 dev eth0 lladdr f0:9f:c2:1e:8f:e9 STALE
0.0.0.0 dev lo lladdr 00:00:00:00:00:00 NOARP
10.0.1.136 dev eth0 lladdr 3c:97:0e:13:36:c0 REACHABLE
ff02::1:ff42:dae4 dev eth0 lladdr 33:33:ff:42:da:e4 NOARP
ff02::2 dev eth0 lladdr 33:33:00:00:00:02 NOARP
ff02::16 dev eth0 lladdr 33:33:00:00:00:16 NOARP
::1 dev lo lladdr 00:00:00:00:00:00 NOARP
Mo 23. Mär 14:45:45 CET 2020
10.0.1.190 dev eth0 lladdr d4:38:9c:01:ac:6c STALE
10.0.1.1 dev eth0 lladdr f0:9f:c2:1e:8f:e9 STALE
0.0.0.0 dev lo lladdr 00:00:00:00:00:00 NOARP
10.0.1.136 dev eth0 lladdr 3c:97:0e:13:36:c0 REACHABLE
ff02::1:ff42:dae4 dev eth0 lladdr 33:33:ff:42:da:e4 NOARP
ff02::2 dev eth0 lladdr 33:33:00:00:00:02 NOARP
ff02::16 dev eth0 lladdr 33:33:00:00:00:16 NOARP
::1 dev lo lladdr 00:00:00:00:00:00 NOARP
Mo 23. Mär 14:46:45 CET 2020
10.0.1.190 dev eth0 lladdr d4:38:9c:01:ac:6c REACHABLE
10.0.1.1 dev eth0 lladdr f0:9f:c2:1e:8f:e9 REACHABLE
0.0.0.0 dev lo lladdr 00:00:00:00:00:00 NOARP
10.0.1.136 dev eth0 lladdr 3c:97:0e:13:36:c0 REACHABLE
ff02::1:ff42:dae4 dev eth0 lladdr 33:33:ff:42:da:e4 NOARP
ff02::2 dev eth0 lladdr 33:33:00:00:00:02 NOARP
ff02::16 dev eth0 lladdr 33:33:00:00:00:16 NOARP
::1 dev lo lladdr 00:00:00:00:00:00 NOARP
And here comes the failed one (10.0.1.64)
[2020-03-23 14:51:00.052 15383] dbquery: "BEGIN TRANSACTION"
[2020-03-23 14:51:00.054 15383] dbquery: "END TRANSACTION"
[2020-03-23 14:51:00.066 15383] dbquery: "INSERT OR REPLACE INTO ftl (id, value) VALUES ( 1, 1584971448 );"
[2020-03-23 14:51:00.074 15383] dbquery: "UPDATE counters SET value = value + 12 WHERE id = 0;"
[2020-03-23 14:51:00.080 15383] dbquery: "UPDATE counters SET value = value + 0 WHERE id = 1;"
[2020-03-23 14:51:00.081 15383] Notice: Queries stored in FTL_db: 12 (took 31.2 ms, last SQLite ID 2179730)
[2020-03-23 14:51:00.082 15383] dbquery: "BEGIN TRANSACTION"
[2020-03-23 14:51:00.090 15383] dbquery: "SELECT id FROM network WHERE hwaddr = 'd4:38:9c:01:ac:6c';"
[2020-03-23 14:51:00.091 15383] dbquery: "UPDATE network SET lastQuery = MAX(lastQuery, 1584971170) WHERE id = 4;"
[2020-03-23 14:51:00.092 15383] dbquery: "UPDATE network SET numQueries = numQueries + 0 WHERE id = 4;"
[2020-03-23 14:51:00.092 15383] dbquery: "UPDATE network SET name = ? WHERE id = ?;" with arguments 1 = "sony-xz1-compact" and 2 = 4
[2020-03-23 14:51:00.092 15383] dbquery: "INSERT OR REPLACE INTO network_addresses (network_id,ip,lastSeen) VALUES(4,'10.0.1.190',(cast(strftime('%s', 'now') as int)));"
[2020-03-23 14:51:00.093 15383] dbquery: "SELECT id FROM network WHERE hwaddr = 'f0:9f:c2:1e:8f:e9';"
[2020-03-23 14:51:00.093 15383] dbquery: "UPDATE network SET lastQuery = MAX(lastQuery, 1584968871) WHERE id = 1;"
[2020-03-23 14:51:00.093 15383] dbquery: "UPDATE network SET numQueries = numQueries + 0 WHERE id = 1;"
[2020-03-23 14:51:00.093 15383] dbquery: "UPDATE network SET name = ? WHERE id = ?;" with arguments 1 = "usg" and 2 = 1
[2020-03-23 14:51:00.093 15383] dbquery: "INSERT OR REPLACE INTO network_addresses (network_id,ip,lastSeen) VALUES(1,'10.0.1.1',(cast(strftime('%s', 'now') as int)));"
[2020-03-23 14:51:00.094 15383] dbquery: "SELECT id FROM network WHERE hwaddr = '3c:97:0e:13:36:c0';"
[2020-03-23 14:51:00.094 15383] dbquery: "UPDATE network SET lastQuery = MAX(lastQuery, 1584971420) WHERE id = 2;"
[2020-03-23 14:51:00.094 15383] dbquery: "UPDATE network SET numQueries = numQueries + 4 WHERE id = 2;"
[2020-03-23 14:51:00.094 15383] dbquery: "UPDATE network SET name = ? WHERE id = ?;" with arguments 1 = "thinkpad-lan" and 2 = 2
[2020-03-23 14:51:00.094 15383] dbquery: "INSERT OR REPLACE INTO network_addresses (network_id,ip,lastSeen) VALUES(2,'10.0.1.136',(cast(strftime('%s', 'now') as int)));"
[2020-03-23 14:51:00.094 15383] Network table: Client 10.0.1.136 has zero queries (4241, 0)
[2020-03-23 14:51:00.094 15383] Network table: Client 10.0.1.2 has zero queries (88, 0)
[2020-03-23 14:51:00.094 15383] Network table: Client 127.0.0.1 has zero queries (372, 0)
[2020-03-23 14:51:00.094 15383] Network table: Client 10.0.1.1 has zero queries (73, 0)
[2020-03-23 14:51:00.094 15383] Network table: Client 10.0.1.215 has zero queries (23, 0)
[2020-03-23 14:51:00.095 15383] Network table: Client 10.0.1.64 known through ARP/neigh cache
[2020-03-23 14:51:00.095 15383] Network table: Client 10.0.1.182 has zero queries (33, 0)
[2020-03-23 14:51:00.095 15383] Network table: Client 10.0.1.190 has zero queries (1677, 0)
[2020-03-23 14:51:00.095 15383] Network table: Client 10.0.40.3 has zero queries (287, 0)
[2020-03-23 14:51:00.095 15383] Network table: 10.0.30.254 NOT known through ARP/neigh cache
[2020-03-23 14:51:00.095 15383] dbquery: "SELECT network_id FROM network_addresses WHERE ip = '10.0.30.254' AND lastSeen > (cast(strftime('%s', 'now') as int)-86400) ORDER BY lastSeen DESC;"
[2020-03-23 14:51:00.095 15383] APR: Identified device 10.0.30.254 using most recently used IP address
[2020-03-23 14:51:00.095 15383] dbquery: "UPDATE network SET lastQuery = MAX(lastQuery, 1584971448) WHERE id = 3;"
[2020-03-23 14:51:00.095 15383] dbquery: "UPDATE network SET numQueries = numQueries + 2 WHERE id = 3;"
[2020-03-23 14:51:00.095 15383] dbquery: "UPDATE network SET name = ? WHERE id = ?;" with arguments 1 = "chromecast-wohnzimmer" and 2 = 3
[2020-03-23 14:51:00.095 15383] dbquery: "INSERT OR REPLACE INTO network_addresses (network_id,ip,lastSeen) VALUES(3,'10.0.30.254',(cast(strftime('%s', 'now') as int)));"
[2020-03-23 14:51:00.096 15383] dbquery: "COMMIT"
[2020-03-23 14:51:00.101 15383] ARP table processing (3 entries from ARP, 1 from FTL's cache) took 19.1 ms
[2020-03-23 14:51:00.487 15383] 0 / 10 client host names resolved
[2020-03-23 14:51:00.487 15383] 0 / 1 upstream server host names resolved
Mo 23. Mär 14:50:52 CET 2020
10.0.1.190 dev eth0 lladdr d4:38:9c:01:ac:6c STALE
10.0.1.1 dev eth0 lladdr f0:9f:c2:1e:8f:e9 DELAY
0.0.0.0 dev lo lladdr 00:00:00:00:00:00 NOARP
10.0.1.64 dev eth0 FAILED
10.0.1.136 dev eth0 lladdr 3c:97:0e:13:36:c0 REACHABLE
ff02::1:ff42:dae4 dev eth0 lladdr 33:33:ff:42:da:e4 NOARP
ff02::2 dev eth0 lladdr 33:33:00:00:00:02 NOARP
ff02::16 dev eth0 lladdr 33:33:00:00:00:16 NOARP
::1 dev lo lladdr 00:00:00:00:00:00 NOARP
No false positive mock-device 
INCOMPLETE and FAILED entries are handled similarly now (we wait for more information to arrive).
Does this mean this feature finally arrives at being ready for review and merge into release/v5.0 ? 
I get an error switching back from an other branch to new/all_clients_network_table . Pihole complains about checksum error...
Please try again, the download seems to work as expected for me. Which architecture are you on (if it still fails)?
It's working again. Architecture: aarch64
@yubiuser I've seen you have also be reading on the other discussion, I'm wondering if we should now undo the non-adding of the incomplete or failed devices? It should now be fine to add them as mock-devices as FTL can now convert them into "real" devices once more ARP data arrives (at least if it arrives within one hour after the mock device was created, we can tweak this timeout).
Although the idea of transforming is appealing I want to point out some things:
I still don't know why @gpb saw those false-positive mock devices. I can't think of an arrangement between ARP and FTL that hasn't been covered by the existing code - except those devices show never up in ARP. But then the "right" solution would be to keep them as mock-devices (interpreting them as from other subnets). More debugging might be helpful to cover this edge case.
Transforming false-positive devices in real devices does make some strong assumptions which you were reluctant to make some days ago. And we can only make them because we know more than pihole knows.
This will probably create (and keep) false positive mock-devices in the table forever. Think about an device in stand-by/sleeping mode with no/incomplete/failed ARP for more than an hour. Or you have to remember that those FTL-devices had some kind of incomplete ARP entry (to distinguish between real FTL-only devices) and delete them after an hour.
I can't properly reassemble when @gpb saw the first duplicate but maybe it was right after flushing the network table. Maybe you should change the behavior of this button in the GUI as it seems to flush ARP as well - with bad timing FTL got some queries from a device since it last stored to the network table, then ARP is flushed manually and a few seconds later the network table will updated again - without those devices being represented in ARP. Then the devices do some more queries and ARP will be filled - voilá duplicates. Let the button clear just the network table in the database, not ARP/neigh.
With the latest changes (before transformation) I haven't seen a single duplicate. If I'm right with (4) we don't need the transformation and less assumptions.
@yubiuser I believe (grain of salt) I first saw the duplicate yesterday when you OK'd the changes in this thread...I concurred then noticed the N/A host name and mentioned the duplicate. I did run the table flush just before that. That said, it also could have been coincidence I didn't see it prior...not sure. I know I had not ever seen a duplicate prior as I mentioned to earlier in this thread, because I was watching for them. The sequence was always the same, mock showed first, then the proper record with (I guess that is retrieved via ARP). Still seems like timing...flush network table, before ARP calls are made, table is empty, then one or more hosts make DNS calls in that brief period, they don't exist in the network table so pihole adds them as mock, THEN ARP sequence is initiated and devices get added. Maybe...? Cheers. 
As the network table is updated every minute this must be slightly different.
host makes query (APR and FTL know about), flush network table (ARP is empty, FTL still knows), no further query is made, network table is updated (only FTL knows -> mock device), host makes query (APR and FTL know), network table is updated (as ARP knows the device -> duplicate real device is added)
That's why I asked @gpb to flush the table using the sqlite3 CLI command rather than the GUI button.
I was cautious making assumptions that IP addresses stay the same forever. My sole concern were/(and are still!) non-deterministic, i.e., enumerating DHCP servers. They seem to be t majority of devices out there. That's why there is the auto-transformation time limit of "the mock device has to be younger than one hour". And this is a hard limit to the creation time, not the time when the last query was seen from this device. I figured this is a reasonably safe assumption. And even if it isn't, the device in question will be added as a new device whenever it appears with its real (different) MAC address in the ARP cache.
The worst thing that could happen seems to be some counts being added to the wrong row for a while. There is no real harm here, otherwise as the IP address did belong to said IP address, so no wrong information is generated here.
We're still discussing, you've maybe seen that I temporarily removed the request for review and added the WIP badge on the PR yesterday. Nothing is written into stone and if we all come to the conclusion that the auto-transformation assumptions (even if already fairly restrictive) are too broad or that the whole thing is bad, well, then I'll simply undo this. Yes, it will be lost time coding this, however, this is not what counts in the end. Often enough, we have to try things and when we realize that this is not the direction to go, then we discard an idea. I'm not married to my code nor my individual commits.
Maybe @gpb can help us out by doing some more testing?
I suggest:
pihole checkout ftl new/all_clients_network_table_no-auto
sudo sqlite3 /etc/pihole/pihole-FTL.db "DELETE FROM network; DELETE FROM network_addresses;"
ip neigh at the time the first false-positives pop up to narrow down the issueFurthermore:
incomplete or failed as it will prevent addition of false-positives when we keep the transformation commit (and a device is longer > limit with incomplete ARP)Yes, use new/all_clients_network_table_no-auto
This code has been merged into the regular beta code so everyone will benefit. Please re-checkout the beta branches to continue receiving updates and new features:
pihole checkout ftl release/v5.0
Thanks for all your contributions!