Querying the Pi-hole Databases

I'll show a couple of simple SQL queries you can run to get some basic information from your pihole databases. At the end, I'll present a question I thought of regarding my blocklists and my thought process towards making a query to answer that question. For privacy reasons, I will not show any example outputs produced by the SQL queries.

Introduction

Pihole has two sqlite databases:

• /etc/pihole/pihole-FTL.db: the query database. All DNS queries made by your clients get logged here.
• /etc/pihole/gravity.db: the "gravity" database, where pihole stores your adlists (both block and allow lists), domains, regex filters, and more.

Using these databases, we can gain insights to our DNS queries similar to pihole's web interface and beyond what the dashboard provides.

How to query the databases

If you want to run queries on your own pihole databases, the simplest way to do so is to open your databases using pihole's embedded sqlite3 shell. You can also use your distro's sqlite3 package if you already have it installed.

However, the pihole databases are owned by the pihole user and group, with permission set to 640. Opening the database as a regular user might yield an unable to open database file error. To get read access to the databases, we can add our user to the pihole group.

sudo usermod myuser -aG pihole

Make sure to relogin to your session, and check that you are in the pihole group with groups.

With that, we can open the databases with pihole's embedded sqlite3 shell with

pihole-FTL sqlite3 /etc/pihole/pihole-FTL.db

If you want to be extra sure you're only reading from the database, add the -readonly flag. If you want formatted output, add -box for tables with unicode or -table for tables with ASCII to the command line.

pihole-FTL sqlite3 -readonly -box /etc/pihole/pihole-FTL.db

Making a Copy of the Databases (Optional)

Making a copy of the databases is optional, but it will allow you to freeze the databases in time. To make a copy of the databases, we can run the following command:

pihole-FTL sqlite3 /etc/pihole/pihole-FTL.db ".backup /home/youruser/ftl.db"
pihole-FTL sqlite3 /etc/pihole/gravity.db ".backup /home/youruser/gravity.db"

Remember the databases are owned by the pihole user. So either the command should be run as the root user or pihole user, or add your user to the pihole group.

Running the queries

Some of the queries can get very long. If you don't want to type or paste the comamnds directly into the shell, you can place the query in a text file, for example query.sql. Then you can run the below command to send the query to the database:

pihole-FTL sqlite3 -readonly -box /etc/pihole/pihole-FTL.db ".read ./query.sql"

With that, here are some simple queries.

Queries to mimic the pihole dashboard statistics

Below are some queries to mimic some of the information given on the dashboard. Some of the queries will be slightly off since I do not know exactly how pihole calculates their numbers (specifically total queries and queries blocked), but the queries still provide interesting info. Each code block will contain a comment noting what database to open.

Total Queries

Total queries in last 24 hours:

-- /etc/pihole/pihole-FTL.db
select count(*) as "Total Queries"
from queries
where timestamp > (select unixepoch('now', '-1 day'));

If you want 24 hours since the last DNS query (maybe you made an offline copy of the database), find the max of the timestamp column, then subtract 86400 seconds to get the appropriate threshold.

-- /etc/pihole/pihole-FTL.db
select count(*) as "Total Queries"
from queries
where timestamp > (select max(timestamp) from queries) - 86400;

Total/Percentage Blocked

Number of blocked queries from gravity, exact, regex, and special in last 24 hours (does not include blocked status codes 6, 7, 8, 15, 18):

-- /etc/pihole/pihole-FTL.db
select count(*) as "Queries Blocked"
from queries
where
  timestamp > (select unixepoch('now', '-1 day'))
  and status in (1, 4, 5, 9, 10, 11, 16);

As a percentage:

-- /etc/pihole/pihole-FTL.db
with t as (
  select count(*) as total
  from queries
  where timestamp > (select unixepoch('now', '-1 day'))
)

select
  cast(round(count(*) * 100.0 / t.total, 2) as text)
  || '%' as "Percentage Blocked"
from queries as q, t
where
  q.timestamp > (select unixepoch('now', '-1 day'))
  and q.status in (1, 4, 5, 9, 10, 11, 16);

Domains on List

