• Plesk Uservoice will be deprecated by October. Moving forward, all product feature requests and improvement suggestions will be managed through our new platform Plesk Productboard.
    To continue sharing your ideas and feedback, please visit features.plesk.com

Resolved How to get domain mail service enabled/disabled from Plesk database?

Visnet

Basic Pleskian
Server operating system version
CentOS 7
Plesk version and microupdate number
18.0.44.3
I'm trying to populate a list of all domains (and aliases) that have the mail service enabled in a script.

Though it's possible to walk through domains and domain aliases using plesk bin domain --info example.com | grep -E 'Mail service:\s*On', this is slow and inefficient compared to finding these in the database directly.

It's easy to do this for domain aliases:
Code:
/usr/sbin/plesk db -Ns --execute="SELECT name FROM domain_aliases WHERE mail='true' ORDER BY name ASC;"

However, there's no mail column in the domains table.

Where and how can I find the mail service on/off state for all regular domains in the Plesk database?
 
You can use this SQL query, for example:
Code:
MariaDB [psa]> SELECT d.name,ds.type,ds.status FROM domains d LEFT JOIN DomainServices ds ON ds.dom_id=d.id AND ds.type='mail';
+--------------------+------+--------+
| name               | type | status |
+--------------------+------+--------+
| domain1.com        | mail |      0 |
| domain2.com        | mail |      0 |
| sub.domain2.com    | mail |     16 |
+--------------------+------+--------+
3 rows in set (0.00 sec)

Status 0 means Active, 16 - Inactive.
 
Thanks, @IgorG !

I got some NULL values from domains that have mail disabled, though:
SQL:
MariaDB [psa]> SELECT d.name,ds.type,ds.status FROM domains d LEFT JOIN DomainServices ds ON ds.dom_id=d.id AND ds.type='mail';
+------------------------------+------+--------+
| name                         | type | status |
+------------------------------+------+--------+
| example1.com                 | mail |      0 |
| example2.com                 | mail |      0 |
| cdn.example.com              | NULL |   NULL |
| example3.net                 | mail |      0 |
| example4.com                 | mail |      0 |
| webonly.example.com          | NULL |   NULL |
| mta-sts.example.com          | NULL |   NULL |
| subdomain.example.com        | NULL |   NULL |
+------------------------------+------+--------+
8 rows in set (0.002 sec)

So I used INNER JOIN:
SQL:
MariaDB [psa]> SELECT d.name,ds.type,ds.status FROM domains d INNER JOIN DomainServices ds ON ds.dom_id=d.id AND ds.type='mail';
+----------------------------+------+--------+
| name                       | type | status |
+----------------------------+------+--------+
| example1.com               | mail |      0 |
| example2.com               | mail |      0 |
| example3.net               | mail |      0 |
| example4.com               | mail |      0 |
+----------------------------+------+--------+
4 rows in set (0.00 sec)
 
Back
Top