Counter SQL Calculation

[Neville]

Anyone got any pointers at all on this one?

Thx
Nev
----- Original Message ----- 
From: "Neville" <nev at itsnev.co.uk>
To: <freeradius-users at lists.freeradius.org>
Sent: Wednesday, October 20, 2010 5:14 PM
Subject: Counter SQL Calculation


> 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