How to generate daily PowerDNS statistics reports

daae8fe19d3ab224f3a104f987acd8bfPowerDNS has been powering authoritative DNS lookups to this web site for quite a while now. It’s such a remarkable¬†piece of software. Here’s how to create a daily statistics report for PowerDNS.

  1. Put the lines below in /etc/cron.daily/powerdns-report:
    #!/bin/sh
    /usr/bin/curl -s localhost:8081 | mail -s "$(echo "PowerDNS Daily Report\nMIME-Version: 1.0\nContent-Type: text/html")" root
  2. Make sure the file is executable: chmod +x /etc/cron.daily/powerdns-report
  3. Enable the internal web server (defaults to 127.0.0.1:8081) in the PowerDNS configuration file:
    webserver=yes
  4. Restart PowerDNS
  5. Make sure all Mails to root are forwarded to your e-mail account (or replace the recipient in the powerdns-report script)

This is how the PowerDNS statistics report will look like:

PowerDNS 4.1.0
Uptime: 3.94 days
Queries/second, 1, 5, 10 minute averages: 0.402, 0.212, 0.186. Max queries/second: 1.17
Cache hitrate, 1, 5, 10 minute averages: 45.0%, 33.4%, 30.9%
Backend query cache hitrate, 1, 5, 10 minute averages: 55.1%, 41.7%, 38.3%
Backend query load, 1, 5, 10 minute averages: 0.343, 0.317, 0.32. Max queries/second: 2.37
Total queries: 52665. Question/answer latency: 1.25ms

Log Messages
gmysql Connection successful. Connected to database 'pdns' on 'localhost'.	422	27.8%
AXFR of domain 'nogo.com.' initiated by 231.91.120.148	70	4.6%
AXFR of domain 'nogo.com.' failed: 231.91.120.148 cannot request AXFR	70	4.6%
AXFR of domain 'nogo.com' denied: client IP 231.91.120.148 has no permission	70	4.6%
Rest:	527	34.7%
Total:	1519	100%

Queries for existing records, but for type we don't have
wopr.com./AAAA	2193	47.1%
wopr.me./AAAA	503	10.8%
drfalken.me./AAAA	482	10.4%
mail.wopr.net./AAAA	281	6.0%
wopr.net./AAAA	253	5.4%
Rest:	685	14.7%
Total:	4653	100%

Queries for non-existent records within existent domains
dn42-fr.wopr.me./A	359	24.9%
_sip._udp.sbc.sipmly.com.wopr.net./SRV	335	23.2%
_sip._udp.sip.ovh.fr.wopr.net./SRV	214	14.8%
dn42-us.wopr.me./A	75	5.2%
_adsp._domainkey.woprs.net./TXT	14	1.0%
Rest:	290	20.1%
Total:	1443	100%

UDP Queries Received
wopr.com./A	2307	23.1%
wopr.net./A	2227	22.3%
trick77.com./AAAA	702	7.0%
wheniskeynote.com./AAAA	578	5.8%
www.trick77.com./A	293	2.9%
Rest:	3135	31.4%
Total:	10000	100%

Queries that could not be answered due to backend errors
Total:	0	100%

Queries for domains that we are not authoritative for
nogo.com./SOA	190	84.4%
cpsc.gov./ANY	16	7.1%
dnsscan.shadowserver.org./A	4	1.8%
./ANY	3	1.3%
cpsc.gov./A	2	0.9%
defcon.org./ANY	2	0.9%
./NS	1	0.4%
4caa2d31.openresolvertest.net./A	1	0.4%
c.afekv.com./A	1	0.4%
doc.gov./ANY	1	0.4%
Rest:	4	1.8%
Total:	225	100%

Remote server IP addresses
137.24.55.110	289	2.9%
164.8.230.15	108	1.1%
8.91.147.135	106	1.1%
8.91.150.198	103	1.0%
28.1.240.64	100	1.0%
163.19.151.12	87	0.9%
15.16.90.42	84	0.8%
141.28.51.41	73	0.7%
169.16.124.226	70	0.7%
164.891.213.199	63	0.6%
Rest:	8917	89.2%
Total:	10000	100%

