Counter SQL Calculation

[Neville]

Hi Everyone,

Here is some Debug if anyone can help explain or correct the 
[monthlytraffic] Counter calculation.

Sat Oct 30 22:39:39 2010 : Info: [monthlytraffic]       expand: SELECT 
IFNULL((sum(acctinputoctets)+sum(acctoutputoctets)),0) FROM radacct WHERE 
username='%{User-Name}' AND Month(acctstoptime) =(Month(NOW())) AND 
Year(acctstoptime) = Year(NOW()) -> SELECT 
IFNULL((sum(acctinputoctets)+sum(acctoutputoctets)),0) FROM radacct WHERE 
username='FTU-GzwgcD' AND Month(acctstoptime) =(Month(NOW())) AND 
Year(acctstoptime) = Year(NOW())
Sat Oct 30 22:39:39 2010 : Debug: sqlcounter_expand:  '%{sql:SELECT 
IFNULL((sum(acctinputoctets)+sum(acctoutputoctets)),0) FROM radacct WHERE 
username='FTU-GzwgcD' AND Month(acctstoptime) =(Month(NOW())) AND 
Year(acctstoptime) = Year(NOW())}'
Sat Oct 30 22:39:39 2010 : Info: [monthlytraffic] sql_xlat
Sat Oct 30 22:39:39 2010 : Info: [monthlytraffic]       expand: 
%{User-Name} -> FTU-GzwgcD
Sat Oct 30 22:39:39 2010 : Info: [monthlytraffic] sql_set_user escaped 
user --> 'FTU-GzwgcD'
Sat Oct 30 22:39:39 2010 : Info: [monthlytraffic]       expand: SELECT 
IFNULL((sum(acctinputoctets)+sum(acctoutputoctets)),0) FROM radacct WHERE 
username='FTU-GzwgcD' AND Month(acctstoptime) =(Month(NOW())) AND 
Year(acctstoptime) = Year(NOW()) -> SELECT 
IFNULL((sum(acctinputoctets)+sum(acctoutputoctets)),0) FROM radacct WHERE 
username='FTU-GzwgcD' AND Month(acctstoptime) =(Month(NOW())) AND 
Year(acctstoptime) = Year(NOW())
Sat Oct 30 22:39:39 2010 : Debug: rlm_sql (sql): Reserving sql socket id: 4
Sat Oct 30 22:39:39 2010 : Info: [monthlytraffic] sql_xlat finished
Sat Oct 30 22:39:39 2010 : Debug: rlm_sql (sql): Released sql socket id: 4
Sat Oct 30 22:39:39 2010 : Info: [monthlytraffic]       expand: %{sql:SELECT 
IFNULL((sum(acctinputoctets)+sum(acctoutputoctets)),0) FROM radacct WHERE 
username='FTU-GzwgcD' AND Month(acctstoptime) =(Month(NOW())) AND 
Year(acctstoptime) = Year(NOW())} -> 991187
Sat Oct 30 22:39:39 2010 : Debug: rlm_sqlcounter: Check item is greater than 
query result
Sat Oct 30 22:39:39 2010 : Debug: rlm_sqlcounter: Authorized user 
FTU-GzwgcD, check_item=262100000, counter=991187
Sat Oct 30 22:39:39 2010 : Debug: rlm_sqlcounter: Sent Reply-Item for user 
FTU-GzwgcD, Type=Session-Octets-Limit, value=262191221
Sat Oct 30 22:39:39 2010 : Info: ++[monthlytraffic] returns ok


The Important bit is that the counter returns 991187, but then the 
Reply-Item "Session-Octets-Limit" is set to 262191221, which is actually an 
INCREASE of 91221, how is this calculation CORRECT?

Thx
Nev


>
>> Hi everyone,
>>
>> I have a small problem where the counter is not working how I would like 
>> it two work.
>>
>> sqlcounter monthlytraffic {
>>         counter-name = Monthly-Traffic
>>                check-name = Max-Monthly-Traffic
>>                reply-name = Session-Octets-Limit
>>                sqlmod-inst = sql
>>                key = User-Name
>>                reset = monthly
>>         query = "SELECT 
>> IFNULL((sum(acctinputoctets)+sum(acctoutputoctets)),0) FROM radacct WHERE 
>> username='%{%k}' AND Month(acctstoptime) =(Month(NOW())) AND 
>> Year(acctstoptime) = Year(NOW())"
>> }
>>
>> The problem with this, is that it the SELECT statement returns a value 
>> Less than the value of Max-Monthly-Traffic, then sets 
>> Session-Octets-Limit is set to equal Max-Monthly-Traffic.
>>
>> What I need it to do is to populate Session-Octets-Limit with the VALUE 
>> of Max-Monthly-Traffic, then subtract the VALUE of the Select Statement.
>>
>> E.G. if Max-Monthy-Traffic is set to 250Mb or 262100000, and the SELECT 
>> returns a result of 52430000 being 50Mb of usage, then 
>> Session-Octets-Limit should be set to 262100000 - 5230000 being 256870000
>>
>> Can anyone point in the right direction on this please.
>>
>> Thx
>> Nev
>