cool tricks for pihole.log data analysis

General
1

I was gobsmacked by incredibly large pihole.log file growth in an extremely short time period. Hours, not days or weeks to grow the main log to >15GB.

I dusted off my coding skillz from a previous life, and wrote some automation to import pihole.log files into SQL Server. WinSCP, PowerShell, BCP, and a SQL 2022 developer edition instance running on a VM running Ubuntu 22.04.

Processed the whole shebang in less than an hour (mind you, it was hundreds of millions of records).

If anyone is interested in the implementation details, let mw know. I'm happy to share the code.

image
image706×126 3.3 KB

YES! That is 206,055,952. 206 MILLION!

image
image386×159 16.2 KB

2

Pi-hole already stores queries in a SQLite3 database at /etc/pihole/pihole-FTL.db, see also Query Database - Pi-hole documentation.

Any reason why you didn't just run your SQL statements against Pi-hole's query database?

3

A few reasons:

  1. I didn't think about trying it :-p
  2. Now that you've given me the idea, I might...
  3. ETL'g the data to my db instance means I can keep it longer if I wish, and I can do more in-depth analysis than I think I can do in SQLite

I am concerned about the cost of querying the db live while it is getting hammered by a rogue process. I would not want my queries to be the reason that my little raspi locks up

Thanks for the link! I'll read up and figure out what I want to do next...

4

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.