Remote hosts sending corrupt packets
219.116.116.12	1	50.0%
116.125.42.131	1	50.0%
Total:	2	100%

Remote hosts querying domains for which we are not auth
8.194.50.198	70	31.1%
8.194.47.135	68	30.2%
163.19.151.12	52	23.1%
185.59.233.131	15	6.7%
88.65.195.128	2	0.9%
64.32.96.66	2	0.9%
41.12.122.91	1	0.4%
45.20.24.195	1	0.4%
15.52.206.174	1	0.4%
89.146.222.158	1	0.4%
Rest:	12	5.3%
Total:	225	100%

Variables
corrupt-packets	2	Number of corrupt packets received
deferred-cache-inserts	1042	Amount of cache inserts that were deferred because of maintenance
deferred-cache-lookup	250	Amount of cache lookups that were deferred because of maintenance
dnsupdate-answers	0	DNS update packets successfully answered.
dnsupdate-changes	0	DNS update changes to records in total.
dnsupdate-queries	1	DNS update packets received.
dnsupdate-refused	1	DNS update packets that are refused.
incoming-notifications	0	NOTIFY packets received.
packetcache-hit	16206	
packetcache-miss	36486	
packetcache-size	1252	
query-cache-hit	52387	Number of hits on the query cache
query-cache-miss	97378	Number of misses on the query cache
rd-queries	212	Number of recursion desired questions
recursing-answers	0	Number of recursive answers sent out
recursing-questions	0	Number of questions sent to recursor
recursion-unanswered	0	Number of packets unanswered by configured recursor
security-status	0	Security status based on regular polling
servfail-packets	0	Number of times a server-failed packet was sent out
signatures	377	Number of DNSSEC signatures made
tcp-answers	497	Number of answers sent out over TCP
tcp-answers-bytes	434810	Total size of answers sent out over TCP
tcp-queries	299	Number of TCP queries received
tcp4-answers	485	Number of IPv4 answers sent out over TCP
tcp4-answers-bytes	432435	Total size of answers sent out over TCPv4
tcp4-queries	287	Number of IPv4 TCP queries received
tcp6-answers	12	Number of IPv6 answers sent out over TCP
tcp6-answers-bytes	2375	Total size of answers sent out over TCPv6
tcp6-queries	12	Number of IPv6 TCP queries received
timedout-packets	0	Number of packets which weren't answered within timeout set
udp-answers	52665	Number of answers sent out over UDP
udp-answers-bytes	4955023	Total size of answers sent out over UDP
udp-do-queries	42490	Number of UDP queries received with DO bit
udp-queries	52665	Number of UDP queries received
udp4-answers	40096	Number of IPv4 answers sent out over UDP
udp4-answers-bytes	3508260	Total size of answers sent out over UDPv4
udp4-queries	40096	Number of IPv4 UDP queries received
udp6-answers	12569	Number of IPv6 answers sent out over UDP
udp6-answers-bytes	1446763	Total size of answers sent out over UDPv6
udp6-queries	12569	Number of IPv6 UDP queries received
key-cache-size	12	Number of entries in the key cache
latency	1251	Average number of microseconds needed to answer a question
meta-cache-size	61	Number of entries in the metadata cache
qsize-q	0	Number of questions waiting for database attention
real-memory-usage	56033280	Actual unique use of memory in bytes (approx)
signature-cache-size	179	Number of entries in the signature cache
sys-msec	37296	Number of msec spent in system time
udp-in-errors	0	UDP 'in' errors
udp-noport-errors	45	UDP 'noport' errors
udp-recvbuf-errors	0	UDP 'recvbuf' errors
udp-sndbuf-errors	0	UDP 'sndbuf' errors
uptime	340341	Uptime of process in seconds
user-msec	57664	Number of msec spent in user time
© 2013 - 2016 PowerDNS.COM BV.