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

Follow

Comments

10 comments

  • Avatar
    Gareth Westwood

    Hey, thanks for the above SQL statement but I wondered if you could explain how to make that work for a specific time period.

    0
    Comment actions Permalink
  • Avatar
    Javaid Asharf

    It works :)

    0
    Comment actions Permalink
  • Avatar
    EONI NOC (Edited )

    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.

    0
    Comment actions Permalink
  • Avatar
    Jhoni Rosales (Edited )

    Here, some arrows appeared... laughs
    What does this mean? need any more commands? appreciate

    0
    Comment actions Permalink
  • Avatar
    Kuzma Ivanov

    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.

    1
    Comment actions Permalink
  • Avatar
    Jhoni Rosales

    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

    0
    Comment actions Permalink
  • Avatar
    Jhoni Rosales

    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


    plesk db "SELECT d.name AS 'Domain', d.status AS 'Status', cl.id AS 'Client ID', cl.pname AS 'Name', cl.login AS 'Login', d.id AS 'Domain ID', 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 './Plesk_Estatistica.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'"

     

    The query below, obtains several information, about traffic

    plesk db "select domains.name, round(sum(DomainsTraffic.http_in)/1024/1024,2) as 'HTTP_IN (MB)', round(sum(DomainsTraffic.http_out)/1024/1024,2) as 'HTTP_OUT (MB)', round(sum(DomainsTraffic.ftp_in)/1024/1024,2) as 'FTP_IN (MB)', round(sum(DomainsTraffic.ftp_out)/1024/1024,2) as 'FTP_OUT (MB)', round(sum(DomainsTraffic.smtp_in)/1024/1024,2) as 'SMTP_IN (MB)', round(sum(DomainsTraffic.smtp_out)/1024/1024,2) as 'SMTP_OUT (MB)', round(sum(DomainsTraffic.pop3_imap_in)/1024/1024,2) as 'POP3_IMAP_IN (MB)', round(sum(DomainsTraffic.pop3_imap_out)/1024/1024,2) as 'POP3_IMAP_OUT (MB)' from domains, DomainsTraffic where id=dom_id AND date>='2019-01-01' AND date<='2019-12-26' GROUP BY dom_id INTO OUTFILE './Plesk_Trafego.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'"

     

    However.
    When I try to join the two:


    plesk db "SELECT domains.name, round(sum(DomainsTraffic.http_in)/1024/1024,2) as 'HTTP_IN (MB)', round(sum(DomainsTraffic.http_out)/1024/1024,2) as 'HTTP_OUT (MB)', round(sum(DomainsTraffic.ftp_in)/1024/1024,2) as 'FTP_IN (MB)', round(sum(DomainsTraffic.ftp_out)/1024/1024,2) as 'FTP_OUT (MB)', round(sum(DomainsTraffic.smtp_in)/1024/1024,2) as 'SMTP_IN (MB)', round(sum(DomainsTraffic.smtp_out)/1024/1024,2) as 'SMTP_OUT (MB)', round(sum(DomainsTraffic.pop3_imap_in)/1024/1024,2) as 'POP3_IMAP_IN (MB)', round(sum(DomainsTraffic.pop3_imap_out)/1024/1024,2) as 'POP3_IMAP_OUT (MB)' from domains, DomainsTraffic where id=dom_id AND date>='2019-01-01' AND date<='2019-12-26' d.name AS 'Domain', d.status AS 'Status', cl.id AS 'Client ID', cl.pname AS 'Name', cl.login AS 'Login', d.id AS 'Domain ID', 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 './Plesk_Estatistica.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'"

     

    I get an error

    ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'd.name AS 'Domain', d.status AS 'Status', cl.id AS 'Client ID', cl.pname AS '...' at line 1

    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

     

    0
    Comment actions Permalink
  • Avatar
    Jhoni Rosales

    Let's go friend. it's easy for you

    0
    Comment actions Permalink
  • Avatar
    Jhoni Rosales

    Let's go friend. it's easy for you

    0
    Comment actions Permalink
  • Avatar
    Daniel Braunmüller

    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

    0
    Comment actions Permalink

Please sign in to leave a comment.

Have more questions? Submit a request