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

• 

  Gareth Westwood February 17, 2021 12:02

  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
• 

  Javaid Asharf July 23, 2021 16:58

  It works :)

  0

  Comment actions Permalink
• 

  EONI NOC July 27, 2021 00:42 (Edited July 27, 2021 00:58)

  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
• 

  Jhoni Rosales December 24, 2021 05:14 (Edited December 24, 2021 05:15)

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

  0

  Comment actions Permalink
• 

  Kuzma Ivanov December 30, 2021 09:28

  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
• 

  Jhoni Rosales January 03, 2022 22:19

  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
• 

  Jhoni Rosales January 13, 2022 04:26

  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

Please sign in to leave a comment.