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.

How to migrate a LVM-based KVM guest to another host

kvm-logo_300dpiIn the past, I have been using the immensely useful virt-backup.pl script to migrate a LVM-based (raw volume) Linux KVM guest from one host to another. However, there is an even easier way to cold-migrate a KVM guest. This approach is particularly helpful if there’s not enough disk space on the host to create a gzipped backup of the logical volume using the virt-backup.pl script.

Here’s how it works:

  1. Use lvcreate to create the new logical volume on the destination host with the same size as the source logical volume. Use the lvdisplay command to find out the required size.
  2. virsh shutdown the source KVM guest
  3. On the source host: screen dd if=/dev/vg_ssd/lv_vm_wopr | pv | ssh -C root@desthost dd of=/dev/vg0/lv_vm_wopr
  4. Wait until finished

I’m using the screen command so it will continue running in the background once I close the ssh session on the source host. Use CTRL-A-D to background a screen session and screen -dr to bring it back up.

Using ssh makes sure the entire transfer is encrypted. The -C parameter makes sure the content will be compressed which may speed up the transfer considerably (or not, on a slow CPU). Obviously, the new KVM guest has to be virsh define‘d on the destination based on the virsh dumpxml configuration data from the source host.

Intel Gigabit CT kext for macOS Sierra 10.12

macos-sierraThe Intel Gigabit CT Desktop ethernet PCI adapter is still one of the fastest and most robust NICs for the Hackintosh. This did not change with macOS Sierra 10.12. I’m still using the IONetworkingFamilyInjector.kext in Clover’s kext folder to override the compatibility list in Apple’s own Intel82574L.kext. However, while the installation of macOS Sierra went smoothly, I lost all network connectivity after installing Sierra. A quick look at the network kernel extensions revealed that Apple changed the driver identifier of the Intel82574L.kext, rendering the injector useless. After changing the identifier in the injector and a reboot, network connectivity was back again.

The patched injector kext is available for download here: IONetworkingFamilyInjector.kext_.macos-sierra.zip. The kext injector has to be placed into the EFI/CLOVER/kexts/10.12 folder.

intel gigabit desktop ct

The Hackintosh is still running in full protected mode (if enabled in Clover):
$ csrutil status
System Integrity Protection status: enabled.

A permanent solution?

While writing this post, I stumbled upon an alternative solution, which seems to be permanent. However, it requires flashing the Intel NIC and changing it’s device ID property. Check out this post on InsanelyMac. I’m going to try this approach in the near future since it would reduce the number of kexts in my Hackintosh rig to just one (only FakeSMC).

Installing Ubuntu Server 16.04 on PC Engines APU or APU2

Most people use PC Engines APU series (APU1D4, APU2C4) system boards for pfSense firewalls (pfSense is awesome!). However, the Ubuntu Server x86-64 version runs on these boards very well too which can turn them into a lightweight, portable Plex Media Server for instance. The APU series doesn’t have a video port, that’s why the Ubuntu Server 16.04 image requires some modifications in order to use the serial port for console output instead. Since the Ubuntu image is using a read-only CD-ROM filesystem, I’m using UNetbootin to create a bootable Live USB drive which lets me modify files. While UNetbootin is available on Linux and MacOS too, only the Windows version gave me consistent results after formatting the USB drive to FAT32 file format. YMMW, but if you get weird bootloader errors, try formatting/creating the bootable drive on Windows.

To access the APU’s serial port, a RS-232 DB9 null-modem to USB interface is required and some software to connect to it. I’m using a Prolific PL-2303 based interface and minicom on Linux or Serial when I’m on my Mac.

Once the Live USB drive has been successfully prepared by UNetbootin, the following files have to be modified in order to send the console output over the serial port:

In /isolinux/isolinux.cfg, insert the following lines at the top:

serial 0 115200
console 0

In /isolinux/txt.cfg, the replace the first occurrence of the append keyword (in the “install” section) with:

