Mysql | Bitwise Gamgee

How to get traffic details of a domain in Plesk Expand via MySQL

March 19th, 2009

No comments

As you might know, Plesk Expand does not limit traffic of domains in any way. So if you want to charge your customers for extra traffic with plain Plesk and Expand, you should do some extra work. As there are no API commands to get traffic details of a domain hosted on Plesk, you have to get your hands dirty and query these data from Plesk Expand MySQL.

First, you have to establish a connection to MySQL, you have to get login credentials from Plesk Expand config file. In my Plesk Expand server, this file is found in /usr/local/expand/conf/expand.conf . In this file you can find the database name, username and password.

[root@exp ~]# more /usr/local/expand/conf/expand.conf
 
################### Database settings
[DBConnection]
# Database name
db=expand
# Database location
host=localhost
# Database credentials
user=expand
password=xxxxxxxxxx

Now you can connect to mysql via the command below.

1 2	[root@exp ~]# /usr/bin/mysql -uexpand -p Enter password:

Just enter the password you acquired in previous step and you are authenticated. Now as the database name is “expand”, you have to switch to that database

mysql> use expand;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

Now as you are in, you can enter queries to get the data you need. There are 4 tables which are important for us right now.

plesk_domain
plesk_domain_stat
exp_mail_traffic
plesk_domain_limit

You can query the traffic limit of domain via:

SELECT plesk_domain.id, plesk_domain.name, plesk_domain_limit.value / 1048576 as max_traffic
FROM plesk_domain, plesk_domain_limit
WHERE plesk_domain.id = plesk_domain_limit.domain_id
AND limit_name = 'max_traffic'
AND plesk_domain_limit.value > 0
AND plesk_domain.name = 'testdomain.com';

You can query the total traffic that domain made in current month via:

SELECT plesk_domain.id, plesk_domain.name, plesk_domain_stat.traffic / 1048576 as traffic
FROM plesk_domain, plesk_domain_stat
WHERE plesk_domain.id = plesk_domain_stat.domain_id
AND plesk_domain.name = 'testdomain.com';

But this total traffic includes Web, FTP and SMTP/POP3 traffics. If you want to get pure Web and FTP traffic you should extract mail traffic from this value. You can query the SMTP and POP traffic that domain made in the month stated in query via:

SELECT plesk_domain.id, plesk_domain.name, ifnull((select sum(exp_mail_traffic.smtp_out) + sum(exp_mail_traffic.pop3_imap_out)
from exp_mail_traffic where exp_mail_traffic.domain_id = plesk_domain.id and exp_mail_traffic.date >= '2009-03-01' and exp_mail_traffic.date < '2009-04-01'),0) / 1048576 as mail_traffic
FROM plesk_domain
WHERE plesk_domain.name = 'testdomain.com';

And if you combine them altogether, here is a query that fetches the domains that has traffic limits and which total FTP and Web traffic usage exceeds 90% of their allowed limit for the given date (March of 2009 in this case).

SELECT plesk_domain.id, plesk_domain.name, plesk_domain_stat.traffic / 1048576 as traffic, plesk_domain_limit.value / 1048576 as max_traffic,
ifnull((select sum(exp_mail_traffic.smtp_out) + sum(exp_mail_traffic.pop3_imap_out)
from exp_mail_traffic where exp_mail_traffic.domain_id = plesk_domain_stat.domain_id and exp_mail_traffic.date >= '2009-03-01' and exp_mail_traffic.date < '2009-04-01'),0) / 1048576 as mail_traffic
FROM plesk_domain, plesk_domain_limit, plesk_domain_stat
WHERE plesk_domain.id = plesk_domain_limit.domain_id
AND plesk_domain.id = plesk_domain_stat.domain_id
AND plesk_domain_stat.traffic - ifnull((select sum(exp_mail_traffic.smtp_out) + sum(exp_mail_traffic.pop3_imap_out)
from exp_mail_traffic where exp_mail_traffic.domain_id = plesk_domain_stat.domain_id and exp_mail_traffic.date >= '2009-03-01' and exp_mail_traffic.date < '2009-04-01'),0) > (plesk_domain_limit.value * 0.9)
AND limit_name = 'max_traffic'
AND plesk_domain_limit.value > 0
ORDER BY plesk_domain.name ASC;

This query can be used to warn customers about their traffic approaching its limits. And you can use the query below to get the domains exceeding their traffic limits.

SELECT plesk_domain.id, plesk_domain.name, plesk_domain_stat.traffic / 1048576 as traffic, plesk_domain_limit.value / 1048576 as max_traffic,
ifnull((select sum(exp_mail_traffic.smtp_out) + sum(exp_mail_traffic.pop3_imap_out)
from exp_mail_traffic where exp_mail_traffic.domain_id = plesk_domain_stat.domain_id and exp_mail_traffic.date >= '2009-03-01' and exp_mail_traffic.date < '2009-04-01'),0) / 1048576 as mail_traffic
FROM plesk_domain, plesk_domain_limit, plesk_domain_stat
WHERE plesk_domain.id = plesk_domain_limit.domain_id
AND plesk_domain.id = plesk_domain_stat.domain_id
AND plesk_domain_stat.traffic - ifnull((select sum(exp_mail_traffic.smtp_out) + sum(exp_mail_traffic.pop3_imap_out)
from exp_mail_traffic where exp_mail_traffic.domain_id = plesk_domain_stat.domain_id and exp_mail_traffic.date > '2009-03-01' and exp_mail_traffic.date < '2009-04-01'),0) > plesk_domain_limit.value
AND limit_name = 'max_traffic'
AND plesk_domain_limit.value > 0
ORDER BY plesk_domain.name ASC;