Please follow the below template, it will help us to help you!
Please ensure that you are running the latest version of the beta code. Run pihole -up to update to the latest, then verify that the problem still exists before reporting it.
Problem with Beta 5.0:
I was messing around with the sqlite db, but noticed there was no whitelist table. Does anyone have any helpful commands to list everything that's in the whitelist and which adlists are being used, and then how to update them in bulk? Batch updates via CLI has always been infinitely better than using the GUI.
The documentation for Pi-hole v5.0 can be found here:
What you are specifically looking for is the domainlist table containing all white- and blacklisted domains, see Overview - Pi-hole documentation
Furthermore, you're looking for the adlist table containing all the adlists, see (on the same page, one further down): Overview - Pi-hole documentation
Bulk updating can be done using usual SQL syntax, I assume you're already familiar with this.
Well, this is your personal opinion. Maybe (if not most) would disagree. You can also bulk insert through the web GUI, however, bulk editing or deleting is obviously something that is reserved for the much more powerful direct interaction with the database. We made everything as easy as convenient.
For instance:
SELECT * FROM domainlist WHERE type = 0;
SELECT * FROM adlist;
See the linked documentation pages for further details.
Sorry, I didn't mean to come off as rude or anything, but I see no way to add more than one whitelisted (or adlist for that matter) domain at a time via the GUI. The scenario that I am having trouble with is this: I setup a new pihole instance and want to add my 200+ whitelisted domains. With the whitelist file gone, which we used to be able to simply manipulate with bash, I don't see a way to add these domains other than 1 by 1. A few friends and I have our whitelisted domains in a git repository and could simply pull them into our whitelist file via basic bash scripting.
I imagine this might be possible via interaction with the db, unfortunately my sql knowledge is pretty limited (am more of a infrastructure/syseng guy).
I haven't looked at the gui for a while but if there are limitations for adding single items at a time, I could probably write something to export adlists, whitelists, blacklists, regex etc to text files, to be then be modified and imported back. It just depends whether it's really necessary to do so or not as it may take a little time.
Edit: This would be third party. I'm not a pihole dev if that's not immediately obvious lol
root@pihole:~ # sqlite3 /etc/pihole/gravity.db
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> SELECT * FROM domainlist WHERE id = 0;
sqlite>
This may not be obvious by the UI's layout:
At least in v4, you should be able to add a list of domains you copy e.g. from an editor, one line per domain, and paste it into Pi-hole's single line field.
I ocassionaly did that with my (admittedly much shorter) whitelist of four entries in the past.
As I have no idea how well it would agree with your 200 domains, just have a go and see if you can confirm this to work for Beta 5.0
You got 2 options, or 3 using GUI just waiting for @DL6ER pull request to get merged
First you can either use the pihole command to whitelist multiple domains (space separated) like pihole -w domain1 domain2 domain3
Or you can take a look a the file /etc/.pihole/advanced/Scripts/list.sh
which is where the all the code is for adding domains from cli.
Here is an example bash script for adding whitelist domains (newline separated) from file
!#/bin/bash
pihole -w $(tr '\n' ' ' < input_filename)
Pihole will check if the domain is already inserted so you don't have to worry about that.
You can just use wget or curl (Edit forgot git) to download the list, have fun scripting!
Most of that ends up in /opt/pihole/ as utility scripts. Check your local install in that directory. Don't use /etc/.pihole as that is really meant to be hidden and may not be there in the future.
As for deleting a list, it would be better to assign a list to a group so it will be
easier to delete/maintain a list, so instead of using a comment, just reference the group to delete.
Unfortunately, i don't quite know how i should work in cli, as the command will be very messy.
Edit my brain is a little slow, you can have something like (just use a file instead)
pihole -w --group group_name whitlist.txt
Another problem is that the data doesn't output from the table neatly (or in a standard format), making it tough to sanitize. It outputs like so:
211|0|www.plex.tv |1|1581987736|1581987736|Migrated from /etc/pihole/whitelist.txt
212|0|youtu.be |1|1581987736|1581987736|Migrated from /etc/pihole/whitelist.txt
213|0|zeustracker.abuse.ch |1|1581987736|1581987736|Migrated from /etc/pihole/whitelist.txt
216|0|iphonesubmissions.apple.com|1|1582161825|1582161825|
Via awk you can sanitize it to what's below, but that doesn't help much either.
The first 3 is the result of the old whitelist.txt file that is migrated automatically & the last one, 216, was an addition via pihole -w. The output goal would just be the domains themselves. I generated that via
sqlite3 /etc/pihole/gravity.db "SELECT * FROM domainlist WHERE type = 0;" | awk '{print $1}'
