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

Follow

Comments

11 comments

  • Avatar
    Timo Meinen

    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
  • Avatar
    Anton Maslov

    @Timo

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

    sh -x ./report.sh

    0
    Comment actions Permalink
  • Avatar
    Timo Meinen (Edited )
    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
  • Avatar
    Denis Bykov

    @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
  • Avatar
    Timo Meinen

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

    0
    Comment actions Permalink
  • Avatar
    Denis Bykov

    @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
  • Avatar
    Brian Craigie

    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
  • Avatar
    Brian Craigie

    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
  • Avatar
    Julian Bonpland Mignaquy

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

    1
    Comment actions Permalink
  • Avatar
    Brian Craigie

    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
  • Avatar
    Julian Bonpland Mignaquy

    Thank you again! This will require some deeper investigation.

    0
    Comment actions Permalink

Please sign in to leave a comment.

Have more questions? Submit a request