mysql command to view domains that have mail enabled

Discussion in 'Parallels Plesk Panel 10.x for Linux Problems, Suggested Fixes, and How-To' started by Frater, Feb 1, 2012.

  1. Frater

    Frater New Member

    Messages:
    132
    I came accross a domain that had its mail enabled, although it didn't have any addresses configured.
    This can be used when you want to redirect, but in this case the domain wasn't supposed to handle the mail.

    If mail is enabled on the server and it should be handled by another server, that server can not send mails to that domain because it's thinking that it handles them itself.

    I looked here: http://pleskhacker.com/
    and saw some interesting info.

    I already have all the domains that have email-addresses configured by modifying a command a bit (I know this can be done better, but I haven't actively used MySQL in the past).
    Now I want a command that will list the domains with mail enabled.
    I'm talking about the checkbox.
    Somehow I can't find this in this overview.

    If I have that command I can get an overview of domains that have mail enabled but no addresses configured.
    Which is at least suspicious.
    Probably misconfigured.


    I would really appreciate the help
  2. Frater

    Frater New Member

    Messages:
    132
  3. Frater

    Frater New Member

    Messages:
    132
    I'm able to answer my own question after I discovered the Plesk CLI
    As you can see I'm not that good with mysql
    I wanted a listing of all domains and took a command I found on pleskhacker.com that lists over quota domains and started editing it...

    I would think that:
    is a valid command, but it apparently isn't.
    So I'm using:
    But here's the script I wanted.
    It highlights the domains that are probably misconfigured on your server.....
    Use it if you think it's useful.

    Any feedback is welcome.

    Code:
    #!/bin/bash
    
    ptr ()
    {
      IPLIST="`echo "$1" | grep -oE '([0-9]{1,3}\.){3}[0-9]{1,3}'`"
      if [ -z "${IPLIST}" ] ; then
        echo "No valid IP given..." >&2
        exit 1
      fi
    
      echo "${IPLIST}" | while read IP ; do
        PTR="`host ${IP} | grep -o 'pointer .*' | awk '{print $2}'`"
        if [ -z "${PTR}" ]  ; then
          echo "${IP} = No PTR"
        else
          echo "${IP} = ${PTR}"
        fi
      done
    }
    showDNS ()
    {
    
       echo -e "\t\t${1}-records:"
       if echo "${1}" | grep -iq 'MX' ; then
         host -t ${1} "${domein}." | grep -o 'handled by .*' | awk '{print $3"\t"$4}' | sort -n >${TMP2}
       else
         host -t ${1} "${domein}." >${TMP2}
       fi
    
       while read line ; do
         HOST="`echo "${line}" | awk '{print $2}'`"
         if echo "${1}" | grep -iq 'MX' ; then
           IP=`host -t A ${HOST} | grep -o 'has address .*' | awk '{print $3}' | head -n1`
           echo -e "\t\t\t${line}\t= ${IP}  \t(`ptr ${IP}`)"
         else
           echo -e "\t\t\t${line}"
         fi
       done<${TMP2}
       echo -e ''
    }
    
    
    TMP1=`mktemp`
    TMP2=`mktemp`
    TMP3=`mktemp`
    TMP4=`mktemp`
    
    
    mysql -uadmin -p`cat /etc/psa/.psa.shadow` psa -e "SELECT domains.name FROM domains, Limits WHERE domains.limits_id = Limits.id ORDER BY domains.name ASC; " | egrep '[a-z0-9.-]+\.[a-z]+$' | sort | uniq >${TMP1}
    
    if [ ! -z "$1" ] ; then
      if grep -qi "$1" ${TMP1} ; then
        grep -i "$1" ${TMP1} >${TMP2}
        cat ${TMP2} >${TMP1}
      else
        echo "Domain $1 not found on this server!" >&2
        exit 1
      fi
    fi
    
    while read domein ; do
    
       /usr/local/psa/bin/domain -i ${domein}      >${TMP4}
       if [ $? -ne 0 ] ; then
         echo "Error reading info for ${domein}"
       else
    
         mysql -uadmin -p`cat /etc/psa/.psa.shadow ` psa -e 'select CONCAT(mail_name,"@",name) as email_address, substring(accounts.password, '1') from mail left join domains on domains.id=mail.dom_id left join accounts on accounts.id=mail.account_id;' | grep "@${domein}" >${TMP2}
    
         if egrep -i '(Maildienst|Mail service):' ${TMP4} | egrep -iq '(Uit|Off)' ; then
           if [ -s ${TMP2} ] ; then
             echo "${domein}"
             echo -e "\tMail service is Off, but these addresses are configured:"
             while read line ; do
               echo -e "\t\t${line}"
             done<${TMP2}
    
             showDNS MX
             showDNS NS
             showDNS TXT
    
           fi
         else
           if [ ! -s ${TMP2} ] ; then
             echo "${domein}"
             echo -e "\tMail service is On, but NO addresses are configured!"
             showDNS MX
             showDNS NS
             showDNS TXT
           fi
         fi
       fi
    done<${TMP1}
    
    rm ${TMP1}
    rm ${TMP2}
    rm ${TMP3}
    rm ${TMP4}
    
    
  4. nocrash

    nocrash New Member

    Messages:
    34
    Thank you very much for your script!! Works great with Plesk 10.x , is there an Update for newer Version of Plesk? :) Would be great!

    Last edited: May 21, 2014

Share This Page