Pihole Usage

Billable ingest volume for the Pi-hole custom log over the last 90 days.

KQL Library  /  Pi-hole

 Pi-hole pihole-usage.kql

Billable ingest volume for the Pi-hole custom log over the last 90 days.

Platforms Pi-hole Microsoft Sentinel
Data Usage
Deep Dive  Deep Dive
 Deep Dive  Download .kql 
// Author: Ian D. Hanley (DevSecOpsDad) | linkedin.com/in/ianhanley | devsecopsdad.com | devsecopsdadattack.com
// Source: Watching The DNS Watcher: Pihole Logs In Sentinel (2025-03-30) — https://www.hanley.cloud/2025-03-30-Watching-the-DNS-Watcher-Pihole-Logs-in-Sentinel/
// Platforms: Pi-hole, Microsoft Sentinel
// Data: Usage
Usage
| where TimeGenerated > ago(90d) 
| where IsBillable == true
| where DataType == "pihole_CL"
| summarize TotalVolumeGB = sum(Quantity) / 1000 by bin(StartTime, 1d), DataType
| render columnchart     

// Alternate Query
// Summarize Pi-hole usage statistics over time
PiHole
| summarize TotalQueries = count(), BlockedQueries = countif(QueryType == "Blocked") by bin(TimeGenerated, 1h)  // Aggregate total and blocked queries in 1-hour intervals
| extend BlockedPercentage = todouble(BlockedQueries) / todouble(TotalQueries) * 100  // Calculate the percentage of blocked queries
| render timechart  // Visualize the data as a time chart