Help with a SQL Query - Client Hostname

[vinifa]

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:

1. Through /var/log/pihole.log
   or
2. 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]

Have you been through the documentation covering the database schemas? Redirecting...

[vinifa]

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]

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:

api_db.php

You can adapt the code for your needs.

[Bucking_Horn]

@vinifa

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

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.