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

Follow

Comments

4 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

Please sign in to leave a comment.

Have more questions? Submit a request