<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<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>
<blockquote type="cite"><font size="1">#!/bin/bash<br>
<br>
clear<br>
<br>
domain=""<br>
autodiscover_value="<b><i><font color="#ff0000"><AUTODISCOVER
HOSTNAME></font></i></b>"<br>
autoconfig_value="<b><i><font color="#ff0000"><AUTOCONFIG
HOSTNAME></font></i></b>"<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>
echo ""<br>
echo "Slaves using AXFER will be automatically updated."</font></blockquote>
<br>
<br>
Already tested and working. Goal achieved ;)<br>
<br>
Have a nice day everybody.<br>
Andrea<br>
<br>
<br>
<div class="moz-cite-prefix">Il 13/10/2023 09:16, Klaus Darilion via
Pdns-users ha scritto:<br>
</div>
<blockquote type="cite"
cite="mid:568b12cafb2345d9b336645bee30e2c9@nic.at">
<blockquote type="cite">
<pre class="moz-quote-pre" wrap="">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.
</pre>
</blockquote>
<pre class="moz-quote-pre" wrap="">
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
<a class="moz-txt-link-abbreviated" href="mailto:Pdns-users@mailman.powerdns.com">Pdns-users@mailman.powerdns.com</a>
<a class="moz-txt-link-freetext" href="https://mailman.powerdns.com/mailman/listinfo/pdns-users">https://mailman.powerdns.com/mailman/listinfo/pdns-users</a>
</pre>
</blockquote>
<br>
<div class="moz-signature">-- <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"
class="moz-txt-link-freetext">https://www.conmet.it</a><br>
Tel. 0574 536553<br>
Fax. 0574 536554<br>
</div>
</body>
</html>