mysql command to view domains that have mail enabled

[Frater]

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

 

[Frater]

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

 

[Frater]

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.

#!/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}

 

[nocrash]

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!

I'm able to answer my own question after I discovered the Plesk CLI

Any feedback is welcome.