Nice work, thnx for the update!
Is there a way to flush the information in the new "Network Information Table"?
There are many devices in there that are very old. I'd like to start fresh.
Thnx!
If its ARP based, below one flushes ARP cache:
sudo ip -s -s neigh flush all
did not work... all entries are still there
Not sure what I just flushed... hope it will not break things
Na ... ARP cache gets populated again but needs to be rediscovered.
EDIT: run below one to see whats in it now:
arp
ok, that might be it. Running arp only lists eight devices. Still in the webinterface 4 pages of devices remain listed. How does 'rediscovering' work? Is that something I have to wait for, or something I can force?
Whenever a new network connection gets established, an ARP entry will be automatically created:
Try flush ARP immediately followed by an arp listing and wait a bit before listing again to see what devices connected:
sudo ip -s -s neigh flush all && echo '--arp--' && arp && echo '--sleep a bit--' && sleep 30 && arp
1 Like
Hmm I believe those are the ones stored in the database:
pi@noads:~ $ echo 'SELECT * FROM network WHERE hwaddr like "5c:33:8e:XX:XX:XX";' | sqlite3 /etc/pihole/pihole-FTL.db
6|10.0.0.244|5c:33:8e:XX:XX:XX|eth0||1558324861|0|0|Alpha Networks Inc.
Not sure if maintenance is done on those ?
Not yet. This is planed for the near future, however, we are not yet decided how to do this best. In small networks, you may want to see the history of devices forever. On larger networks, you maybe don't want to do this.
Yes, you can flush the network table to start afresh.
1 Like
I was succesfull in deleting a single MAC with below one:
echo 'DELETE FROM network WHERE hwaddr LIKE "50:46:5d:6c:XX:XX";' | sudo -u pihole sqlite3 /etc/pihole/pihole-FTL.db
I imagine this to do no harm or do I miss something ?
That should not be an issue. Note that you don't have to pipe it in though, as sqlite3 will accept:
sqlite3 /etc/pihole/pihole-FTL.db "SQL QUERY"
1 Like
Newby question: how would I flush the network table?
I suppose it has to begin with sudo but what next?
To start with a fresh slate, first flush the actual ARP cache with below one:
sudo ip -s -s neigh flush all
Backup the database in case things go wrong:
cd ~
sqlite3 /etc/pihole/pihole-FTL.db ".backup main pihole-FTL.db.bak"
See whats in it now:
sqlite3 /etc/pihole/pihole-FTL.db "SELECT * FROM network"
Delete them all:
sudo -u pihole sqlite3 /etc/pihole/pihole-FTL.db "DELETE FROM network"
And check again:
sqlite3 /etc/pihole/pihole-FTL.db "SELECT * FROM network"
To rollback and restore the backup:
sudo service pihole-FTL stop
sudo -u pihole sqlite3 /etc/pihole/pihole-FTL.db ".restore main pihole-FTL.db.bak"
sudo service pihole-FTL start
Ps. It might be that you experience a "database is locked" error because pihole-FTL probably locked some temporarily.
Wait a bit and run the query again.
EDIT: removed "VACUUM" from query.
6 Likes
That does the trick, thank you, dank je wel!
1 Like
You can also look at the network table in the pihole-FTL.db, using this addon to pihole (customizing pihole - Browsing the FTL database).
- password: admin
- select the database: /etc/pihole/pihole-FTL.db
- select
network
edit
If you try to edit or delete a record, you get the following error message:
NOT a bug, it requires you to set the proper permissions on both the database (/etc/pihole/pihole-FTL.db) and the folder (/etc/pihole), reason: The database-file itself must be writable, and, to write into it, the containing directory needs to be writable as well. This is because SQLite puts temporary files in there for locking.
@DL6ER Question(s):
- How to find out witch user is actually accessing the file and folder.
- What is the best way to allow this user (web application) access to the file and folder
P.S. applying 777 to the folder and 666 to the file allows the web application to write to the database, but I assume this is NOT the proper way to do this, hence the questions.
Thanks for your time and effort.
/edit
1 Like
See if below ones clear things up a bit 
pi@noads:~ $ sudo lsof
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
[..]
php-cgi 777 www-data 6rr REG 179,2 1740800 134391 /etc/pihole/pihole-FTL.db
[..]
pi@noads:~ $ grep 'www-data\|pihole' /etc/passwd
www-data:x:33:33:www-data:/var/www:/usr/sbin/nologin
pihole:x:999:996::/home/pihole:/usr/sbin/nologin
pi@noads:~ $ sudo cat /etc/sudoers.d/pihole
[..]
# Allows the WebUI to use Pi-hole commands
[..]
www-data ALL=NOPASSWD: /usr/local/bin/pihole
pi@noads:~ $ stat /usr/local/bin/pihole
Access: (0755/-rwxr-xr-x) Uid: ( 0/ root) Gid: ( 50/ staff)
pi@noads:~ $ ps -o pid,uid,user,gid,group,cmd -C pihole-FTL -C lighttpd
PID UID USER GID GROUP CMD
574 33 www-data 33 www-data /usr/sbin/lighttpd -D -f /etc/lighttpd/light
8738 999 pihole 996 pihole /usr/bin/pihole-FTL
pi@noads:~ $ stat /etc/pihole/pihole-FTL.db
Access: (0644/-rw-r--r--) Uid: ( 999/ pihole) Gid: ( 996/ pihole)
EDIT: plus there is communication between the web frontend and the 4711 TCP API backend.
You should not need to run the VACUUM query. I expect it to take minutes up to even hours for very large long-term databases.
It's the user pihole as @deHakkelaar already mentioned. It should be the legit owner of this file on your system. Permissions are (partially) corrected when starting FTL. See /etc/init.d/pihole-FTL for further details. Changes to this file will be overwritten on update/repair as it is a critical part of Pi-hole.
First: This is generally a bad idea.
Once you have acknowledged this and still want to continue, the cleanest way is to add the user www-data to the group pihole to give it proper permissions to the database file. Make sure the group permissions are high enough on the folder and the file (this should be the case).
This may NOT be the best idea, but isn't it safer to use a web interface than doing this using sqlite3 commands, example here?
By adding the user www-data to the group pihole, setting the permissions on /etc/pihole to 775 and the permission on /etc/pihole/pihole-FTL.db to 664, It worked, after restarting the lighttpd service.
NOT sure I understand the concept of sudoers, www-data already has an entry in /etc/sudoers.d/pihole. I was wondering if this couldn't be achieved, using a sudoers entry?
1 Like
The sudoers files are all about sudo, i.e., you can specify which users are allowed to run which commands through sudo. This has nothing to do with file permissions.
I tried TRUNCATE at first until I figured out sqlite3 doesnt support this type of query.
Maybe its not so smart to run VACUUM after deleting only a few rows 
Leave that to general maintenance.
Adjusted in above posting of mine.
I recon the tools we use for administrative tasks (sudo -u pihole sqlite3 & your web UI) are all unsafe in one way or another.
For example I didnt use sudo to read the dbase and only when do critical changes, I used sudo.
And the sudo to root, or any user, capability should only be available to users that need root user privileges for administrative tasks:
pi@noads:~ $ sudo cat /etc/sudoers.d/010_pi-nopasswd
pi ALL=(ALL) NOPASSWD: ALL
But in general, everything that you add to the system, like a web UI to administer the dbase, makes the system less secure/stable and more complex to maintain.
It has an entry to do only one command with elevated privileges (NOPASSWD):
pi@noads:~ $ sudo cat /etc/sudoers.d/pihole
www-data ALL=NOPASSWD: /usr/local/bin/pihole