I have a working FreeRadius installation used for PPPOE clients using a Mikrotik NAS (Essentialy Linux)
I am using Freeradius to limit data a user can send/receive within a month and automatically reset it every month.
I used an example from chilliuspot hotspot for this.
However what im noticing is sometimes a customer gets denied access because he has exceeded he's monthly allowance, however when I check the
Db this is not the case cause it's the beginning of month.
This only happens sometimes so im lost!
I use sqlcounter.conf for the counter part of things and INCLUDE this from radius.conf.
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 - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0))+ SUM(AcctOutputOctets -GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
}
Anybody for input? I would greately appreciate help here! A Work around is to simply increase he's allowance till he is allowed to connect!
Here is an update. I have created an entirely new account that has NO history and yet I get same issue. Following in radius.log Wed Oct 1 17:51:46 2008 : Auth: Invalid user (rlm_sqlcounter: Maximum monthly usage time reached): [njale/<CHAP-Password>] (from client OldPPPOES port 40541 cli 00:0C:29:0B:44:66) I kept increasing the data limit via dialupadmin till it allowed me to connect. Ideas? __________ NOD32 3485 (20081001) Information __________ This message was checked by NOD32 antivirus system. http://www.eset.com
It complaining about time, not usage? Marco Marcel Grandemange wrote:
I have a working FreeRadius installation used for PPPOE clients using a Mikrotik NAS (Essentialy Linux)
I am using Freeradius to limit data a user can send/receive within a month and automatically reset it every month.
I used an example from chilliuspot hotspot for this.
However what im noticing is sometimes a customer gets denied access because he has exceeded he's monthly allowance, however when I check the
Db this is not the case cause it's the beginning of month.
This only happens sometimes so im lost!
I use sqlcounter.conf for the counter part of things and INCLUDE this from radius.conf.
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 - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0))+ SUM(AcctOutputOctets -GREATEST((%b - >UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
}
Anybody for input? I would greately appreciate help here! A Work around is to simply increase he's allowance till he is allowed to connect!
Here is an update... I have created an entirely new account that has NO history and yet I get same issue.
Following in radius.log
Wed Oct 1 17:51:46 2008 : Auth: Invalid user (rlm_sqlcounter: Maximum monthly usage time reached): [njale/<CHAP-Password>] (from client OldPPPOES port 40541 cli 00:0C:29:0B:44:66)
I kept increasing the data limit via dialupadmin till it allowed me to connect...
Ideas?
__________ NOD32 3485 (20081001) Information __________
This message was checked by NOD32 antivirus system. http://www.eset.com
------------------------------------------------------------------------
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
query = "SELECT SUM(AcctInputOctets - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0))+ SUM(AcctOutputOctets -GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
What is "GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)" doing in data (octet) counter? Ivan Kalik Kalik Informatika ISP
query = "SELECT SUM(AcctInputOctets - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0))+ SUM(AcctOutputOctets -GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
What is "GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)" doing in data (octet) counter?
No idea to be honest, im a novice. Many web-sites seem to advice using that and it works for the "most part" Eg.... http://cakeforge.org/forum/forum.php?forum_id=631 But advice is welcome!
Ivan Kalik Kalik Informatika ISP
No idea to be honest, im a novice. Many web-sites seem to advice using that and it works for the "most part"
OK. It's wrong. That part of the expression counts time not octets. It handles sessions that started in one counting period and continue into next one. Data is counted properly without such additions. Delete it. query = "SELECT SUM(AcctInputOctets + AcctOutputOctets) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'" Remove "+ AcctSessionTime" if you don't want sessions that started in one counting period and continuing into the next one to count in the new one. And tell Dirk and others where you found that information not to mix time and data counters. Ivan Kalik Kalik Informatika ISP
No idea to be honest, im a novice. Many web-sites seem to advice using that and it works for the "most part"
OK. It's wrong. That part of the expression counts time not octets. It handles sessions that started in one counting period and continue into next one. Data is counted properly without such additions. Delete it.
query = "SELECT SUM(AcctInputOctets + AcctOutputOctets) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
Thank You, Will Try This!
Remove "+ AcctSessionTime" if you don't want sessions that started in one counting period and continuing into the next one to count in the new one.
One counting period meaning beginning of month? 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? Please excuse my lack of knowledge! Really appreciate the help though!
And tell Dirk and others where you found that information not to mix time and data counters.
Ivan Kalik Kalik Informatika ISP
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. 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
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@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@thavinci /usr/local/etc/raddb]# edit sqlcounter.conf [root@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@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
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
Post the debug of request processing.
I have attached it as attachment because of size...
Ivan Kalik Kalik Informatika ISP
Your check has a negative value: check_item=-1073741824 Delete that minus. Of course everything counted will be greater than a negative number. It starts working on 5GB because the counter rolls over at 4GB. Ivan Kalik Kalik Informatika ISP Dana 2/10/2008, "Marcel Grandemange" <thavinci@thavinci.za.net> piše:
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
Post the debug of request processing.
I have attached it as attachment because of size...
Ivan Kalik Kalik Informatika ISP
Your check has a negative value:
check_item=-1073741824
Delete that minus. Of course everything counted will be greater than a negative number. It starts working on 5GB because the counter rolls over at 4GB.
Excuse the ignorance again, but where will I do that? Where is it getting that value from? I checked the radcheck table for that user ... ID UserName Attribute op Value 345 njale Max-Monthly-Traffic := 3221225472 He has it set on 3gb and gets denied, only when Value = 5368709120 does it work. Counter Rolls Over? Btw, thanks for being patient with me!
Ivan Kalik Kalik Informatika ISP
Your check has a negative value:
check_item=-1073741824
Delete that minus. Of course everything counted will be greater than a negative number. It starts working on 5GB because the counter rolls over at 4GB.
Excuse the ignorance again, but where will I do that? Where is it getting that value from? I checked the radcheck table for that user ...
ID UserName Attribute op Value 345 njale Max-Monthly-Traffic := 3221225472
He has it set on 3gb and gets denied, only when Value = 5368709120 does it work.
Counter Rolls Over?
Yes. It's a 32 bit number so it can go up only to 4GB. That is in the FAQ. Hm, it should wrap at 4GB but it wraps at 2GB. I think bug 490 relates to this. Ivan Kalik Kalik Informatika ISP
Your check has a negative value:
check_item=-1073741824
Delete that minus. Of course everything counted will be greater than a negative number. It starts working on 5GB because the counter rolls over at 4GB.
Excuse the ignorance again, but where will I do that? Where is it getting that value from? I checked the radcheck table for that user ...
ID UserName Attribute op Value 345 njale Max-Monthly-Traffic := 3221225472
He has it set on 3gb and gets denied, only when Value = 5368709120 does it work.
Counter Rolls Over?
Yes. It's a 32 bit number so it can go up only to 4GB. That is in the FAQ.
Hence Why I Use: reply-name = Mikrotik-Xmit-Limit-Gigawords Also this explains more detail my kind of setup: http://forum.mikrotik.com/viewtopic.php?f=6&t=9902 I do interim updates, and haven't noticed the issue you are describing. I tested an account that was limited to 7Gb and worked successfully. (All 7Gb Was tranfered in one session) User was not able to reconnect till I upped the limit.
Hm, it should wrap at 4GB but it wraps at 2GB. I think bug 490 relates to this.
This issue only happens on rare occasions and with this particular user, I do not know what could be causing... I think I might test this whole thing manually gig for gig again... Input Anyone?
Ivan Kalik Kalik Informatika ISP
participants (4)
-
Alan DeKok -
Marcel Grandemange -
Marco C. Coelho -
tnt＠kalik.net