Exporting PiHole Stats for use in Excel

General Customizing Pi-hole
1

This is the way I do it.

The example below exports the date timestamp in human readable format, the domain that was looked up and the status code for all queries from a specific client .

I am then running samba so I can retrieve the file from my laptop and open in excel so I can run it through a pivot table.

Obviously this can be expanded upon as required

pi@Heimdall:~ $ sqlite3 "/etc/pihole/pihole-FTL.db"
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> .headers on
sqlite> .mode csv
sqlite> .output GB010225.csv
sqlite> select datetime(queries.timestamp,'unixepoch','localtime'),queries.domain,queries.status from queries inner join network on queries.client = network.ip where network.name = 'gb010225.lan';
sqlite> .quit
pi@Heimdall:~ $ ls -al | grep GB010225
-rw-r--r-- 1 pi   pi   2355957 Sep 18 22:39 GB010225.csv

NB one thing I've noticed at the Long Term Database docs is that the Network table isn't documented

1 Like
2

so after some tweaking I've now got it so that I can get the status codes in text rather than as a number

select datetime(timestamp,'unixepoch','localtime') as timestamp,queries.domain,status.status from queries inner join network on queries.client = network.ip inner join status on queries.status = status.statuscode where network.name = 'gb010225.lan';

This did involve creating and populating a new table as follows

pi@Heimdall:/var/www/html $ sudo sqlite3 "/etc/pihole/pihole-FTL.db"
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> .headers on
sqlite> .tables
counters  ftl       network   queries
sqlite> CREATE TABLE status(id INTEGER PRIMARY KEY AUTOINCREMENT, statuscode INTEGER NOT NULL, status TEXT NOT NULL);
sqlite> .tables
counters  ftl       network   queries   status
sqlite> Insert into status(statuscode,status) Values (0,'Unknown');
sqlite> Insert into status(statuscode,status) Values (1,'Blocked : Gravity');
sqlite> Insert into status(statuscode,status) Values (2,'Permitted : Forwarded');
sqlite> Insert into status(statuscode,status) Values (3,'Permitted : Cache');
sqlite> Insert into status(statuscode,status) Values (4,'Blocked : Wildcard');
sqlite> Insert into status(statuscode,status) Values (5,'Blocked : Blacklist');
sqlite> Insert into status(statuscode,status) Values (6,'Blocked : Upstream (known blocking page IP)');
sqlite> Insert into status(statuscode,status) Values (7,'Blocked : Upstream (0.0.0.0 or ::)');
sqlite> Insert into status(statuscode,status) Values (8,'Blocked : Upstream (NXDOMAIN with RSA bit unset)');
sqlite> select * from status;
id|statuscode|status
1|0|Unknown
2|1|Blocked : Gravity
3|2|Permitted : Forwarded
4|3|Permitted : Cache
5|4|Blocked : Wildcard
6|5|Blocked : Blacklist
7|6|Blocked : Upstream (known blocking page IP)
8|7|Blocked : Upstream (0.0.0.0 or ::)

The other thing I've done for now is drop a simple php page in var/www/html . Again bit more work and you could have an input box there (I'm rusty and relearning)

<?php

$db = new SQLite3('/etc/pihole/pihole-FTL.db');
$res = $db->query('select datetime(queries.timestamp,"unixepoch","localtime") as timestamp,queries.domain,status.status from queries inner join network on queries.client = network.ip  inner join status on queries.status = status.statusc$

while ($row = $res->fetchArray()) {
    echo "{$row['timestamp']}, {$row['domain']}, {$row['status']} <br>";
}

example
example893×205 9.58 KB

3

That's a problem to tackle with SQLs CASE statement.
Something like the following should allow you to achieve it without resorting to create tables in a 3rd-party database. You might have to adjust it in places for SQLite, as I am not familiar with it (e.g. I don't know which characters that would be using for string literals).

SELECT 
    ...
    CASE status 
         WHEN  0 THEN 'unknown'
               1 THEN 'blocked : gravity', 
               2 THEN 'permitted : forwarded', 
               3 THEN 'permitted : cached',
               4 THEN 'blocked : wildcard',
               5 THEN 'blocked : blacklist',
               6 THEN 'blocked : upstream known blocking page IP',
               7 THEN 'blocked : upstream 0.0.0.0 or ::',
               8 THEN 'blocked : upstream NXDOMAIN with RSA bit unset',
         ELSE '<UNDEFINED>'
    END as status_txt
FROM ...
4

I did consider that but then thought "meh" do it the easy way ;o)