[API] incremental counters for statistical retrieval

Feature Requests
1

I've had a look at the API, and I see it provides access to "cooked" numerical values (i.e.summaryRaw).

Would there be any way to provide us with the raw values? Incremental counters that aren't reset/stripped per 24 hour, and aren't divided into 10 minute segments. Basically just counters that show how many of each action that have been performed since the the log files were initialized. This way it's super easy to access these data in external software (i.e. PRTG).

2

The summary values are for the last 24 hours, and beyond that the only place you'd find the data is if you parsed the log files yourself or connected the the FTL database file (/etc/pihole/pihole-FTL.db). FTL does not store anything past the last 24 hours, except in the database.

For the raw values of the last 24 hours though, most of the data is available through the API. What kind of data were you looking for?

3

Well, I was hoping for incremental life-time counters for the same counters as shown in "summaryRaw". Just that it shows total numbers (since the logs were created/reset), and not just for the last 24 hours.

Example output (don't know what they should be named, to be honest. It doesn't really matter):

dns_queries_lifetime 2095468
ads_blocked_lifetime 485156
ads_percentage_lifetime 23.1526
unique_domains_lifetime 115422
queries_forwarded_lifetime 805460
queries_cached_lifetime 805473

Statistical/monitoring software rely on incremental counters to calculate their graphs (by using delta values). That's why having 24 hour values doesn't work if you want an external monitoring program to do this job.

4

For those kind of life-time counts, you'd best look at the SQLite database: /etc/pihole/pihole-FTL.db

In the development branch (pihole checkout dev) you can see stats generated from the database. You can read how it gets the stats here:
https://github.com/pi-hole/AdminLTE/blob/devel/api_db.php

5

Aha, interesting. I've done some WQL on WIndows WMI, so I should be able to figure it out I guess.

Thanks :slight_smile:

6

Ok, I have a working solution here that provides me with most of the things I need over SNMP. Figured I'd share in case someone else comes across the same problem looking for a solution.

This requires that SNMP be installed with a working snmpd service running on your Pi-hole system. You also need the command line interface for SQLite 3.
On my Raspberry Pi 3 running Raspbian I just used the "Add/Remove Software" feature and installed "snmp-5.7.3", "snmpd-5.7.3", and "sqlite3-3.16.2".

On my Windows server I already have Net-SNMP.

Edit the file "/etc/snmp/snmpd.conf"

sudo nano /etc/snmp/snmpd.conf

...and add these entries:

extend pihole-permitted   /usr/bin/sqlite3 -init /etc/pihole/init.sql /etc/pihole/pihole-FTL.db  'SELECT count(timestamp) FROM queries WHERE (status == 2 OR status == 3 OR status == 0)'
extend pihole-forwarded   /usr/bin/sqlite3 -init /etc/pihole/init.sql /etc/pihole/pihole-FTL.db  'SELECT count(timestamp) FROM queries WHERE (status == 2)'
extend pihole-cached      /usr/bin/sqlite3 -init /etc/pihole/init.sql /etc/pihole/pihole-FTL.db  'SELECT count(timestamp) FROM queries WHERE (status == 3)'
extend pihole-unknown     /usr/bin/sqlite3 -init /etc/pihole/init.sql /etc/pihole/pihole-FTL.db  'SELECT count(timestamp) FROM queries WHERE (status == 0)'
extend pihole-piholed     /usr/bin/sqlite3 -init /etc/pihole/init.sql /etc/pihole/pihole-FTL.db  'SELECT count(timestamp) FROM queries WHERE (status == 1 OR status == 4 OR status == 5)'
extend pihole-blacklisted /usr/bin/sqlite3 -init /etc/pihole/init.sql /etc/pihole/pihole-FTL.db  'SELECT count(timestamp) FROM queries WHERE (status == 4 OR status == 5)'
extend pihole-queries     /usr/bin/sqlite3 -init /etc/pihole/init.sql /etc/pihole/pihole-FTL.db  'SELECT count(timestamp) FROM queries'
extend pihole-oldest      /usr/bin/sqlite3 -init /etc/pihole/init.sql /etc/pihole/pihole-FTL.db  'SELECT MIN(timestamp) FROM queries'

Whenever you edit this conf file, you need to restart the snmpd service for the changes to take effect:

sudo service snmpd restart

You also need to create the file "/etc/pihole/init.sql"

sudo nano /etc/pihole/init.sql

...and add the following command to it to avoid "Error: database is locked" messages:

.timeout 10000

Example: to get the results from your Pi-hole system, you could do it in a manner like below. It retrieves number of permitted (forwarded+cached) queries:

snmpget -v 2c -c public 192.168.0.3 1.3.6.1.4.1.8072.1.3.2.3.1.1.16.112.105.104.111.108.101.45.112.101.114.109.105.116.116.101.100

Output will look something like this, and is easy to parse for monitoring software like PRTG:

NET-SNMP-AGENT-MIB::nsExtensions.2.3.1.1.16.112.105.104.111.108.101.45.112.101.114.109.105.116.116.101.100 = STRING: "18369"
7

Hm, ok so I've run into a new problem. I keep getting "Error: database is locked" when using the sqlite3 command line. In the beginning the file will "unlock" after a few seconds, and you can get around this by using the sqlite .timeout command. But eventually after a few hours of system uptime the "pihole-FTL.db" file ends up being perpetually locked, and only way to unlock it is to reboot the system. After a few hours of uptime, the db file ends up locked again.

So, my initial request stands. I really hope that providing these numbers through the API would be possible in a future release.

8

The next release will include an endpoint for getting data from the database.

1 Like