append file=/cdrom/preseed/ubuntu-server.seed vga=off initrd=/install/initrd.gz -- console=ttyS0,115200n8 -

In /syslinux.cfg, insert the following lines at the top:

serial 0 115200
console 0

Again in /syslinux.cfg, replace the first occurrence of the append keyword (in the “unetbootindefault” section) with:

append initrd=/ubninit vga=off console=ttyS0,115200n8 --

Using the serial cable you should now be able to install Ubuntu Server 16.04 on the APU:

apu-ubuntu-serial-console-1apu-ubuntu-serial-console-2

During the installation:

  • Make sure the APU is connected to a router. While configuring the network, always keep in mind that the rightmost network port ist the first port (eth0 or enp1s0)
  • Make sure to include “OpenSSH server” when choosing software to install

Most likely, there won’t be any visible console output (i.e. a login prompt) after the first reboot because the installer didn’t add the necessary parameters to GRUB_CMDLINE_LINUX. This is where the SSH daemon comes in handy (-:

To fix this, use SSH to login to the server and modify /etc/default/grub to include the following line:

GRUB_CMDLINE_LINUX="console=tty0 console=ttyS0,115200n8"

Run update-grub , reboot the APU and eventually there should be a login prompt:

apu-ubuntu-serial-console-3

Adding a DS3231 Real Time Clock to the Raspberry Pi 3

ds3231-rtcSince the Raspberry Pi 3 doesn’t come with a battery-powered real time clock, it will only show the correct time once it has Internet connectivity (thanks to the NTP daemon). If the Raspberry Pi 3 is not connected to the Internet, you might want to add a hardware clock to set the current date. Here’s how to add a DS3231 real time clock GPIO module to the Raspberry Pi 3 in Raspbian Jessy Lite:

  1. Get a DS3231 real time clock module and install it on the GPIO header of the Raspberry Pi 3 on pin 1
  2. Add the following line at the end of /boot/config.txt in Raspbian Jessy:
    dtoverlay=i2c-rtc,ds3231
  3. We don’t need fake-hwclock anymore:
    apt-get purge fake-hwclock
  4. Check/set the current system time and write the system time to the RTC module using:
    hwclock -w
  5. Set the correct time zone using:
    dpkg-reconfigure tzdata
  6. Edit /etc/rc.local and add the hwclock command above the line that says “exit 0”:
    /sbin/hwclock -s
  7. The /etc/init.d/hwclock.sh shell scripts tends to corrupt this RTC clock module. In my case, the RTC clock was set to 2066/01/01 after every reboot. To prevent this from happening, edit /etc/default/hwclock and set HWCLOCKACCESS to no:
    HWCLOCKACCESS=no
  8. Reboot
  9. Done! Raspbian will now set the time from the RTC clock during boot even if there is no Internet connectivity available.
  10. If RTC corruption is still happening, you may have to get rid of the NTP daemon as well using:
    apt-get purge ntp
    apt-get install ntpdate
  11. After the NTP daemon has been removed, you can still sync the system clock using ntpdate-debian which you might add to /etc/rc.local as well (after the hwclock command though) – just in case there is an Internet connection available during boot. And/or add it to /etc/cron.daily for example.

Raspbian Jessy Lite will detect the DS3231 real time clock module automatically (as a DS1307 module but nevermind), there’s no need to whitelist or blacklist any I2C modules. There’s no need to run the i2cdetect command from the i2c-tools package. Once the clock module is detected, this line should be visible using dmesg:

# dmesg | grep rtc
[    6.640799] rtc-ds1307 1-0068: rtc core: registered ds3231 as rtc0

Check /proc/driver/rtc for more data on the RTC:

# cat /proc/driver/rtc
rtc_time : 19:26:18
rtc_date : 2016-03-25
alrm_time : 00:00:00
alrm_date : 1970-01-01
alarm_IRQ : no
alrm_pending : no
update IRQ enabled : no
periodic IRQ enabled : no
periodic IRQ frequency : 1
max user IRQ frequency : 64
24hr : yes