How to get a list of all email accounts and their disk usage via a command-line interface in Plesk

Question

How to get a list of all email accounts and their disk usage via a command-line interface in Plesk?

Answer

1. Connect to a Plesk server via SSH (Linux) / RDP (Windows Server).

2. Run the following SQL queries to get email accounts:

   Note: The output of stats is shown in bytes. "-1" under the Mailbox limit column means unlimited.

   • of subscriptions' main domains:

     MYSQL_LIN: plesk db "SELECT concat(mail.mail_name,'@',domains.name) AS 'Email address',mn_param.val AS 'Mailbox usage',Limits.value AS 'Mailbox limit' FROM mail LEFT JOIN mn_param ON mail.id=mn_param.mn_id LEFT JOIN domains ON mail.dom_id=domains.id LEFT JOIN Subscriptions ON domains.id=Subscriptions.object_id LEFT JOIN SubscriptionProperties ON Subscriptions.id=SubscriptionProperties.subscription_id LEFT JOIN Limits ON SubscriptionProperties.value=Limits.id WHERE mn_param.param='box_usage' AND Subscriptions.object_type='domain' AND SubscriptionProperties.name='limitsId' AND Limits.limit_name='mbox_quota'"

   • of subscriptions' additional domains:

     MYSQL_LIN: plesk db "SELECT concat(mail.mail_name,'@',domains.name) AS 'Email address',mn_param.val AS 'Mailbox usage',Limits.value AS 'Mailbox limit' FROM mail LEFT JOIN mn_param on mail.id=mn_param.mn_id LEFT JOIN domains on mail.dom_id=domains.id LEFT JOIN Subscriptions ON domains.webspace_id=Subscriptions.object_id LEFT JOIN SubscriptionProperties ON Subscriptions.id=SubscriptionProperties.subscription_id LEFT JOIN Limits ON SubscriptionProperties.value=Limits.id WHERE mn_param.param='box_usage' AND Subscriptions.object_type='domain' AND SubscriptionProperties.name='limitsId' AND Limits.limit_name='mbox_quota' AND domains.webspace_id<>0 AND domains.parentDomainId=0"

   Sample output:

   MYSQL_LIN: +---------------------+---------------+---------------+
   | Email address       | Mailbox usage | Mailbox limit |
   +---------------------+---------------+---------------+
   | jdoe@example.com    | 146774        | 52428800      |
   +---------------------+---------------+---------------+