Applicable to:
- Plesk for Linux
Question
How to get a traffic usage report for a custom period in Plesk?
Answer
There is no such tool in Plesk interface that allows filtering traffic usage by date. Suggest such feature in our UserVoice portal.
The top-ranked suggestions are likely to be implemented in next versions of Plesk.
As a workaround, this data can be acquired manually from the database:
-
Connect to the server using SSH.
-
Execute the following command to get the list of domains and its traffic usage in MB for the required period:
Note: change dates to the correct one in the command below.
# 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;"
-
The next query will return the list of domains which have no traffic for the required time:
Note: change dates to the correct one in the command below.
# plesk db "select name from domains where id not in (select distinct dom_id from DomainsTraffic where date>='2019-01-01' AND date<='2019-12-26')"
-
The following SQL request will provide traffic usage in MB for the required period and for the specific service plan:
Note: change dates and service plan name to the correct one in the command below.
# plesk db "SELECT round(SUM(http_in + ftp_in + smtp_in + pop3_imap_in + http_out + ftp_out + smtp_out + pop3_imap_out)/1024/1024,2) as 'Total traffic usage (MB)' FROM DomainsTraffic as dt LEFT JOIN domains AS d ON d.id = dt.dom_id LEFT JOIN clients AS c ON c.id = d.vendor_id LEFT JOIN Subscriptions AS s ON s.object_type = 'client' AND c.id = s.object_id LEFT JOIN PlansSubscriptions AS ps ON ps.subscription_id = s.id LEFT JOIN Templates AS t ON t.id = ps.plan_id WHERE (c.type = 'reseller') AND (t.name = 'Default') AND (dt.date between '2019-01-01' and '2019-12-26');"
Comments
4 comments
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?
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
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
Your query, on the other hand, can perfectly extract important traffic data
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
Great queries!
Just one thing, the query to get the list of domains and its traffic usage in MB for the required period, doesn't seem to catch HTTP_IN. No matter what time period I use the result is 0 for all domains.
Rgds
Dave_W
Please sign in to leave a comment.