Results 1 to 4 of 4

Thread: mysql command to view domains that have mail enabled

  1. #1
    Mega Poster
    Join Date
    Oct 2011
    Posts
    130

    Default mysql command to view domains that have mail enabled

    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).
    mysql -uadmin -p`cat /etc/psa/.psa.shadow ` psa -e 'select CONCAT(mail_name,"@",name) as email_address from mail left join domains on domains.id=mail.dom_id left join accounts on accounts.id=mail.account_id;' | grep @ | awk -F@ '{print $2}' | sort | uniq >domainswithmailaddresses
    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.

    mistery_mysql_command | grep -vf domainswithmailaddresses

    I would really appreciate the help



  2. #2
    Mega Poster
    Join Date
    Oct 2011
    Posts
    130

    Default

    It's a shame this site: http://pleskhacker.com/ isn't maintained anymore...
    No-one has an idea?



  3. #3
    Mega Poster
    Join Date
    Oct 2011
    Posts
    130

    Default

    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:
    SELECT domains.name FROM domains ORDER BY domains.name ASC;
    is a valid command, but it apparently isn't.
    So I'm using:
    SELECT domains.name FROM domains, Limits WHERE domains.limits_id = Limits.id ORDER BY domains.name ASC;
    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}


    1 out of 1 members found this post helpful.

  4. #4

    Thumbs up

    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!

    Quote Originally Posted by Frater View Post
    I'm able to answer my own question after I discovered the Plesk CLI

    Any feedback is welcome.
    Last edited by nocrash; May 21, 2014 at 05:22 AM.
    kindly regards



Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •