Add comments to domains on the CLI

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.

Debug Token:
N/A

1 Like

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 https://deploy-preview-201--pihole-docs.netlify.com/database/gravity/#domain-tables-domainlist

Furthermore, you’re looking for the adlist table containing all the adlists, see (on the same page, one further down): https://deploy-preview-201--pihole-docs.netlify.com/database/gravity/#adlist-table-adlist

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.

edit Fixed SQL instructions.

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

This doesn’t return anything for some reason.

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>

I’m pretty sure @DL6ER meant to say “type” instead of “id”, so
SELECT * FROM domainlist WHERE type = 0;

2 Likes

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 :wink:

See here for CLI and here for GUI

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! :grinning:

This is where you would use the teleporter to export and import these entries from your existing Pi-Hole to the new one.

This is perfect, I had no idea this directory even existed.

Not really helpful when using a git repo for our adlists and whitelists. Used to be as simple as a wget somegithuburlhere > /etc/pihole/whitelist

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.

Understood, thanks Dan. I’ll see if I can get something to work.

1 Like

In the webinterface you can state a comment which used for all domains inserted.

This seems not be the case when using pihole directly. Or is the listname also used for the comment field?

By using pihole directly i assume you mean cli?
if so, after looking at the list.sh, it does not include any comment.

Looks like pihole cli can be updated to include a comment when inserting any list?
Someone make a pull request quick :yum:

Yes at the cli. One comment string given when using pihole on the cli to be added. That same comment string can then also be used to identify specific domains to be deleted (–delmode) or when listing (–list).

Example: --comment can be omitted and then add then domain(s) are needed for delete.

pihole -w --comment Whitelist_example --wild site1.com site2.com

pihole -b --comment Blacklist_regex_example --regex \.fb\.me$

pihole -w --comment Whitelist_example  --wild -d

pihole -b  --wild -d example.com

pihole -w --comment Whitelist_example  -l

pihole -w --wild -l

etc.

I don’t know if the comment can contain spaces or have to be between " ".

1 Like

The comment part looks good.

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

1 Like

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.

211|0|www.plex.tv
212|0|youtu.be
213|0|zeustracker.abuse.ch
216|0|iphonesubmissions.apple.com|1|1582161825|1582161825|

What is the input and what is the output you’d like to see?

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}'