How to Maintain Pi-Hole History Data

[FunkyBuddha]

Problem: Pi-Hole generates over one million rows of data per week which slows down performance if not remediated weekly.

Objectives:

• Maintain operational efficiency
• Keep History
• Automate

Approach:

• Schedule weekly jobs to copy Pi-Hole DBs and truncate Pi-Hole's FTL DB (this resets everything to zero rows).
• Execute SQL to aggregate current week's history and append it to prior historical data (see sample code below).

Sqlite Code:

Quote

attach 'R:\pihole-FTL.db' as db1; 
attach 'R:\gravity.db' as db2; 
attach 'R:\pihole-hist.db' as db3;  
attach 'R:\temp.db' as db4;

;
drop table if exists db4.pi_hole_hist
;
CREATE TABLE db4.pi_hole_xtk(
  datestamp  VARCHAR(10),
  tymestamp  VARCHAR(20),
  timestamp INT,
  domain VARCHAR(50),
  ip     VARCHAR(15),
  name   VARCHAR(50),
  hwaddr VARCHAR(10),
  macVendor VARCHAR(20),
  numQueries INT,
  firstSeen VARCHAR(10),
  lastQuery VARCHAR(10),
  blocked   VARCHAR(10),
  adlist_id INT,
  cntQueries INT
);
insert into db4.pi_hole_xtk
SELECT distinct strftime('%m-%d-%Y', datetime(timestamp, 'unixepoch')) as datestamp
    ,datetime(timestamp, 'unixepoch') as tymestamp
    ,timestamp
    ,d.domain
    ,c.ip
    ,e.hwaddr
    ,e.macVendor
    ,e.numQueries
    ,strftime('%m-%d-%Y', datetime(firstSeen, 'unixepoch')) as firstSeen
    ,strftime('%m-%d-%Y', datetime(lastQuery, 'unixepoch')) as lastQuery
    ,case
       when f.domain is NULL then 'N'
       else 'Y'
    end as blocked
    ,case
       when f.domain is NULL then NULL
       else f.adlist_id
    end as adlist_id
    ,0 as cntQueries
FROM db1.query_storage q
join
db1.domain_by_id d
on d.id = q.domain
left join
db1.client_by_id c 
on c.id = q.client
left join
db1.network_addresses d
on c.ip=d.ip
left join
db1.network e 
on d.network_id=e.id
left join
(select domain, group_concat(adlist_id) as adlist_id from db2.gravity group by 1) f 
on d.domain=f.domain
;
CREATE TABLE db4.pi_hole_hist(
  datestamp  VARCHAR(10),
  tymestamp  VARCHAR(20),
  timestamp INT,
  domain VARCHAR(50),
  ip     VARCHAR(15),
  name   VARCHAR(50),
  hwaddr VARCHAR(10),
  macVendor VARCHAR(20),
  numQueries INT,
  firstSeen VARCHAR(10),
  lastQuery VARCHAR(10),
  blocked   VARCHAR(10),
  adlist_id INT,
  cntQueries INT
);
insert into db4.pi_hole_hist
select * from
    (
    select * from db3.pi_hole_hist
    where datetime(timestamp, 'unixepoch') >= date('now','-720 day')
    )
union
select * from
    (
    select datestamp
           ,max(tymestamp)    as tymestamp
           ,max(timestamp)    as timestamp
           ,domain
           ,ip
           ,name
           ,hwaddr
           ,macVendor
           ,sum(numQueries) as numQueries
           ,min(firstSeen)
           ,max(lastQuery)
           ,blocked
           ,adlist_id
           ,count(distinct(timestamp)) as cntQueries
    from db4.pi_hole_xtk a
    join
    (select max(timestamp) as max_timestamp from db3.pi_hole_hist) b
    where timestamp > max_timestamp
    group by datestamp, domain, ip, name, hwaddr, macVendor, blocked, adlist_id
    )
;
drop table if exists db3.pi_hole_hist
;
CREATE TABLE db3.pi_hole_hist(
  datestamp  VARCHAR(10),
  tymestamp  VARCHAR(20),
  timestamp INT,
  domain VARCHAR(50),
  ip     VARCHAR(15),
  name   VARCHAR(50),
  hwaddr VARCHAR(10),
  macVendor VARCHAR(20),
  numQueries INT,
  firstSeen VARCHAR(10),
  lastQuery VARCHAR(10),
  blocked   VARCHAR(10),
  adlist_id INT,
  cntQueries INT
);
insert into db3.pi_hole_hist
select datestamp
       ,max(tymestamp)    as tymestamp
       ,max(timestamp)    as timestamp
       ,domain
       ,ip
       ,name
       ,hwaddr
       ,macVendor
       ,sum(numQueries) as numQueries
       ,min(firstSeen)
       ,max(lastQuery)
       ,blocked
       ,adlist_id
       ,sum(cntQueries) as cntQueries
from db4.pi_hole_hist
where
    (
     Domain not like '%.gstatic.com%' and
     Domain not like '%.ntp.org%' and
     Domain not like '%.nist.gov%' and
     Domain not like '%plex.direct%' and
     Domain not like '%wpad.local%' and
     Domain not like '%.localdomain' and
     Domain not like '%.local' and
     Domain not like '%.in-addr.arpa'
    )
group by datestamp, domain, ip, name, hwaddr, macVendor, blocked, adlist_id
order by 1
;
drop table if exists db4.pi_hole_xtk
;
drop table if exists db4.pi_hole_hist
;
vacuum
;
select count(*) as totrec from db3.pi_hole_hist
;