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
13 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.
Here, some arrows appeared... laughs
What does this mean? need any more commands? appreciate
Hi Jhoni Rosales
You don't need to enter MySQL to run these commands, "plesk db" will do this for you.
Just open a command prompt and execute the commands as-is.
Excelente.
Muito obrigado pela consistência da resposta, devo testá-lo hoje.
Tenho mais duas perguntas para você, ficarei muito grato se você respondê-las
1) Como faço para seguir suas postagens, gostaria de ver todas as suas postagens, mas este fórum diz que sua conta é privada. É assim mesmo ?
2) Como faço para entrar em contato com você?
Abraços muito agradecidos do Brasil
Great job
I'm trying to help the guys and generate a more complete query.
The two queries below work perfectly separately
Your query, on the other hand, can perfectly extract important traffic data
Domain Status ID Provider Customer "Customer Login" "ID Domain" Disk Plan Expiration "ID Customer" and more
The query below, obtains several information, about traffic
However.
When I try to join the two:
I get an error
This query would be very good for the community, without a doubt it would be the biggest and best so far. We will help if you can of course
Let's go friend. it's easy for you
Let's go friend. it's easy for you
Is it possible to Export WP information too?
Like Plugin XY ver2.6.0 (latest 2.6.2)
or only the Wordpressversion?
I dont understand the synax, or which fileds i can choose, is there a List?
Thanks all
a: Is it possible to get column headers in this export.
b: Can you please explain the possible status values, (I'm seeing numbers, 0, 2, 16, etc)
Thank you
Hi there Mendel Nemanov,
Thanks for your questions.
> a: Is it possible to get column headers in this export.
According to MySQL docs page, there is no turnkey solution for this.
However, the answers to this question on Stack Overflow may help.
> b: Can you please explain the possible status values, (I'm seeing numbers, 0, 2, 16, etc)
Sure, the status codes are explained in the KB article #213902805.
Hi,
Is there any way to update this to display all domains that use a mail subscription and the mail accounts that are assosiated with the domain?
Please sign in to leave a comment.