Hi Arran, I think i have managed to make the datacounter working. It may not be the best counter but it is the best i have ever done in freeradius. Below posted is the configs :
Post-Auth { sql # Unlang Data-Counter. Sends Mikrotik-Recv-Limit to NAS update control { Tmp-Integer-0 = "%{sql:SELECT ((SELECT tbl_groupcheck.value from tbl_groupcheck \ JOIN tbl_usergroup on tbl_groupcheck.groupname = tbl_usergroup.groupname \ WHERE tbl_usergroup.username = '%{User-Name}') > (SELECT IFNULL(SUM(AcctInputOctets) \ +SUM(AcctOutputOctets),0) FROM tbl_acct WHERE UserName='%{User-Name}' \ AND MONTH(acctstoptime) = MONTH(NOW()) AND YEAR(acctstoptime) = YEAR(NOW())))}" Tmp-Integer-1 = "%{sql:SELECT ((SELECT tbl_groupcheck.value from tbl_groupcheck \ JOIN tbl_usergroup on tbl_groupcheck.groupname = tbl_usergroup.groupname \ WHERE tbl_usergroup.username = '%{User-Name}') - (SELECT IFNULL(SUM(AcctInputOctets) \ +SUM(AcctOutputOctets),0) FROM tbl_acct WHERE UserName='%{User-Name}' \ AND MONTH(acctstoptime) = MONTH(NOW()) AND YEAR(acctstoptime) = YEAR(NOW())))}" } if ("%{control:Tmp-Integer-0}" == "1") { update reply { Mikrotik-Recv-Limit := "%{control:Tmp-Integer-1}" } } if ("%{control:Tmp-Integer-0}" == "0") { update reply { Reply-Message := "Fair Usage Policy Enforced, Bandwidth Limited" Mikrotik-Rate-Limit := "128K/256K 128K/256K 128K/256K 180/180 8" } } The caveats : It will return a negative value if Max-used-Traffic is more than Max-Monthly-Limit but we don't need that negative value as we will enforce Mikrotik-Rate-Limit (i.e Fair Usage Policy) If Max-Monthly-Limit - Max-used-Limit > 32bit Integer, The Mikrotik-Recv-Limit will be wrapped and user will have a rough of 2GB per session limit. If user disconnects again and connects , the same thing applies. However, user will be able to use 100% of Max-Monthly-Traffic allocated in multiple sessions. I hope someone can make a hybrid of this counter. Regards Suman On Mon, Aug 8, 2011 at 8:04 PM, Arran Cudbard-Bell <a.cudba...@freeradius.org> wrote: > > On 8 Aug 2011, at 16:29, Suman Dash wrote: > >> Just another small question before i jump into testing. If output from >> sub-query is less than 32bit, I can easily store it in Tmp-Integer , >> But sometimes when the user data usage is null, the sub-query will >> output more than 32bit ex. 10GB Limit But user downloaded 0 Bytes. >> >> In that condition it is impossible to store it in Tmp-Integer . So >> ultimately the Integer passed by xlat and the stored in Tmp-Integer >> will differ. > > Yes. I'd imagine it'd be truncated. > > -Arran > > > - > List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html > - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html