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

Please sign in to leave a comment.