Applicable to:
- Plesk for Linux
- Plesk for Windows
Question
How to export a list of all domains in Plesk with their status, service plan, disk usage, traffic and expiration date to an Excel file?
Answer
Currently, there is no such functionality. If you'd like to see this feature in Plesk, vote for it on Plesk UrserVoice.
As a workaround, the required information can be retrieved from the Plesk database:
-
Connect to a Plesk server via SSH (Linux) / RDP (Windows Server).
-
Run one of the following SQL queries:
-
to show the output in a command-line interface:
MYSQL_LIN: # plesk db "SELECT cl.id AS 'Client ID', cl.pname AS 'Name', cl.login AS 'Login', d.id AS 'Domain ID', d.name AS 'Domain', FROM_UNIXTIME(Limits.value) AS 'Exp date', Templates.name AS 'service plan', ROUND(d.real_size/1024/1024,2) AS 'disk usage (MB)', cl.vendor_id AS 'Belongs to', d.status AS 'Status', ip.ip_address AS 'IPv4/IPv6', round(sum(dtf.http_in)/1024/1024,2) AS 'HTTP_IN (MB)', round(sum(dtf.http_out)/1024/1024,2) AS 'HTTP_OUT (MB)' FROM DomainServices ds,IpAddressesCollections ipc, IP_Addresses ip, clients cl, domains d LEFT JOIN Subscriptions as s ON d.id=s.object_id LEFT JOIN PlansSubscriptions AS pls ON s.id=pls.subscription_id LEFT JOIN Templates ON pls.plan_id=Templates.id LEFT JOIN SubscriptionProperties AS sp ON s.id=sp.subscription_id LEFT JOIN Limits ON sp.value=Limits.id left join DomainsTraffic AS dtf on d.id=dtf.dom_id WHERE (sp.name='limitsId' OR sp.name IS NULL) AND (Limits.limit_name='expiration' OR Limits.limit_name is NULL) AND (Templates.type <> 'domain_addon' OR Templates.type IS NULL) AND d.id = ds.dom_id AND d.cl_id = cl.id AND ds.type = 'web' AND ds.ipCollectionId = ipc.ipCollectionId AND ip.id = ipc.ipAddressId group by d.id"
-
to export the output in a .csv file:
# plesk db "SELECT cl.id AS 'Client ID', cl.pname AS 'Name', cl.login AS 'Login', d.id AS 'Domain ID', d.name AS 'Domain', FROM_UNIXTIME(Limits.value) AS 'Exp date', Templates.name AS 'service plan', ROUND(d.real_size/1024/1024,2) AS 'disk usage (MB)', cl.vendor_id AS 'Belongs to', d.status AS 'Status', ip.ip_address AS 'IPv4/IPv6', round(sum(dtf.http_in)/1024/1024,2) AS 'HTTP_IN (MB)', round(sum(dtf.http_out)/1024/1024,2) AS 'HTTP_OUT (MB)' FROM DomainServices ds,IpAddressesCollections ipc, IP_Addresses ip, clients cl, domains d LEFT JOIN Subscriptions as s ON d.id=s.object_id LEFT JOIN PlansSubscriptions AS pls ON s.id=pls.subscription_id LEFT JOIN Templates ON pls.plan_id=Templates.id LEFT JOIN SubscriptionProperties AS sp ON s.id=sp.subscription_id LEFT JOIN Limits ON sp.value=Limits.id left join DomainsTraffic AS dtf on d.id=dtf.dom_id WHERE (sp.name='limitsId' OR sp.name IS NULL) AND (Limits.limit_name='expiration' OR Limits.limit_name is NULL) AND (Templates.type <> 'domain_addon' OR Templates.type IS NULL) AND d.id = ds.dom_id AND d.cl_id = cl.id AND ds.type = 'web' AND ds.ipCollectionId = ipc.ipCollectionId AND ip.id = ipc.ipAddressId group by d.id INTO OUTFILE './request.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'"
The .csv file will be saved in the folder defined by the datadir MySQL directive. This folder can be found using the command:
# plesk db "show variables like 'datadir'"
-
Comments
3 comments
Hey, thanks for the above SQL statement but I wondered if you could explain how to make that work for a specific time period.
It works :)
This is really useful. I do not know the database schema...could you revise the SQL query to look up the status code to what the actual status is? Like active, disabled, suspended, etc rather than the status ID number.
Please sign in to leave a comment.