Connect local "list.*" in /etc/pihole/ to source URL

[Palaress]

Hi,

over the time I have accumulated quite some Adlists.
Now I wanted to get rid of some (useless ones) and was wandering where the link between the URLs of Adlist and the local list files in /etc/pihole/ is made.

I have many lists in the style of list.[0-9]+.raw.githubusercontent.com.domains in /etc/pihole/. I would need to compare them 1 by 1 to be able to tell what file belongs to what URL, which is super annoying.

Any way to make the connection between local file and source URL automatically? Is there a table in one of the DBs maybe?

Thanks in advance.

[rdwebdesign]

I'm not understanding exactly what is your goal:

Do you simply want to free space in /etc/pihole?

Do you know which URLs you want to delete/keep, but don't know the files?

Did you use some method to find out which files you want to delete/keep, but don't know which URLs should be deleted/kept?

[deHakkelaar]

Adlists are sourced from a database and not a file:

pi@ph5a:~ $ pihole-FTL sqlite3 /etc/pihole/gravity.db ".databases"
main: /etc/pihole/gravity.db r/o

pi@ph5a:~ $ pihole-FTL sqlite3 /etc/pihole/gravity.db ".tables"
adlist               domainlist_by_group  vw_gravity
adlist_by_group      gravity              vw_regex_blacklist
client               group                vw_regex_whitelist
client_by_group      info                 vw_whitelist
domain_audit         vw_adlist
domainlist           vw_blacklist

pi@ph5a:~ $ pihole-FTL sqlite3 /etc/pihole/gravity.db ".schema adlist"
CREATE TABLE adlist
(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        address TEXT UNIQUE NOT NULL,
        enabled BOOLEAN NOT NULL DEFAULT 1,
        date_added INTEGER NOT NULL DEFAULT (cast(strftime('%s', 'now') as int)),
        date_modified INTEGER NOT NULL DEFAULT (cast(strftime('%s', 'now') as int)),
        comment TEXT,
        date_updated INTEGER,
        number INTEGER NOT NULL DEFAULT 0,
        invalid_domains INTEGER NOT NULL DEFAULT 0,
        status INTEGER NOT NULL DEFAULT 0
);
CREATE TRIGGER tr_adlist_update AFTER UPDATE OF address,enabled,comment ON adlist
    BEGIN
      UPDATE adlist SET date_modified = (cast(strftime('%s', 'now') as int)) WHERE id = NEW.id;
    END;
CREATE TRIGGER tr_adlist_delete AFTER DELETE ON adlist
    BEGIN
      DELETE FROM adlist_by_group WHERE adlist_id = OLD.id;
    END;
CREATE TRIGGER tr_adlist_add AFTER INSERT ON adlist
    BEGIN
      INSERT INTO adlist_by_group (adlist_id, group_id) VALUES (NEW.id, 0);
    END;

pi@ph5a:~ $ pihole-FTL sqlite3 --header --column /etc/pihole/gravity.db "SELECT id,address FROM adlist ORDER BY id"
id  address
--  ---------------------------------------------------------------------
1   https://raw.githubusercontent.com/StevenBlack/hosts/master/hosts
3   https://dehakkelaar.nl/lists/cryptojacking_campaign.list.txt
4   https://gitlab.com/ZeroDot1/CoinBlockerLists/raw/master/list.txt
5   https://blocklist.cyberthreatcoalition.org/vetted/domain.txt
6   https://github.com/NCSC-NL/flubot/raw/main/dga/flubot_1136_202106.txt

[jfb]

@Palaress

Now I wanted to get rid of some (useless ones)

Delete the adlists you no longer want.

Then, in the terminal, use the following command to force a fresh rebuild of the gravity database.

pihole -g -f

[Palaress]

To clearify: I copied the lists from /etc/pihole/ to my computer to evaluate them with a self wrote script. Mostly I was counting the "unique" domains in a list, thus lists without (or with only few) unique domains are redundant and therefore not needed.

Problem was I then knew list.xy.raw.githubusercontent.com.domains was redundant, but I did not know which Adlist to delete in the web interface, because there where quite some list from github all named the same in /etc/pihole/ only different by the number xy.

pihole-FTL sqlite3 --header --column /etc/pihole/gravity.db "SELECT id,address FROM adlist ORDER BY id"

This was what I was searching for, to I can make a connection between a redundant file and the source URL to delete in the web GUI. Thank you [deHakkelaar] (I cant mention them, because my account is still restricted, but credit to them)

From the non-github source Adlist I can verify that the ID matches the "xy" in the filenames of /etc/pihole/. Perfect

[deHakkelaar]

@Palaress

From the non-github source Adlist I can verify that the ID matches the "xy" in the filenames of /etc/pihole/

I knew you would figure that out