[Pdns-users] Adding multiple records to all zones at once

[Andrea Biancalani]

Thanks to Klaus & Chris to help me figure out how to go ahead.
I'm not that good using MySQL, I'm not a backend dev , but I know how to 
use bash scripting to reach my goals (even if that is a longer road...).

For whom interested in doing something similar this is my free to use 
bash script to solve (change values to your needs):

> #!/bin/bash
>
> clear
>
> domain=""
> autodiscover_value="*/<AUTODISCOVER HOSTNAME>/*"
> autoconfig_value="*/<AUTOCONFIG HOSTNAME>/*"
> *
> # GET ALL DOMAIN_IDs*
>
> mysql <<EOF > all_ids
>
> use powerdns;
> select domain_id from records;
>
> EOF
>
> *# GET DOMAINS_ID WHICH ALREADY CONTAINS MISSING VALUES*
>
> mysql <<EOF > excluded_ids
>
> use powerdns;
> select domain_id from records where content like '%autodiscover%' and 
> (type = 'cname' or type = 'srv');
> select domain_id from records where content like '%autoconfig%' and 
> (type = 'cname' or type = 'srv');
>
> EOF
>
> *# EXCLUDING FROM ALL DOMAIN_ID THOSE THAT INCLUDES ALREADY AN 
> AUTOCONFIG/AUTODISCOVER RECORD IN IT*
>
> grep -vf excluded_ids all_ids|sort --version-sort -f|uniq|grep -v 
> domain_id > good_ids
>
> *# PUSHING DIFFS IN DB*
>
> for i in $(cat good_ids);
> do
>
> *# GET DOMAIN NAME FROM DOMAIN_ID IN DB*
>
> mysql <<EOF > domain_name
> use powerdns;
> select name from domains where id = '$i';
> EOF
>
> *# SET A VARIABLE TO LOOP THROUGH*
>
> domain=$(cat domain_name|grep "\.")
> *
> # DEBUG: UNCOMMENT IF YOU WANT JUST VIEW QUERY OUTPUT. COMMENT OUT 
> ACTION QUERIES TOO!!
>
> # echo "insert into records(domain_id,name,type,content,ttl) VALUES 
> ('$i','autoconfig.$domain','CNAME','$autoconfig_value','3600');"
> # echo "insert into records(domain_id,name,type,content,ttl) VALUES 
> ('$i','autodiscover.$domain','CNAME','$autodiscover_value','3600');"*
>
> *### ACTION QUERIES ###*
>
> echo "Working on : $domain"
>
> mysql <<EOF
> use powerdns;
> insert into records(domain_id,name,type,content,ttl) VALUES 
> ('$i','autoconfig.$domain','CNAME','$autoconfig_value','3600');
> insert into records(domain_id,name,type,content,ttl) VALUES 
> ('$i','autodiscover.$domain','CNAME','$autodiscover_value','3600');
> EOF
>
> *######################*
>
> done
>
> rm all_ids excluded_ids good_ids domain_name
>
> echo -e "\n\n"
> echo "Now if you want to increase zones serial use this loop:"
> echo ""
> echo "for i in $(pdnsutil list-all-zones|grep -v in-addr.arpa|sort); 
> do pdnsutil increase-serial $i; done"
> echo ""
> echo "Slaves using AXFER will be automatically updated."


Already tested and working. Goal achieved ;)

Have a nice day everybody.
Andrea


Il 13/10/2023 09:16, Klaus Darilion via Pdns-users ha scritto:
>> Why not do something along the lines of (PostgreSQL syntax as that's
>> what I know, and completely untested):
>>
>> INSERT INTO records (domain_id, name, type, content, ttl)
>>     SELECT domain.id, 'autoconfig.' || domains.name, 'CNAME, '<my autoconfig
>> zone>, 3600
>>         FROM domains
>>         JOIN records ON domains.id = records.domain_id
>>         WHERE domains.id NOT IN (
>>             SELECT domains_id FROM records
>>                 JOIN domains ON domains.id = records.domain_id
>>                 WHERE name = 'autoconfig.' || domains.name );
>>
>> You may want to just run the SELECT first to see that it gives you what
>> you want.
> When you insert records directly into the DB, the zone may be broken (NSEC, NSEC3 ordername, empty non-terminal RRs ...).
>
> Therefore you have to call "rectify" for every zone that was changed and PowerDNS will check and if necessary fix the zone.
>
> regards
> Klaus
> _______________________________________________
> Pdns-users mailing list
> Pdns-users at mailman.powerdns.com
> https://mailman.powerdns.com/mailman/listinfo/pdns-users

-- 
Andrea Biancalani - /Rep. Commerciale e Tecnico/
*Connessioni Metropolitane srl*
Via G. Valentini, 14 - Prato (PO) - 59100
Sito web: https://www.conmet.it
Tel. 0574 536553
Fax. 0574 536554
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.powerdns.com/pipermail/pdns-users/attachments/20231013/0e3506dc/attachment.htm>