To get the total number of distinct domains in your block lists:

-- /etc/pihole/gravity.db
select count(distinct domain) as "Domains on List" from vw_gravity

That number should exactly match what is reported on the web interface. Note that it does not count domains outside the lists (the ones listed in pi.hole/admin/groups/domains).

Most Blocked Domains

Top 10 most blocked domains from gravity list in the last 24 hours:

-- /etc/pihole/pihole-FTL.db
select
  domain,
  count(*) as hits
from queries
where
  status in (1, 4, 5, 9, 10, 11, 16)
  and timestamp > (select unixepoch('now', '-1 day'))
group by domain
order by hits desc
limit 10;

Top Clients (Total)

Top 10 clients with the most requests:

-- /etc/pihole/pihole-FTL.db
select
  client,
  count(*) as requests
from queries
where timestamp > (select unixepoch('now', '-1 day'))
group by client
order by requests desc
limit 10;

Top Clients (Blocked Only)

Top 10 clients with the most blocked requests:

-- /etc/pihole/pihole-FTL.db
select
  client,
  count(*) as requests
from queries
where
  status in (1, 4, 5, 9, 10, 11, 16)
  and timestamp > (select unixepoch('now', '-1 day'))
group by client
order by requests desc
limit 10;

Other Queries

Get number of domains in each blocklist

Count the number of domains in each list

-- /etc/pihole/gravity.db
select
  g.adlist_id,
  a.address,
  count(*) as num_domains
from gravity as g
left join adlist as a on g.adlist_id = a.id
group by g.adlist_id

If the blocklist URLs are too long, run the following before running the SQL query:

.mode box --wrap 60 --quote;

Finding the blocklists with the most blocked queries

My motivation for writing this article came from wondering which blocklists were blocking the most queries. Here is the basic idea of the query:

1. Open the pihole-FTL.db database, and attach the gravity.db database with the attach keyword.
2. Build a temporary table called g that contains only unique domains, what blocklist they belong to, and the id of the list from the gravity database using a CTE. For simplicity, we just use the distinct keyword, however we could write code to have finer control over which blocklist to associate the domain with (like using min(id) with a group by).
3. Inner join the queries table with g on the domain.
4. Filter for only queries that were blocked by the gravity list. Optionally restrict the time interval to last 24 hours or any other criteria.
5. Group the data by the blocklist id, aggregate it with the count function, and sort it by that value in descending order.

Here's the code I wrote:

-- /etc/pihole/pihole-FTL.db
attach database '/etc/pihole/gravity.db' as gdb;

with g as (
  select distinct
    gg.domain,
    gg.adlist_id,
    ga.address
  from gdb.gravity as gg
  left join gdb.adlist as ga on gg.adlist_id = ga.id
)

select
  g.adlist_id,
  g.address,
  count(*) as num_blocked
from queries as q
inner join g
  on q.domain = g.domain
where
  q.status in (1, 9)
  and q.timestamp > (select unixepoch('now', '-1 day'))
group by g.adlist_id
order by num_blocked desc;

If you run that, you might find that some of your blocklists rarely get hit, if at all. That might be useful information if you ever want to prune your list of blocklists.

One thing to note is the use of inner join in the main select statement to restrict our interest to domains that are in the gravity list. If you or your pihole updated your gravity list (which pihole does every Sunday), you might have blocked domains in the past which are not in the domain list anymore. If you wish to include those domains in the result, replace the inner join with left join. Note that sqlite will print null values as an empty string. If you want to change the value to something else, add the following to the sqlite3 command line: -nullvalue '(null)'.

Miscellaneous

By default, pihole only keeps the last 91 days of queries in the query database. If you want to modify that value, modify the database.maxDBdays value by going to Expert Settings -> Database.

If you want a GUI to open the sqlite databases, check out sqlitebrowser.

Resources

For documentation:

• https://docs.pi-hole.net/database/query-database/
• https://docs.pi-hole.net/database/domain-database/

Some resources for learning basic SQL:

• https://sqlbolt.com/
• https://www.sql-practice.com/

I used SQLFluff to format and lint the queries.