Counter SQL Calculation

Neville nev at itsnev.co.uk
Wed Oct 20 18:14:12 CEST 2010


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 





More information about the Freeradius-Users mailing list