Re: 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=26210, 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 26210, and the SELECT 
returns a result of 5243 being 50Mb of usage, then 
Session-Octets-Limit should be set to 26210 - 523 being 25687


Can anyone point in the right direction on this please.

Thx
Nev





-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: FAQ and Wiki down?

[Alexander Clouter]

"Sallee, Stephen (Jake)"  wrote:
> 
> I feel your pain, we have the same thing happen form time to time.
> Check with your ISP, when it happens to us it is usually their DNS
> server caching and old entry or a bad statement in one of their routers.
>
Sometimes hostmaster@ should receive some pain for misconfiguration:

a...@chipmunk:~$ dig +trace NS freeradius.org
[snipped]
freeradius.org. 86400   IN  NS  ns3.freeradius.org.
freeradius.org. 86400   IN  NS  ns5.freeradius.org.
freeradius.org. 86400   IN  NS  ns6.freeradius.org.
;; Received 134 bytes from 2001:500:48::1#53(b2.org.afilias-nst.org) in 103 ms
[snipped]

a...@chipmunk:~$ host -t NS freeradius.org ns3.freeradius.org
;; connection timed out; no servers could be reached
a...@chipmunk:~$ host -t NS freeradius.org ns6.freeradius.org
;; connection timed out; no servers could be reached
a...@chipmunk:~$ host -t NS freeradius.org ns5.freeradius.org
Using domain server:
Name: ns5.freeradius.org
Address: 88.191.76.128#53
Aliases: 

freeradius.org name server ns6.freeradius.org.
freeradius.org name server ns5.freeradius.org.


For some reason freeradius has three NS servers being spat out by the 
.org TLD authority boxen, you ask the *only* responding authority server 
for freeradius.org and it comes back that there are only two authoritative 
servers for the zone. :)

If this is a recent breakage, it's possible either ns3 or ns6 gave back 
something interesting (NXDOMAIN for example) which have be cached by 
your resolver just before it completely went down.

Either way, freeradius.org probably could do with more than one 
authoritive DNS server :)

Cheers

-- 
Alexander Clouter
.sigmonster says: People are unconditionally guaranteed to be full of defects.

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html