I had to implement something similar, but only to the level of logging bandwidth per hour. What I did was set static IPs in chap-secrets for each incoming connection, then added some rules to iptables to count the data.
iptables -N vpn2fw
iptables -N fw2vpn
iptables -A INPUT -i ppp+ -j vpn2fw
iptables -A OUTPUT -o ppp+ -j fw2vpn
iptables -A vpn2fw -s 10.0.0.100/32
iptables -A fw2vpn -d 10.0.0.100/32
Note that these rules are abstracted from my shorewall configuration and may not be exactly correct.
Obviously changing 10.0.0.100 to the IP of the user's PPTP session. Then I wrote the following script which is run by a cronjob every minute (note, needs gawk):
#!/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
function getUsage {
local PARSE=""
while [ $# -ne 0 ]; do
PARSE="$PARSE$(iptables -nvx -L $1 && iptables -Z $1)"
shift;
done
echo "$PARSE" | gawk '
{
if ($7 ~ /10\.0\.0\.1\d*/) user=$7
else if ($8 ~ /10\.0\.0\.1\d*/) user=$8
else next;
out[user] += $2
} END {
for(user in out)
printf("%s %d\n", user, out[user]);
}'
}
function getUser {
cat /etc/ppp/chap-secrets | awk '{if ($4 == "'$1'") print $1}'
}
function logUsage {
local DATE="$1"
local TYPE="$2"
local DATA="$3"
local IFS=$'\n'
if [ "$TYPE" == "RX" ]; then
local QUERY="INSERT INTO vpn_usage (user, date, rx) VALUES ('%s', '%s', %d) ON DUPLICATE KEY UPDATE rx=rx+VALUES(rx)"
else
local QUERY="INSERT INTO vpn_usage (user, date, tx) VALUES ('%s', '%s', %d) ON DUPLICATE KEY UPDATE tx=tx+VALUES(tx)"
fi
for LINE in $DATA; do
USER=$(getUser $(echo $LINE | cut -d' ' -f1))
test -z "$USER" && continue;
printf "$QUERY\n" "$USER" "$DATE" "$(echo $LINE | cut -d' ' -f2)" | mysql -u root bandwidth
done
}
DATE="$(date '+%Y-%m-%d %H:00:00')"
TX="$(getUsage vpn2fw)"
RX="$(getUsage fw2vpn)"
logUsage "$DATE" "TX" "$TX"
logUsage "$DATE" "RX" "$RX"
After that I installed MySQL and created the following table:
CREATE TABLE `vpn_usage` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user` varchar(50) CHARACTER SET latin1 NOT NULL,
`date` datetime NOT NULL,
`rx` bigint(20) unsigned NOT NULL DEFAULT '0',
`tx` bigint(20) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `user` (`user`,`date`)
) ENGINE=InnoDB AUTO_INCREMENT=190 DEFAULT CHARSET=utf8