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

[Visnet]

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:

/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?

 

[IgorG]

You can use this SQL query, for example:

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.

 

[Visnet]

Thanks, @IgorG !

I got some NULL values from domains that have mail disabled, though:

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:

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)