<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;
color:black;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Vorformatiert Zchn";
margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";
color:black;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman",serif;
color:black;}
span.HTMLVorformatiertZchn
{mso-style-name:"HTML Vorformatiert Zchn";
mso-style-priority:99;
mso-style-link:"HTML Vorformatiert";
font-family:"Consolas",serif;
color:black;}
span.E-MailFormatvorlage20
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 2.0cm 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body bgcolor="white" lang="DE-AT" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<div style="border:none;border-left:solid blue 1.5pt;padding:0cm 0cm 0cm 4.0pt">
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="DE" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext">Von:</span></b><span lang="DE" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext"> Andrea Biancalani <a.biancalani@conmet.it>
<br>
<b>Gesendet:</b> Freitag, 13. Oktober 2023 11:16<br>
<b>An:</b> All about using and deploying powerdns <pdns-users@mailman.powerdns.com><br>
<b>Cc:</b> Klaus Darilion <klaus.darilion@nic.at><br>
<b>Betreff:</b> Re: [Pdns-users] Adding multiple records to all zones at once<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><br>
Thanks to Klaus & Chris to help me figure out how to go ahead. <br>
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...).<br>
<br>
For whom interested in doing something similar this is my free to use bash script to solve (change values to your needs):<br>
<br>
<br>
<o:p></o:p></p>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<p class="MsoNormal"><span style="font-size:7.5pt">#!/bin/bash<br>
<br>
clear<br>
<br>
domain=""<br>
autodiscover_value="</span><b><i><span style="font-size:7.5pt;color:red"><AUTODISCOVER HOSTNAME></span></i></b><span style="font-size:7.5pt">"<br>
autoconfig_value="</span><b><i><span style="font-size:7.5pt;color:red"><AUTOCONFIG HOSTNAME></span></i></b><span style="font-size:7.5pt">"<br>
<b><br>
# GET ALL DOMAIN_IDs</b><br>
<br>
mysql <<EOF > all_ids<br>
<br>
use powerdns;<br>
select domain_id from records;<br>
<br>
EOF<br>
<br>
<b># GET DOMAINS_ID WHICH ALREADY CONTAINS MISSING VALUES</b><br>
<br>
mysql <<EOF > excluded_ids<br>
<br>
use powerdns;<br>
select domain_id from records where content like '%autodiscover%' and (type = 'cname' or type = 'srv');<br>
select domain_id from records where content like '%autoconfig%' and (type = 'cname' or type = 'srv');<br>
<br>
EOF<br>
<br>
<b># EXCLUDING FROM ALL DOMAIN_ID THOSE THAT INCLUDES ALREADY AN AUTOCONFIG/AUTODISCOVER RECORD IN IT</b><br>
<br>
grep -vf excluded_ids all_ids|sort --version-sort -f|uniq|grep -v domain_id > good_ids<br>
<br>
<b># PUSHING DIFFS IN DB</b><br>
<br>
for i in $(cat good_ids);<br>
do<br>
<br>
<b># GET DOMAIN NAME FROM DOMAIN_ID IN DB</b><br>
<br>
mysql <<EOF > domain_name<br>
use powerdns;<br>
select name from domains where id = '$i';<br>
EOF<br>
<br>
<b># SET A VARIABLE TO LOOP THROUGH</b><br>
<br>
domain=$(cat domain_name|grep "\.")<br>
<b><br>
# DEBUG: UNCOMMENT IF YOU WANT JUST VIEW QUERY OUTPUT. COMMENT OUT ACTION QUERIES TOO!!<br>
<br>
# echo "insert into records(domain_id,name,type,content,ttl) VALUES ('$i','autoconfig.$domain','CNAME','$autoconfig_value','3600');"<br>
# echo "insert into records(domain_id,name,type,content,ttl) VALUES ('$i','autodiscover.$domain','CNAME','$autodiscover_value','3600');"</b><br>
<br>
<b>### ACTION QUERIES ###</b><br>
<br>
echo "Working on : $domain"<br>
<br>
mysql <<EOF<br>
use powerdns;<br>
insert into records(domain_id,name,type,content,ttl) VALUES ('$i','autoconfig.$domain','CNAME','$autoconfig_value','3600');<br>
insert into records(domain_id,name,type,content,ttl) VALUES ('$i','autodiscover.$domain','CNAME','$autodiscover_value','3600');<br>
EOF<br>
<br>
<b>######################</b><br>
<br>
done<br>
<br>
rm all_ids excluded_ids good_ids domain_name<br>
<br>
echo -e "\n\n"<br>
echo "Now if you want to increase zones serial use this loop:"<br>
echo ""<br>
echo "for i in $(pdnsutil list-all-zones|grep -v in-addr.arpa|sort); do pdnsutil increase-serial $i; done"<br>
<br>
</span><span style="font-size:7.5pt;color:#1F497D"><o:p></o:p></span></p>
</blockquote>
</div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">In this loop you should add "pdnsutil rectify-zone $i", or after the loop make a "pdnsutil rectify-all-zones"<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">regards<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">Klaus<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:7.5pt">echo ""<br>
echo "Slaves using AXFER will be automatically updated."</span><o:p></o:p></p>
<p class="MsoNormal" style="margin-bottom:12.0pt"><br>
<br>
Already tested and working. Goal achieved ;)<br>
<br>
Have a nice day everybody.<br>
Andrea<br>
<br>
<o:p></o:p></p>
<div>
<p class="MsoNormal">Il 13/10/2023 09:16, Klaus Darilion via Pdns-users ha scritto:<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<pre>Why not do something along the lines of (PostgreSQL syntax as that's<o:p></o:p></pre>
<pre>what I know, and completely untested):<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>INSERT INTO records (domain_id, name, type, content, ttl)<o:p></o:p></pre>
<pre> SELECT domain.id, 'autoconfig.' || domains.name, 'CNAME, '<my autoconfig<o:p></o:p></pre>
<pre>zone>, 3600<o:p></o:p></pre>
<pre> FROM domains<o:p></o:p></pre>
<pre> JOIN records ON domains.id = records.domain_id<o:p></o:p></pre>
<pre> WHERE domains.id NOT IN (<o:p></o:p></pre>
<pre> SELECT domains_id FROM records<o:p></o:p></pre>
<pre> JOIN domains ON domains.id = records.domain_id<o:p></o:p></pre>
<pre> WHERE name = 'autoconfig.' || domains.name );<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>You may want to just run the SELECT first to see that it gives you what<o:p></o:p></pre>
<pre>you want.<o:p></o:p></pre>
</blockquote>
<pre><o:p> </o:p></pre>
<pre>When you insert records directly into the DB, the zone may be broken (NSEC, NSEC3 ordername, empty non-terminal RRs ...).<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>Therefore you have to call "rectify" for every zone that was changed and PowerDNS will check and if necessary fix the zone.<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>regards<o:p></o:p></pre>
<pre>Klaus<o:p></o:p></pre>
<pre>_______________________________________________<o:p></o:p></pre>
<pre>Pdns-users mailing list<o:p></o:p></pre>
<pre><a href="mailto:Pdns-users@mailman.powerdns.com">Pdns-users@mailman.powerdns.com</a><o:p></o:p></pre>
<pre><a href="https://mailman.powerdns.com/mailman/listinfo/pdns-users">https://mailman.powerdns.com/mailman/listinfo/pdns-users</a><o:p></o:p></pre>
</blockquote>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal">-- <br>
Andrea Biancalani - <i>Rep. Commerciale e Tecnico</i><br>
<b>Connessioni Metropolitane srl</b><br>
Via G. Valentini, 14 - Prato (PO) - 59100<br>
Sito web: <a href="https://www.conmet.it">https://www.conmet.it</a><br>
Tel. 0574 536553<br>
Fax. 0574 536554<o:p></o:p></p>
</div>
</div>
</body>
</html>