For a while I have had an error in my home automation system that caused my hub to do hundreds of request to popeen.com every night. Today I finally sat down and fixed it and once it was fixed I also wanted to purge all the lines from the database so it doesn’t screw up my statistics anymore. Doing so was fairly simple but took me a few minutes to figure out so I decided to write it down here. That way I can come back here whenever I need to do it again and hopefully help someone else out at the same time.
First you SSH into the server running pihole, I am assuming you know how to do that already and if not there are plenty of guides for it if you google it so I won’t cover it here.
The first thing we need to do is shutdown pihole so we are free to modify the database, to shut down pihole run the command
sudo service pihole-FTL stop
Then we need to access the database, we do so by running the command
sudo sqlite3 /etc/pihole/pihole-FTL.db
ok, now it’s time to delete the domain, we do this by running this command, replace popeen.com with the domain you want to purge.
delete from queries where domain = "popeen.com";
when you have purged all the domains you want we exit the database by hitting CTRL + C
and lastly we start pihole up again by running
sudo service pihole-FTL start
This looked promising, but the 3rd step now results in this:
sqlite> delete from queries where domain = “example.com”;
Error: cannot modify queries because it is a view
Ah, it works if you use this command:
sqlite> delete from query_storage where domain in (select id from domain_by_id where domain like ‘example.com’);
Got this from https://gist.github.com/dentex/1ff0994e0430bdfa7326d29d09429ddc