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