pihole-FTL.db has some undocumented tables, more specifically aliasclient and message.
Could you please explain, possibly document, the goal and usage of these tables?
Thanks for your time and effort.
Just a quick overview here:
is used to define alias-clients (AKA super-clients). We discussed this here on the forum, so the search will reveal this for everyone. We haven't added a user interface page for them, so far and this will be part of a future release of Pi-hole.
This will contain messages sent by your Pi-hoe to you. They are meant to be machine-readable but the format has been chosen such that also humans can estimate what they mean. If there are any messages, they will be shown on the dashboard like:
Thank you for this, sorry, but I have to ask for additional info, this to see if I understand the feature correctly.
I was never very interested in the superclient (now aliasclient) idea, because my pi doesn't see the MAC addresses of the devices that use pihole (pi is on an isolated physical segment of my network). It looks like the approach has changed over time (MAC address dependency is gone), making it possibly useful for my network layout.
Please confirm if my interpretation is correct.
In the aliasclient table, I simply enter (example):
In the network table, I would than add the aliasclient_id (1) to all the entries related to that machine (Y50)
There are a lot of entries, because Windows 10 uses temporary IP addresses, which change every day or every restart.
I than issue the command pkill -RTMIN+3 pihole-FTL (see signals in the documentation - thanks for that)
result in the pihole-FTL log:
[2021-01-19 12:43:08.018 1145M] Received: Real-time signal 3 (37 -> 3)
[2021-01-19 12:43:08.046 1145/T1149] Imported 1 alias-client
result in the dashboard:
It's correct that I don't see the aliasclient name in the query log:
Since I see the aliasclient name in the dashboard, I assume I'm understanding things correctly, just need to write a script to add the correct alliasclient_id to the network table, whenever a new temporary IPv6 is added to the network_addresses table
Yes, what you wrote there is all correct.
This is implementation-caused. Alias-clients are virtual clients that sum up all the statistics of the associated clients under their namespace. All the associated clients are then hidden from the statistics (over time data, top clients tables). So far, alias-clients do not extend to the Query Log, however, I can see that this could be useful. Putting this somewhere in the middle of my ToDo list (which is actually getting a bit shorter these days).
Pro / con for this change
- Con: being able to see the original name helps to determine which interface was used to do the query, and may help identifying the program / app, that is responsible for the query.
- Pro makes it easier to see all queries from a specific device, although this is already possible, using the long term query log, e.g. Y50eth0, Y50wifi and Y50ipv6 all show up when performing a long term query search with Y50 as the search term.
if at all possible, make this configurable, either by setupVars variable or checkbox on the query log page (preferred).
aliasclient doesn't show recent queries. Little bug?
dashboard:
click on Y50 -> no data available in table
Well, yes and no. No because the link should likely not be there. Yes because this is actually expected:
As said, alias-clients were implemented for those who really need it but we haven't had the time to code the proper interface nor do more involved testing (what you did now) so somethings are expected to work suboptimal at the moment.
Thanks for testing this, however, I don't see us making another v5.x release (unless we have to fix a bug in a v5.5.2 patch release), as v6.0 is right on its way. Hopefully, we can open a beta round sometime this year. The v5.0 beta period was very productive as well. And as reward for testing, things can get implemented very quickly during these periods (I know you benefited from this as well 
).
But, hence, I'd like to postpone further work on alias-clients for now. It is not even clear how settings will be stored in v6.0. We'll likely store them in the database instead having to deal with setupVars.conf any longer, but that all is very much undiscovered and will be explored once the API/web interface came its way.
Another bug?
Y50 is the alias client.
y50ipv6.localdomain are the clients that have an alias id
I think these clients should NOT show up in the "top clients (total)" and "top clients (blocked only)", because the alias client (Y50) already represents them.
just my two cents...
First we have to check: Are you sure you have all the y50ipv6.localdomin in network associated to Y50 and sent the signal afterwards?
Then the problem is likely that:
- The answer is No, and
- New IP addresses do not inherit the alias-client property.
I agree that such an inheritance (if set for all addresses of a MAC address) is a meaningful thing.
edit: "New IP addresses" are meant as "New records in network with hwaddr starting in ip-...
YES, I'm currently working on scripting this, but to test the effects on the dashboard, I manually updated the alias id in the network table, using phpliteadmin, and issued the signal on the command line. The log (pihole-FTL.log) shows the signal is processed.
- What does hovering the two remaining entries in the table show?
- Are these two IP address registered in the
networktable and assigned toaliasclient-id = 1? - Would there usually be more
y50ipv6.localdomainin the table?
shows the IP address, which is the temporary IPv6 address from the device at that time (2a02:1810:4d02:6903:3d00:3c24:e09:3574 and 2a02:1810:4d02:6903:81a8:f2cb:3617:559d)
YES
As explained before, windows 10 uses temporary addresses, these change every 24 hours or when the device is restarted. Because my pi doesn't see the MAC addresses (pi is on isolated physical network), I have written a script (refer our conversation and the pkill -RTMIN+4 pihole-FTL signal to force update the network_addresses table), that pulls the neighbor information from pfsense, and ensures the new temporary address (a new client, as far as FTL is concerned) can be resolved to a name (always the same name for the device, identified with the MAC address from the neighbor info). This implies I have, after a while, several client entries from a single device with a different (temporary) IPv6 address, same name. To get all the queries from the device (all different addresses, but same name), I use the long term query analysis, search = name. The alias client would make this easier, and the interface cleaner, unfortunately, some web interface bugs still prevent this.
I'm confident this will eventually be fixed.
sorry for the delay, was working on the script to automate the database update, needed to switch from flat file to json config file to allow processing of network_addresses and network table in a single script with only one config file. This took some time...
Unfortunately, I can no longer duplicate the problem where the individual clients showed up, next to the alias client, so I must of done something wrong, which I than unknowingly corrected.
Thus this bug report can be ignored for now, If it happens again, I will report it.
Added a second aliasclient (the pi), network table now looks like this
Happy to report aliasclient_id can be added to a MACaddress entry
and the result is perfect in the dashboard
I can confirm the solution works perfectly with an alias client, based on the mac address and the mac address 00:00:00:00:00:00 (localhost 127.0.0.1 and ::1)
I have some follow up questions:
- Which entry is added first by pihole-FTL, the entry (IP address) in the nework_addresses table OR the entry (ip-IP address in the network table?
For my script, that modifies the aliasclient_id in the network table, i want to test if the entry already exists, before executing the update statement. Knowing the order these entries are added, would eliminate on of the tests.
- in this topic, you mentioned the command sudo -u pihole sqlite3 /etc/pihole/gravity.db "BEGIN EXCLUSIVE;COMMIT"
I've trying to read up on this, but don't fully understand the impact and usage. Is this something that would be usable / recommended in scripts to eliminate the "database is locked" problem?
- I'm now using both pkill -RTMIN+4 pihole-FTL (force name update in network_addresses table) and pkill -RTMIN+3 pihole-FTL (force alias client update).
Is there a preferred order to send these signals (currently using 4, than 3 in my script)?
As always, thank you for your time and effort, much appreciated.
They are added at the same time. Technically, the network entry comes first, then the network_addresses entry, however, the difference should only be a matter of at most milliseconds.
This command does nothing except failing if the database is already locked. I just use it to test if somebody/something is holding the database locked. I don't think it can be used to remedy any database locking issues. FTL depends on the database being unlocked whenever it accesses it. Otherwise, DNS resolution would be paused as long as the database is locked. Hence, we skip reading from the database when the file is locked (externally) to avoid any DNS resolution issues introduced by external scripts.
No, they are added to a queue of events and FTL processes them them whenever it has some spare time for them (usually, this is close to instantaneous, however, there could also be some delay).
This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.