I want to use PiHole in every household of the family. (Let´s say, I am managing 3 PiHoles) So I want to automate things, like adding blocklists to PiHole. To have every PiHole blocking the same URLs. In PiHole v4 I was preparing a script, that downloads a adlist.list that I maintain centrally on a webspace. It than replaces the downloaded version with the local version. Its like:
wget -t X http://domain.com/PiHole/adlist.list -p /etc/pihole/
cp /etc/pihole/adlist.lis /etc/pihole/adlists.list
But I read the new Version works a bit different from this. So how to achive a central Blocklistmanagement in PiHole version 5?
Instead of downloading the list of blocklists to the file adlists.list (which no longer exists), insert the adlist URLs into the gravity database(s) with SQL commands.
sudo sqlite3 /etc/pihole/gravity.db "select address from adlist" > /home/pi/adlists.list
lists from file (this will only add new entries, it doesn't remove any lists):
while read list
do
sudo sqlite3 /etc/pihole/gravity.db "insert or ignore into adlist (address, enabled) values (\"$list\", 1);"
done < /home/pi/adlists.list
after the import, run pihole restartdns reload-lists
I am now at this point: But doesnt work:```
while read -r LISTURL || [[ -n "$LISTURL" ]];
INSERT INTO adlist (id,address,enabled) VALUES ($id,$LISTURL,1);
done < http://domain.com/ListOfLists.txt
As far as I know, you can't specify the id field, the id field is the primary key and autogenerated by sqlite3, it will increment automatically, when a new entry is inserted.
I don't see what your trying to achieve with the or (||) function in the read statement, read the list from file, keep it simple.
I don't have a clue if you can redirect from url, wget the file from the remote host and
test if the file has content, using something like:
wget http://domain.com/ListOfLists.txt - O /home/pi/ListOfLists.txt
if [ ! -s /home/pi/ListOfLists.txt ]; then
exit
fi
The example I provided works (has been since the beginning of beta5).
If you want to empty the table (to remove lists that are no longer exist in the export), look at this (NOT TESTED -use at your own risk).
I decided to put a little more effort into this so it's not deleting everything every time we update the adlist. for me this is part of my update script to keep my pihole fresh. this will only remove or add sites from adlist if needed...
#!/bin/bash
# exit on any error
set -e
# udpate raspbian
sudo apt-get update -q
sudo apt-get dist-upgrade -qy
sudo apt-get autoremove -qy
sudo apt-get autoclean -q
sudo ldconfig
# update pihole
pihole -up
# update pihole blocklists
sqlite3 /etc/pihole/gravity.db "SELECT Address FROM adlist" |sort >/home/pi/pihole.list
wget -qO - https://v.firebog.net/hosts/lists.php?type=tick |sort >/home/pi/firebog.list
comm -23 pihole.list firebog.list |xargs -I{} sudo sqlite3 /etc/pihole/gravity.db "DELETE FROM adlist WHERE Address='{}';"
comm -13 pihole.list firebog.list |xargs -I{} sudo sqlite3 /etc/pihole/gravity.db "INSERT INTO adlist (Address,Comment,Enabled) VALUES ('{}','firebog, added `date +%F`',1);"
pihole restartdns reload-lists
pihole -g