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