Finding domains without NS records in PowerDNS

So, a fun one happened the other day. I needed to remove all the old domains from our nameserver cluster which is open to customers who were no longer pointing there domains to our nameservers. Easy eh?

Well, unless you have access to a unrestricted IP capable of doing that many lookups on Ausreg (most of the domains are’s) you would need some other method.I just did a dig to get the nameservers but a lot returned without NS records and thus didn’t know where they were pointing.

Turns out some of our customers didn’t use our nice templates when they made the domains, so it didn’t create a NS record in our system. So I thought better fix that. Anyway, the SQL is

<pre>SELECT, FROM domains WHERE NOT IN (SELECT domain_id FROM records WHERE type = “NS”);</pre>

You could also substitue this for any record type really. Someone with mad SQL skilz could probably optimise that, but it does what i need it to 🙂

If you save the above to a CSV file (into outfile ‘/tmp/domains.txt’ FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’;)

and then you can use something like this to automate the creating of the records


[ ! -f $INPUT ] && { echo “$INPUT file not found”; exit 99; }
while read ID DOMAIN

mysql -u pdnsuser -p ‘pass’ powerdns -e “insert into records (domain_id,name,type,content,ttl,prio,change_date) values ($ID,’$DOMAIN’,’NS’,’ns1.yourdomain’,3600,0,unix_timestamp());”
mysql -u pdnsuser -p ‘pass’ powerdns -e “insert into records (domain_id,name,type,content,ttl,prio,change_date) values ($ID,’$DOMAIN’,’NS’,’ns2.yourdomain’,3600,0,unix_timestamp());”
mysql -u pdnsuser -p ‘pass’ powerdns -e “insert into records (domain_id,name,type,content,ttl,prio,change_date) values ($ID,’$DOMAIN’,’NS’,’ns3.yourdomain’,3600,0,unix_timestamp());”

done < $INPUT


Leave a Reply

Your email address will not be published.

Blue Captcha Image