Remove specified DOMAINS or CLIENTS from the pi-hole logs and long term data

rm_from_db.sh

#!/bin/bash


usage() {
  echo "***************************************************"
  echo "Valid commands are: 'remove' OR 'search'"
  echo ""
  echo "'remove' accepts the parameters: 'domain' OR 'client'"
  echo -e "  'domain' accepts a domain's name (list) in the form:\n     google.it\n     it.somesite.com\n     %anothersite.com (wildcard for subdomains)"
  echo -e "  'client' accepts: a client IP (list) in the form:\n     192.168.1.xxx"
  echo ""
  echo "'search' accepts a 'SUB-STRING' to be searched in the db domains list"
  echo "***************************************************"
}


# Set Global Variables
db="/etc/pihole/pihole-FTL.db"
log="/var/log/pihole.log"

arr="[\xe2\x9d\xaf\xe2\x9d\xaf]"
yep="[\xe2\x9c\x85]"
nop="[\xe2\x9d\x8c]"
noe="[\xe2\x9b\x94]"


# Initial checks
if [[ $(id -u) -ne 0 ]]; then 
    echo -e "$noe Please run as root"
    exit 1
elif [ "$#" -lt 2 ]; then
    usage
    exit 2
elif [ ! -f "$db" ]; then
    echo -e "$noe NO DATABASE ***"
    exit 3
fi


stopFtl() {
    echo -e "$arr Stopping pihole-FTL ..."
    if service pihole-FTL stop; then
      echo -e "$yep DONE"
    else
      echo -e "$nop ERROR"
      exit 4
    fi
}


reloadFtl() {
    echo -e "$arr Restarting pihole-FTL ..."
    if service pihole-FTL restart; then
      echo -e "$yep DONE"
    else
      echo -e "$nop ERROR"
    fi
}


removeDomain() {
  # Loop through parameters, starting from the 2nd one
  for ((i=1; i<=$#; i++)); do

    # Verify the domain's name and proceed
    name="${!i}"

    if echo "$name" | grep -Pq '(?=^.{5,254}$)(^(?:(?!\d+\.)[a-zA-Z0-9_\-%]{1,63}\.?)+(?:[a-zA-Z]{2,})$)'; then

      stopFtl

      # Delete the domain from the database
      echo -e "$arr DELETING DOMAIN "$name" FROM DATABASE"
	sqlite3 "$db" "delete from query_storage where domain in (select id from domain_by_id where domain like '$name');" && sed -i "/$name/d" "$log"

        if [ $? = 0 ]; then
            echo -e "$yep DOMAIN "$name" SUCCESSFULLY DELETED"
        else
            echo -e "$nop DOMAIN "$name" NOT DELETED"
        fi

	reloadFtl
    else
        echo -e "$nop "$name" : INVALID DOMAIN NAME"
        exit 3
    fi

  done
}


removeClient() {
  # Loop through parameters, starting from the 2nd one
  for ((i=1; i<=$#; i++)); do

    # Verify the IP Address and proceed
    ip="${!i}"

    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

        stopFtl

        # Delete the client from the database
        echo -e "$arr DELETING "$ip" CLIENT FROM DATABASE"
        sqlite3 "$db" "DELETE from query_storage WHERE client = '$ip';" && sed -i "/$ip/d" "$log"

        if [ $? = 0 ]; then
            echo -e "$yep CLIENT "$ip" SUCCESSFULLY DELETED"
        else
            echo -e "$nop CLIENT "$ip" NOT DELETED"
        fi

	reloadFtl
    else
        echo -e "$nop "$ip" : INVALID IP ADDRESS"
        exit 3
    fi

  done
}


searchDomain() {
  echo -e "$arr SEARCHING for \""$1"\" into DATABASE"
  ndom=`sqlite3 "$db" "SELECT * FROM query_storage where domain in (select id from domain_by_id where domain like '%$1%');" | wc -l`
  echo -e "$arr Found "$ndom" domains."
}



##################
## MAIN SCRIPT ###
##################

if [ "$1" == "search" ]; then
  searchDomain $2
elif [ "$1" == "remove" ]; then
  if [ "$2" == "domain" ]; then
    removeDomain $3
  elif [ "$2" == "client" ]; then
    removeClient $3
  else
    usage
  fi
else
  usage
fi