Help with a SQL Query - Client Hostname

vinifa · November 26, 2024, 2:34am

Good evening, I’m new here and have been using Pi-hole for a few days in my production environment. We replaced pfBlockerNG with it because the latter had issues with unbound functioning in pfSense, which I believe might have been due to the number of requests or some other unidentified bug by Netgate.

Anyway, I’d like some help regarding the logs, as I noticed I have two options:

Through /var/log/pihole.log
or
A SQL query on the database located at /etc/pihole/pihole-FTL.db.

The first option doesn’t work well for me because I can’t retrieve my hostname, only the IP address and link.

I believe the second option will be more viable since I want to extract the log in the same way the system displays it in the "Query log" menu (date, domain, and client with resolved hostname). The problem is that I couldn’t figure out the SQL query that would help me achieve this.

As I use Graylog to store my data, my plan is to create a shell script that performs this SQL query and writes the results to a file periodically (once a day, for example). Then, I’ll take this “processed” file and send it to my remote log server using syslog-ng.

Anyway, I thank you all in advance!

DanSchaper · November 26, 2024, 2:57am

Have you been through the documentation covering the database schemas? Query database - Pi-hole documentation

vinifa · November 26, 2024, 3:05am

Yes, I’ve seen that, but I don’t understand how to make the client (IP) get the resolved hostname.

I even explored the database and viewed the tables, but I couldn’t create a query that brings the information as it is shown in the "Query log" menu.

rdwebdesign · November 26, 2024, 3:27am

The data in the Query Log page is retrieved via API call to pihole-FTL.
The API call returns data from the last 24h, from an in-memory database (you can't access this data directly).

On the other hand, the data stored into the database is shown in the web interface when you access the Long-term Data > Query Log.

Note:
All queries are stored into the database using only the IP (not hostname).
The hostnames (when available) are stored in a different table client_by_id, but not every IP has a hostname and some IPs have more than one hostname.

The web interface uses this SQL code to return the Long-term Data > Query Log data:

github.com

pi-hole/web/blob/5c8945c81c98f6f17e5c138ffbfdcb0831e5a062/api_db.php#L66-L78


      
          // Use table "query_storage"
          //   - replace domain ID with domain
          //   - replace client ID with client name
          //   - replace forward ID with forward destination
          $dbquery = 'SELECT timestamp, type,';
          $dbquery .= " CASE typeof(domain) WHEN 'integer' THEN (SELECT domain FROM domain_by_id d WHERE d.id = q.domain) ELSE domain END domain,";
          $dbquery .= " CASE typeof(client) WHEN 'integer' THEN (";
          $dbquery .= "   SELECT CASE TRIM(name) WHEN '' THEN c.ip ELSE c.name END name FROM client_by_id c WHERE c.id = q.client";
          $dbquery .= ' ) ELSE client END client,';
          $dbquery .= " CASE typeof(forward) WHEN 'integer' THEN (SELECT forward FROM forward_by_id f WHERE f.id = q.forward) ELSE forward END forward,";
          $dbquery .= ' status, reply_type, reply_time, dnssec';
          $dbquery .= ' FROM query_storage q';
          $dbquery .= ' WHERE timestamp >= :from AND timestamp <= :until ';

You can adapt the code for your needs.

Bucking_Horn · November 26, 2024, 9:12am

I'd recommend to stick with the IP, or use hostnames only as additional information.

The DNS protocol exclusively uses IPs to identify a requestor. Pi-hole puts in additional effort to associate IPs with a name to display in its dashboard, but that information is derived - it isn't available from the original DNS request (which is why it isn't present in the logged DNS requests).

If you use hostnames only instead, you'll lose information.
A device with a name may have multiple network interfaces, and each network interface may carry multiple IPv4 and IPv6 addresses, and especially IPv6 addresses can be expected to change over time.