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:
# 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
4 comments
Queries output incomplete lists. At least domains that are redirected don't show up in the output list.
The query also does not extract domain aliases
Can you give me the literal statuses for the values in the “status” field? I have values like 0, 16, 64, etc for which I'd like to know if it's “active”, “disabled”, “suspended”.
Walter, the list of domain code statuses can be found here: https://support.plesk.com/hc/en-us/articles/12377968989463-What-do-the-domain-status-codes-in-Plesk-mean
Please sign in to leave a comment.