Deleting a client from the database

General Customizing Pi-hole
1

This post contains scripts, or links to scripts, that are untested by the Pi-hole team. We encourage users to be creative, but we are unable to support any potential problems caused by running these scripts

Have you ever wondered how to delete a client from Pi-hole?
Just follow these simple steps:

FOR EXPERTS

  1. sqlite3 /etc/pihole/pihole-FTL.db
  2. sqlite> DELETE from queries WHERE client = 'clientIP';
  3. Verify that you deleted the client
    sqlite> SELECT * FROM queries WHERE client = 'clientIP';
    There should be little to no queries as a result of the above command.
  4. Exit SQLite: sqlite> .exit

PLEASE NOTE:
If you are logged in as the root user, such as with sudo -su, then you do not need to enter sudo before every command. If you are a regular user, then you must use the sudo before every command and enter your password (if set).

  1. Using a text editor of your choice, create a new file in /usr/local/bin. Give it a name, such as rmclient
cd /usr/local/bin
sudo nano rmclient
  1. Copy and paste the following code below into your file:
#!/bin/bash
# rmclient
## Set Global Variables
db="/etc/pihole/pihole-FTL.db"
log="/var/log/pihole.log"

# Pre Run Checks
if [[ $(id -u) -ne 0 ]]; then 
    echo "Please run as root"
    exit 1
elif [ "$#" -lt 1 ]; then
    exit 1
elif [ "$#" -gt 1 ]; then
    echo "ONLY ONE ARGUMENT PERMITTED"
    exit 1
elif [ ! -f "$db" ]; then
    echo "NO DATABASE"
    exit 1
fi

# Verify the IP Address
ip="$1"
if [[ "$ip" =~ ^(([1-9]?[0-9]|1[0-9][0-9]|2([0-4][0-9]|5[0-5]))\.){3}([1-9]?[0-9]|1[0-9][0-9]|2([0-4][0-9]|5[0-5]))$ ]]; then
    # Delete the client from the database
    echo "DELETING CLIENT FROM DATABASE"
    sqlite3 "$db" "DELETE from queries WHERE client = '$ip';" && sed -i '/"$ip"/d' "$log"
    if [ $? = 0 ]; then
        echo "CLIENT SUCCESSFULLY DELETED"
        #service pihole-FTL reload
    fi
else
    echo "INVALID IP ADDRESS"
    exit 1
fi
  1. Save the file (using nano):
[CTL]+[X]
[Y]
[ENTER]
  1. Give the file execute permissions
    sudo chmod a+x rmclient
  2. You're All Done! You can now delete clients from the Pi-hole database. Look at the example below:
    sudo rmclient 127.0.0.1
    5B. If you want to see instant results, you will have to reload FTL. Enter the command below:
    sudo service pihole-FTL reload

EXTRAS

  • PRAGMA table_info('queries')
  • Get domains for a specific client: SELECT domain FROM queries WHERE client = '192.168.1.7';

SOURCE CODE: Click Here

4 Likes
2

This deletes the client from the long term database, but client transactions will still exist in the pihole logs (unless the logs are disabled).

In /var/log

pihole.log
pihole.log.1
pihole.log.3.gz
pihole.log.4.gz
pihole.log.5.gz
4

I added support for removing client queries from the main log. I don't know what pihole.log.1 is but I might need to edit it to remove queries from that one too. The other ones are compressed, and it would be hard on the SD card (Pi only) uncompressing and re-compressing those logs, so they can just be left alone.

UPDATE:
pihole.log.1 only includes information from the previous day, as at the bottom, it stops at 00:00 the next day. So it does not matter. As stated before, the logs are rotated, so eventually they will disappear altogether as time passes if I'm correct.

5

The pihole.log is rotated nightly at midnight. The previous 5 days are stored, with the oldest being removed each night. So, if you delete a client, in 6 days it will not appear in the pihole logs.

If you delete a client and want to get rid of it immediately from the pihole logs, then wait until the next day and delete all the pihole logs 1 through 5. No record of the client remains in the logs.

2 Likes
6

Hey, while I know I'm late to the party, THANK YOU for taking the time to post this. I've been running pihole for a while but today configured it as my DHCP server, so I wanted to dump all the historic queries from my router but retain everything else. I shutdown pihole-ftl, did the sqlite delete. I was a touch nervous because the delete took a few minutes but I was patient and it completed. Started up pihole-ftl and was happy to see the improved stats.

7

For me it says that I cannot delete the entries because queries is a view.
Any solutions?

8

This topic is from 2018. It's outdated.

The current database structure (2022) is different. This script won't work.

9

Here is the documentation on the existing query database structure:

https://docs.pi-hole.net/database/ftl/

10

See here Using sqlite3 to remove entries for pihole-FTL.db is not longer working - #8 by ontherails

11

Thank you!