I have a script that collects the hwaddr and last query from pihole-FTL.db
Is there a way to stop devices displaying as having the same MAC address being combined together
the pihole DHCP detects the MAC addresses as separate but the network table detects them as the same
the mac address shown is a wireless range extender
after looking in the database all the network ids are 2 for the affected devices
The DNS protocol is unaware of MAC addresses, using IP address information to identify a requestor.
Pi-hole puts in additional effort (beyond DNS) to associate IP addresses to MAC addresses, by inspecting its host's ARP and IPv6 neighbour tables.
As MAC addresses are strictly same link only, those tables only holds MAC address information for devices on the same network link as Pi-hole.
Thus, your wifi extender is listed correctly in Pi-hole's Network overview as observed.
That would suggest that your wifi extender is running a DHCP relay that would provide a client's MAC address (as observable by that extender on its own wifi link) in its relayed DHCP request to Pi-hole, allowing your wifi extender's clients to acquire a DHCP lease through Pi-hole (or another DHCP server), even if that DHCP server is on a separate link.
Note, however, that grouping of IP addresses to your extender's MAC is happening in Pi-hole's network overview - it does not affect DNS requests:
As said, the DNS protocol relies on IP address information to identify a requesting client.
Pi-hole's Query Log still shows the requesting IP, and thus you still can apply client-specific filtering.
thats right, each device is shown correctly in the query log and dashboard but as I said I am trying to setup a script to display this information on a conky window
there also combined in the database somehow, the network table just has the one MAC address for the extender, and network_addresses table has them all with he same network id
Since you are trying to visualise DNS requests, I'd recommend to refrain from using MAC addresses and go with IP address information to identify clients.
to get the last query data, the only option to link it with a device name and ip is hardware address
the last seen data in network_address has IP and hostname but is inaccurate with the timestamp always being the current time, no the time the device was last used
No.
As mentioned, DNS is unaware of MAC addresses.
As a result, MAC addresses as well as hostnames can only be inferred - IP address information is the original piece of data identifying a client in a DNS request.
How does your script try to get hold of the most recent query?
I fetech the hwaddr and lastquery with sqlite3
sudo sqlite3 /docker/pi-hole/etc/pihole/pihole-FTL.db 'select hwaddr,lastquery from network'
then fetch the static dhcp file
search the staic dhcp file for the MAC and change the MAC to the ip address
then print the IP and timestamp after convering into human readable format
all this is done within python with the subprocess.getoutput command
You are not fetching the most current query then.
Instead, you are retrieving the last time when Pi-hole received a DNS request originating from a specific MAC address. Essentially, that is exactly same data as displayed on Pi-hole's Network overview.
What's your intention here?
What data do you want to display for what purpose?
If you really wanted to show the last time a client IP made a DNS request during the past week, you could use something like:
pihole-FTL sqlite3 /etc/pihole/pihole-FTL.db "SELECT client, max(timestamp) FROM queries WHERE timestamp > strftime('%s','now','-7 days') GROUP BY client ORDER BY 2 DESC;"
Thats what I am wanting, just want to display it in conky in realtime, not though a browser
I want the timestamp of when a device was last used / its last query made
ChatGPT / bing copilot give me something similar but it was too slow updating the information
thats actually quite fast, the chatGPT one was fetching one at a time
thank you, that might be useful
there is a few device with older timestamps but that's was the database read, when I manually checked
it must have lost some data at some point recently
my device at 192.168.1.6 is a family member who does not live here, but they have been since the timestamp, all DHCP is static though pihole
as long as the database keeps data this will work thank you
That's confusing.
I thought you did not want aggregate data by MAC (as in Pi-hole's network overview), but rather by client IP?
It will be faster the narrower the timeframe is, e.g. you could reduce that to a day or even an hour.
Note that those two SQLs are answering different questions:
Yours as adapted from Pi-hole's Network overview:
What's the last time ever Pi-hole has seen a DNS request originating from a given MAC?
My suggestion based on Pi-hole's Query Log:
When was the most recent request from a client as observed by Pi-hole during the last week/last day/last hour?
It would be essential that you decide on your question before you design the corresponding SQL statements.
its better to do it by IP
I wanted to do it by IP, just was using hwaddr and last query as there in the same table
anyway
I actually increased it from 7 days to 30 days, as the speed is still instant, well instant enough that you do not notice it skipping seconds in the script, when the timestamp is converted
You managed to understand what I actually needed, I know my wording is not always the best
your suggestion was the best way to do this, thank you
just a quick question with this being fetched from the database, is the database updated in realtime or every few seconds or seems to be like every 5 minutes, maybe its just device not being that active
again, thank you for this
Pi-hole collects queries and then writes them to the database once every minute, see DBINTERVAL.
If your script would manage and persist its own data, you could prime that by running an initial SQL statement with a long time window (or no 'WHERE timestamp' at all), and then regularly run the statement for the last hour to update your data. That way, older source IPs would also show up, even if they did not make a query for a year.
I found that in the documentation, and have made it 0.5
my script gets the data every second, and I am running into database locks and high system load, But if I reduce the how often the data is fetched its not as up to date as it could be
I could write the data to a text file every few seconds, and then my script would not hang when the database is locked
That would be the case if I did not change the database length for 365 to 60
I wish I havn't have done that, as I have some blanks in data with device that are not used much