Articles in this section

See more

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

Avatar
Kuzma Ivanov
Updated
Plesk for Windows kb: how-to Plesk for Linux ABT: Group B

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:

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

  2. 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

6 comments

Sort by Date Votes
  • Avatar
    Manuel Costa

    Queries output incomplete lists. At least domains that are redirected don't show up in the output list.

    1
  • Avatar
    Alessio De Simone

    The query also does not extract domain aliases

    0
  • Avatar
    Alcor WALTER

    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”.

    0
  • Avatar
    Gilson Siqueira

    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 

    0
  • Avatar
    Frank Heilmann

    How to extract full list of domains including alias, regards and thanks.

    0
  • Avatar
    Gilson Siqueira

    Hi Frank Heilmann , if you literally need a list of domains and aliases with no differentiation between each other, here's how you do it:

    plesk db "SELECT name FROM domains UNION SELECT name FROM domain_aliases ORDER BY name"

    Now, if you need to know which is which, the query is slightly different:

    plesk db "SELECT name, 'Main' AS type FROM domains UNION SELECT name, 'Alias' AS type FROM domain_aliases ORDER BY name"

    Where "Main" is a domain and "Alias" is an alias. Feel free to change such words in the query if necessary.

    If you want just a list of aliases you can run this:

    plesk db "select name from domain_aliases"

    If you need further clarification or assistance, please submit a ticket to Plesk Support.

    1

Please sign in to leave a comment.

Have more questions? Submit a request

Related articles