command to extract pihole stats during a collect interval

piholestats

#!/bin/bash

PATH=$PATH:/bin:/usr/bin:/usr/local/bin
SQLITE=/usr/bin/sqlite3
FTLDB=/etc/pihole/pihole-FTL.db
LATEST=/tmp/latest
LASTREC=/tmp/lastrec
SELECTLASTREC="SELECT id FROM queries ORDER by id desc limit 1;"

#logger test
# get the latest record

if [[ ! -f "${LATEST}" ]]; then
  logger "Creating file ${LATEST}"
  touch ${LATEST}
fi
if [[ ! -w "${LATEST}" ]]; then
  logger "Can't write to file ${LATEST}"
  exit
fi
${SQLITE} ${FTLDB} "${SELECTLASTREC}" > ${LATEST} 
retval=$? 
if [[ $retval -ne "0" ]]; then
  logger "${SQLITE} exited with a non zero value: ${retval} try to get the last record" 
  exit ${retval}
fi
logger "latest file contains: $(cat /tmp/latest) value";
if [[ ! -f "${LASTREC}" ]]; then
  logger "Creating file ${LASTREC}"
  touch ${LASTREC}
  cp ${LATEST} ${LASTREC}
fi
if [[ ! -w "${LASTREC}" ]]; then
  logger "Can't write to file ${LASTREC}"
  exit
fi

# query the database for all records between lastrec and latest.
SELECTDELTA="select timestamp||substr(id,length(id)-2,3),type,status,domain,client,forward,id from queries where id > $(cat /tmp/lastrec) and id <= $(cat /tmp/latest);"
${SQLITE} ${FTLDB} "${SELECTDELTA}" 2>>/tmp/blah
retval=$? 
if [[ $retval -ne "0" ]]; then
  logger "${SQLITE} exited with a non zero value: ${retval}" 
  exit ${retval}
fi

# update lastrec with the value of latest
cp ${LATEST} ${LASTREC}