# Exporting PiHole Stats for use in Excel

**URL:** <https://discourse.pi-hole.net/t/exporting-pihole-stats-for-use-in-excel/23726>\
**Category:** Customizing Pi-hole\
**Created:** [September 18, 2019, 9:47pm UTC](https://discourse.pi-hole.net/t/exporting-pihole-stats-for-use-in-excel/23726 "2019-09-18T21:47:56Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![mikeyw64](https://discourse-cdn.pi-hole.net/letter_avatar_proxy/v4/letter/m/c4cdca/32.png) [@mikeyw64](https://discourse.pi-hole.net/u/mikeyw64)\
**Post date:** [September 18, 2019, 9:47pm UTC](https://discourse.pi-hole.net/t/exporting-pihole-stats-for-use-in-excel/23726/1 "2019-09-18T21:47:56Z")

</div>

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](https://docs.pi-hole.net/ftldns/database/#supported-status-types) 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

```auto
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](https://docs.pi-hole.net/ftldns/database/) docs is that the Network table isn't documented

---

<div class="post-metadata">

**Author:** ![mikeyw64](https://discourse-cdn.pi-hole.net/letter_avatar_proxy/v4/letter/m/c4cdca/32.png) [@mikeyw64](https://discourse.pi-hole.net/u/mikeyw64)\
**Post date:** [September 19, 2019, 8:02pm UTC](https://discourse.pi-hole.net/t/exporting-pihole-stats-for-use-in-excel/23726/2 "2019-09-19T20:02:15Z")

</div>

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

```auto
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

```auto
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)

```auto
<?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](https://discourse.pi-hole.net/uploads/default/original/2X/0/0f89342d680b1050d790b966c46c0dd093322091.png)

---

<div class="post-metadata">

**Author:** ![Bucking\_Horn](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/bucking_horn/32/18719_2.png) [@Bucking\_Horn](https://discourse.pi-hole.net/u/Bucking_Horn)\
**Post date:** [September 27, 2019, 9:08pm UTC](https://discourse.pi-hole.net/t/exporting-pihole-stats-for-use-in-excel/23726/3 "2019-09-27T21:08:16Z")

</div>

> [@mikeyw64](#):
>
> I’ve now got it so that I can get the status codes in text

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).

```auto
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 ...

```

---

<div class="post-metadata">

**Author:** ![mikeyw64](https://discourse-cdn.pi-hole.net/letter_avatar_proxy/v4/letter/m/c4cdca/32.png) [@mikeyw64](https://discourse.pi-hole.net/u/mikeyw64)\
**Post date:** [September 27, 2019, 10:03pm UTC](https://discourse.pi-hole.net/t/exporting-pihole-stats-for-use-in-excel/23726/4 "2019-09-27T22:03:03Z")

</div>

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