Counter SQL Calculation

[Neville]

Can anyone please help on this as I've googled as cannot find a solution to 
the issue I've outlined below.

Thx
Nev

> 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
>>