How to get a traffic usage report for a custom period in Plesk?

Follow

Comments

3 comments

  • Avatar
    Nan Dee

    Base on query #2, how can we specify to only display sites with 0 traffic from all cases (http_in, http_out, ftp_in, ftp_out, smtp_in, smtp_out, pop3_impa_in, pop3_imap_out) of a required period?

    0
    Comment actions Permalink
  • Avatar
    Nikita Nikushkin

    Hi @Nan Dee,

    Domains with all 0 stats for the specific period are not present in the "DomainsTraffic" table. If the domain receives any kind of traffic, then after Daily Task execution, the "DomainsTraffic" table is updated with the corresponding traffic type for the required date

    So, to get a list of domains which have no any kind of traffic for the required period, the next request can be used:

    # plesk db "select name from domains where id not in (select distinct dom_id from DomainsTraffic where date>='2017-03-01' AND date<='2019-03-20')"

    Do not forget to change the dates to the correct one

    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

    The query below, obtains several information

    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'"

     

    Your query, on the other hand, can perfectly extract important traffic data

    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.

Have more questions? Submit a request