Redirect output of pihole-FTL cache dump to database

Currently, the result of the following commands dump the content of the cache into the pihole log.

pi@raspberrypi:~ $ ps -e | grep pihole-FTL
 2859 ?        00:00:14 pihole-FTL
pi@raspberrypi:~ $ sudo kill -10 2859

This is the SIGUSR1 signal, see Linux signal man for values, see the dnsmasq man for an explanation, quote:

Enable a full cache dump on receipt of SIGUSR1

The (partial) result, currently in the pihole log:

Oct  6 13:21:56 dnsmasq[2859]: geoffreyhand.co.uk             0.0.0.0                                  4F I   H
Oct  6 13:21:56 dnsmasq[2859]: explosiondigital.com.ve        0.0.0.0                                  4F I   H
Oct  6 13:21:56 dnsmasq[2859]: 8199wnsr.com                   0.0.0.0                                  4F I   H
Oct  6 13:21:56 dnsmasq[2859]: www.alarm-error-q3zdam25n.stre 0.0.0.0                                  4F I   H
Oct  6 13:21:56 dnsmasq[2859]: gmviagens.com.br                                                         F I   H
Oct  6 13:21:56 dnsmasq[2859]: www.lotusapps.net              0.0.0.0                                  4F I   H
Oct  6 13:21:56 dnsmasq[2859]: ifasthost.eu                                                             F I   H

feature request: redirect the output of the cache dump to a database (cachedump.sql) to allow for sqlite3 queries on the cache.

I’m using phpliteadmin, see old topic here, updated installation instructions here (chapter 20 / section 3 - new version and php-mbstring required) to explore and query the sqlite3 databases.

If you have the output in hand, can you not convert it to the database format you desire?

I guess you request this feature to avoid the cache being dumped to a text file that goes onto the SD card, is that correct?
If so, let me suggest an alternative that is already available in the current version of FTL: Put the log file on a RAM disk.

If this is not your main concern, then I’d like to know what do you want to achieve with this SQL file? What is the advantage for users to be able to store the content of the cache in a database? This will surely take some time as well as SQL injections are obviously more expensive than simple fprintf(...) calls dumping into a text file.

I’ve dumped the cache, it goes to the pihole log, which is already on ramdisk.
This dump contains all entries, including the 0.0.0.0 replies. The idea is, to have pihole-FTL write the valid content (NOT 0.0.0.0 or ::) to an additional database (only if instructed by the command sudo kill -10). The entries in this database could than be used to generate an unbound prefill configuration file (not part of pihole, customization for advanced users), ensuring the data is available for use in the unbound cache at all time, thus speeding up the response time.

As I indicated, I’m currently trying to achieve this, using the unbound + Redis configuration, not yet sure this is benificial…

Okay, so the request would be for an additional effort during cache dumping. Acknowledged.

Okay, no worries. Let’s wait for how the other discussion evolves before continuing here. This is neither an approval nor dismissal of this feature request. There is just no point in discussing something we are not sure yet if it will – at all – be used in the future.