Sqlite3 to remove target domain from database

[graysky2]

How can adjust the sqlite3 call shown in this thread to remove all entries containing the target domain _dns.resolver.arpa irrespective of host IP calling it?

[jpgpi250]

in order to find the id of the domain:

SELECT id FROM "domain_by_id" WHERE domain = '_dns.resolver.arpa';

this will return the id of the domain in the database, a single number.

in order to list the entries, use

pihole-FTL sqlite3 "/etc/pihole/pihole-FTL.db" "select * from query_storage where domain = (select id from domain_by_id where domain = '_dns.resolver.arpa');"

this ill return all entries in the database for this domain, it can be a very long list, depending on your MAXDBDAYS setting.

it should return something like this, where the highlighted number matches the number of the first query...

in order to delete the entries, replace the select * with delete
you probably need to run the delete command with sudo, e.g.

sudo pihole-FTL sqlite3 "/etc/pihole/pihole-FTL.db" "delete from query_storage where domain = (select id from domain_by_id where domain = '_dns.resolver.arpa');"

• you might want to make a backup of the FTL database, before you start
• you might want to vacuum the database, when finished.

[graysky2]

Thank you!