FW: FreeRadius

[Marcel Grandemange]

>
>One counting period meaning beginning of month?
>

>Month, week, day, doesn't matter. Query is the same for all.

>So it should be:
>
>query = "SELECT SUM(AcctInputOctets + AcctOutputOctets) FROM radacct
>WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) > '%b'"
>
>If I want to allow users say 1Gb per month limit that gets reset every
>month.
>(In other words no cary over of data)
>
>And:
>query = "SELECT SUM(AcctInputOctets + AcctOutputOctets) FROM radacct
>WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) +
>AcctSessionTime > '%b'"
>
>If I want them to be able to use there unused data from previous month +
new
>data limit?

>No. Not unused but data used in a session that started in one period and
>continued into the next one. Lets say today is the last of the month.
>User starts the session today and finishes tomorrow morning. Is that
>data counting towards the new months limit? Or not? If it does leave
>AcctSessionTime in, if it doesn't - take it out.

Thank you, did test it not fully but isn't resolving my issue im afraid...

A User njale has a limit of 3Gb.
I check via dialupadmin from begging of this month to today

Page Total	17 hours, 2 minutes, 8 seconds 	88.01 MBs 	274.39 MBs


Hence he has done 362.3Mb

I made the change to my sqlcounter.conf
Restarted radius.
Ensured njele's limit is indeed on 3Gb.
He gets denied....

"sqlcounter monthlytraffic {
                 counter-name = Monthly-Traffic
                 check-name = Max-Monthly-Traffic
                 reply-name = Mikrotik-Xmit-Limit-Gigawords
                 sqlmod-inst = sql
                 key = User-Name
                 reset = monthly
#query = "SELECT (sum(AcctInputOctets)+sum(AcctOutputOctets)) FROM radacct
WHERE UserName='%{%k}' AND Month(AcctStopTime) =(Month(NOW())-1) AND
Year(Acct
#query = "SELECT SUM(AcctInputOctets - GREATEST((%b -
UNIX_TIMESTAMP(AcctStartTime)), 0))+ SUM(AcctOutputOctets -GREATEST((%b -
UNIX_TIMESTAMP(AcctStartT
query = "SELECT SUM(AcctInputOctets + AcctOutputOctets) FROM radacct WHERE
UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) > '%b'"
}



"sqlcounter.conf" 73 lines, 3220 characters
[root at thavinci /usr/local/etc/raddb]# /usr/local/etc/rc.d/radiusd restart
Stopping radiusd.
Waiting for PIDS: 7656.
Starting radiusd.
Thu Oct  2 13:11:42 2008 : Info: Starting - reading configuration files ...
[root at thavinci /usr/local/etc/raddb]# edit sqlcounter.conf
[root at thavinci /usr/local/etc/raddb]# tail -f /var/log/radius.log
Thu Oct  2 13:11:43 2008 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #9
Thu Oct  2 13:11:43 2008 : Info: Ready to process requests.
Thu Oct  2 13:12:18 2008 : Auth: Login incorrect:
[TEST/\025\002\202\216\241\253\244C\221Q\202\027\275\203=(] (from client
OldPPPOES port 0)
Thu Oct  2 13:12:40 2008 : Auth: Login OK: [njale/<CHAP-Password>] (from
client OldPPPOES port 40587 cli 00:0C:29:0B:44:66)
Thu Oct  2 13:13:18 2008 : Auth: Login incorrect:
[TEST/\025\002\202\216\241\253\244C\221Q\202\027\275\203=(] (from client
OldPPPOES port 0)
Thu Oct  2 13:13:53 2008 : Auth: Invalid user (rlm_sqlcounter: Maximum
monthly usage time reached): [njale/<CHAP-Password>] (from client OldPPPOES
port 40588 cli 00:0C:29:0B:44:66)
Thu Oct  2 13:13:58 2008 : Auth: Login OK: [00:80:48:46:E9:CF/] (from client
pbwexnetworkvpn port 0 cli 00-80-48-46-E9-CF)
Thu Oct  2 13:14:02 2008 : Auth: Login OK: [00:80:48:46:E9:CF/] (from client
pbwexnetworkvpn port 0 cli 00-80-48-46-E9-CF)
Thu Oct  2 13:14:04 2008 : Auth: Invalid user (rlm_sqlcounter: Maximum
monthly usage time reached): [njale/<CHAP-Password>] (from client OldPPPOES
port 40589 cli 00:0C:29:0B:44:66)
Thu Oct  2 13:14:18 2008 : Auth: Login incorrect:
[TEST/\025\002\202\216\241\253\244C\221Q\202\027\275\203=(] (from client
OldPPPOES port 0)
^C
[root at thavinci /usr/local/etc/raddb]#

"

Only increasing hes limit to 5Gb Allows him to connect....





>If you want to carry over unused allowance you alter the check-name
>parameter. Instead of that value being constant (1GB in your example)
>you would add whatever is left from previous month and insert that as
>the value for the counter.

>Ivan Kalik
>Kalik Infromatika ISP