Option to remove entries from new Network tab

#1

The new “Network” section has a couple incorrect entries in it that I’d like the ability to remove. Even if I had to reset the whole table, that would work for my needs.

Alternatively, the ability to remove devices that haven’t queried the pi-hole in 24 hours would work.

I’m not sure how it happened, but the for me the table currently lists a couple MAC / host name combinations that I can tell are wrong. It would be nice to be able to clean up the table.

#2

For now, you can manually edit the network table in the /etc/pihole/pihole-FTL.db database.

1 Like
#3

Do you have any recommendations what to use to edit the database? It’s a pretty chunky file and I’m not familiar with editing SQL files.

#4

Use sqlite3. There are plenty of online tutorials for it as well.

#5

You can use a web browser to view the FTL database, explained here. The database can be made writable, explained here.

1 Like
#6

I copied the file, made the edits (deleted a couple entries) using sqlitemanager, and copied the file back. When I re-enable query logging, the changes are reflected in the network table but the network table data no longer updates, e.g. the “last seen” never updates. Other tables continue to work as expected. So I broke something by manually editing the database. Restarting the pi didn’t fix it. I restored a backup of the database. Not sure where I messed up.

#7

What are the permissions on the file?

#8

The original file was set to 644, owned by pihole:pihole. I set the edited database back to those permissions/ownership after the edits. Other tables continue to update in the edited database; new queries are successfully recorded. The only thing that broke was the network table, which no longer adds new devices or updates existing entries. If I empty the table completely, it just stays empty regardless of what activity is showing in the query log.

Last night I also tried flushing the logs (on an unedited database) to get a fresh start on the network table. But the table persists after flushing the logs, which (I think) it shouldn’t do.

#9

Run pihole -d for a debug token.

#10

Was just about to post below :wink:

Try fresh with the tools that are already available and known to work.
First create backup:

cd ~

sqlite3 /etc/pihole/pihole-FTL.db ".backup main pihole-FTL.db.bak"

See what columns have been created in the network table (id, ip, hwaddr, interface etc):

sqlite3 /etc/pihole/pihole-FTL.db ".schema network"

See what entries are in the network table now:

sqlite3 /etc/pihole/pihole-FTL.db "SELECT * FROM network"

Or:

sqlite3 /etc/pihole/pihole-FTL.db ".dump network"

Delete the unwanted entries based on the “id”, the first column in the network table (replace the 9999 id number):

sudo -u pihole sqlite3 /etc/pihole/pihole-FTL.db "DELETE FROM network WHERE id = 9999"

And check if its deleted now:

sqlite3 /etc/pihole/pihole-FTL.db "SELECT * FROM network"

To rollback and restore the backup in case things go wrong:

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

1 Like
#11

You can use the equals sign instead of LIKE: WHERE id = 9999

Edit: Also, the double quotes in that query will fail the command because Bash will use them instead of SQL. SQL uses single quotes for strings.

2 Likes
#12

Changed it.
Worked for me before somehow :wink:

#13

deHakkelaar, thanks so much for your detailed instructions. It worked perfectly and the table continues to function after the edits. Thanks for your help!

I will submit a debug file if the devs would find it useful, but I think I just broke the database because I don’t know SQL.

1 Like