How to get a list of all domains with information about IP address, DMARC status and SSL certificates?

• 

  Timo Meinen September 25, 2019 08:47

  I get the following error running the script:

  ERROR 1242 (21000) at line 1: Subquery returns more than 1 row
  exit status 1

  0

  Comment actions Permalink
• 

  Anton Maslov October 01, 2019 07:55

  @Timo

  Could you please run the script as below and share the output:

  sh -x ./report.sh

  0

  Comment actions Permalink
• 

  Timo Meinen October 01, 2019 16:42 (Edited October 01, 2019 16:42)

  root@mail:~# sh -x ./report.sh
  
  + plesk db -e 'CREATE TABLE IF NOT EXISTS tmp_report (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,dom_id INT, dom_name varchar(255),ipCollectionId INT, ip_address varchar(255), ip_address6 varchar(255), Dkim varchar(255), Dmarc varchar(255), Ssl_cert varchar(255), Ssl_cert_mail varchar(255));'
  
  + plesk db -e 'INSERT INTO tmp_report (dom_id, dom_name ) SELECT dom.id, dom.name FROM domains dom;'
  
  + plesk db -e 'UPDATE tmp_report t set t.ipCollectionId=(SELECT d.ipCollectionId FROM DomainServices d where t.dom_id = d.dom_id AND d.type = '\''web'\'');'
  
  + plesk db -e 'UPDATE tmp_report t set t.ip_address6=(SELECT iad.ip_address FROM IP_Addresses iad where iad.id in (select ia.ipAddressId from IpAddressesCollections ia where ia.ipCollectionId=t.ipCollectionId) and iad.ip_address like '\''%:%'\'');'
  
  + plesk db -e 'UPDATE tmp_report t set t.ip_address=(SELECT iad.ip_address FROM IP_Addresses iad where iad.id in (select ia.ipAddressId from IpAddressesCollections ia where ia.ipCollectionId=t.ipCollectionId) and iad.ip_address like '\''%.%'\'');'
  
  + plesk db -e 'UPDATE tmp_report t set t.Dmarc=(SELECT dnsr.val FROM dns_recs dnsr where dns_zone_id =(select dom.dns_zone_id from domains dom where dom.Id=t.dom_id) and dnsr.val like '\''%DMARC%'\'');'
  
  + plesk db -e 'UPDATE tmp_report t set t.Dkim=(SELECT dnsr.val FROM dns_recs dnsr WHERE dns_zone_id =(SELECT dom.dns_zone_id FROM domains dom WHERE dom.Id=t.dom_id) AND dnsr.val LIKE '\''%DKIM%'\'');'
  
  ERROR 1242 (21000) at line 1: Subquery returns more than 1 row
  
  exit status 1
  
  + plesk db -e 'UPDATE tmp_report t set t.Ssl_cert=(SELECT cert.name FROM certificates cert where cert.id =(select host.certificate_id from hosting host where host.dom_id=t.dom_id));'
  
  + plesk db -e 'UPDATE tmp_report t set t.Ssl_cert_mail=(SELECT certificates.name FROM dom_param, domains, certificates WHERE dom_param.dom_id=domains.id AND dom_param.param='\''webmail_certificate_id'\'' AND domains.name=t.dom_name AND certificates.id=dom_param.val);'
  
  + plesk db -e 'select dom_name,ip_address,ip_address6,Dkim,Dmarc,Ssl_cert,Ssl_cert_mail from tmp_report order by dom_name'
  
  + plesk db -e 'drop table tmp_report;'

  0

  Comment actions Permalink
• 

  Denis Bykov October 22, 2019 16:04

  @Timo Meinen

  There is a possibility that the DNS setup differs from what is expected by script, i.e. there are two DMARC records configured for the same domain.
  To troubleshoot it further, please check the output of the following queries:

  # plesk db "SELECT dns_recs.val, dns_zone_id, dns_recs.val FROM dns_recs, tmp_report where dns_zone_id =(select domains.dns_zone_id from domains where domains.Id=tmp_report.dom_id) and dns_recs.val like '%DMARC%';"
  # plesk db "SELECT domains.dns_zone_id, domains.Id, tmp_report.dom_id from domains, tmp_report where domains.Id=tmp_report.dom_id;"

  0

  Comment actions Permalink
• 

  Timo Meinen November 07, 2019 09:23

  @... Sorry, Table 'psa.tmp_report' doesn't exist

  0

  Comment actions Permalink
• 

  Denis Bykov November 12, 2019 20:42

  @Timo Meinen
  Please create it as in the script:

  # plesk db -e 'CREATE TABLE IF NOT EXISTS tmp_report (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,dom_id INT, dom_name varchar(255),ipCollectionId INT, ip_address varchar(255), ip_address6 varchar(255), Dkim varchar(255), Dmarc varchar(255), Ssl_cert varchar(255), Ssl_cert_mail varchar(255));'

  # plesk db "SELECT dns_recs.val, dns_zone_id, dns_recs.val FROM dns_recs, tmp_report where dns_zone_id =(select domains.dns_zone_id from domains where domains.Id=tmp_report.dom_id) and dns_recs.val like '%DMARC%';"
  # plesk db "SELECT domains.dns_zone_id, domains.Id, tmp_report.dom_id from domains, tmp_report where domains.Id=tmp_report.dom_id;"

  0

  Comment actions Permalink
• 

  Brian Craigie October 19, 2021 13:34

  I get the same error as Timo - ERROR 1242 (21000) at line 1: Subquery returns more than 1 row

  I created the tmp_report table and ran the two queries above and they returned nothing.  What next? :-)

   

   

  0

  Comment actions Permalink
• 

  Brian Craigie October 19, 2021 14:22

  Mine was failing at the Dmarc line, and I realised this was because my master domain has several DMARC records to allow reporting for other domains, so I added AND host not like '%._report._%' to line 12 in the script the line just before );" at the end and that has fixed it. 

  Since it is valid to have more than one DMARC record containing ._report._ (but otherwise only one DMARC line is allowed), I would suggest updating the script to cope with this eventuality.

   

  0

  Comment actions Permalink
• 

  Julian Bonpland Mignaquy October 20, 2021 13:05

  Hi Brian Craigie, thanks for that, i will test it and update the script.

  1

  Comment actions Permalink
• 

  Brian Craigie October 20, 2021 14:24

  Thanks :-)

  Actually, I found another use-case where more than one valid DMARC entry can exist on a domain.  When someone has added a DMARC entry for MailChimp.  I discovered this today and it made the script fail again, so actually, it might be better to modify the script to cope with multiple rows in the subquery and add all the DMARC entries to the report.  I'm not sure how to modify the SQL to do that though.

  0

  Comment actions Permalink
• 

  Julian Bonpland Mignaquy October 20, 2021 14:45

  Thank you again! This will require some deeper investigation.

  0

  Comment actions Permalink

Please sign in to leave a comment.