Hi everyone,
I'm trying to setup a new counter maxmonthlytraffic, but
as soon as I connected, sql_counter sends reply to do a session timout and I get
disconnected.
This is what I've done so far...
I've added to ./raddb/sql/mysql/counter.conf
sqlcounter monthlytraffic
{
counter-name =
Monthly-Traffic
check-name =
Max-Monthly-Traffic
sqlmod-inst =
sql
key =
User-Name
reset = monthly
query = "SELECT (sum(acctinputoctets)+sum(acctoutputoctets))
\
FROM radacct WHERE username='%{%k}' AND
\
Month(acctstoptime) =(Month(NOW())) AND
\
Year(acctstoptime) = Year(NOW())"
}
authorize {
..
monthlytraffic
}
instantiate {
monthlytraffic
}
created a dictionary entry in daloradius as..
| id |
9433 |
| Type |
integer |
| Attribute |
Max-Monthly-Traffic |
| Value |
NULL |
| Format |
NULL |
| Vendor |
dictionary.freeradius.internal |
| RecommendedOP |
:= |
| RecommendedTable |
check |
| RecommendedHelper |
|
| RecommendedTooltip |
Check Monthly Traffic
Allowance |
User created as "testmaxm", with the following attributes
set:-
Check
Simultaneous-Use := 1
Pool-Name := tvpool
Cleartext-Password := testmaxm
Max-Monthly-Traffic := 10490000 (10Mb) (If
this is removed from the Check, the user connects fine, so everything else is
working)
Reply
Framed-MTU = 1400
Framed-Protocol = PPP
Service-Type = Framed-User
Acct-Interim-Interval := 300 (Every 5
mins for testing)
Some Debug...
rlm_sqlcounter: Check item is greater than query
result
rlm_sqlcounter: Authorized user testmaxm, check_item=10490000,
counter=80411
rlm_sqlcounter: Sent Reply-Item for user testmaxm,
Type=Session-Timeout, value=11601138
++[monthlytraffic] returns
ok
rad_recv: Accounting-Request packet from host
aaa.bbb.ccc.ddd port 53637, id=47,
length=140
Acct-Session-Id =
"4A8B6FA0721900"
User-Name =
"testmaxm"
Acct-Status-Type =
Interim-Update
Service-Type =
Framed-User
Framed-Protocol =
PPP
Acct-Authentic =
RADIUS
Acct-Session-Time =
600
Acct-Output-Octets =
37033544
Acct-Input-Octets =
906612
Acct-Output-Packets =
27837
Acct-Input-Packets =
15791
NAS-Port-Type =
Async
Framed-IP-Address =
192.168.0.29
NAS-Identifier =
"aaa.bbb.ccc.ddd"
NAS-Port =
1
Acct-Delay-Time = 0
+-
entering group preacct {...}
++[preprocess] returns ok
[acct_unique]
Hashing 'NAS-Port = 1,Client-IP-Address = 193.33.186.190,NAS-IP-Address =
aaa.bbb.ccc.ddd,Acct-Session-Id = "4A8B6FA0721900",User-Name =
"testmaxm"'
[acct_unique] Acct-Unique-Session-ID =
"049e959019a363e4".
++[acct_unique] returns ok
[suffix] No '@'
in User-Name = "testmaxm", looking up realm NULL
[suffix] No such realm
"NULL"
++[suffix] returns noop
+- entering group accounting
{...}
[detail] expand:
/var/log/radius/radacct/%{Client-IP-Address}/detail-%Y%m%d ->
/var/log/radius/radacct/aaa.bbb.ccc.ddd/detail-20090819
[detail]
/var/log/radius/radacct/%{Client-IP-Address}/detail-%Y%m%d expands to
/var/log/radius/radacct/aaa.bbb.ccc.ddd/detail-20090819
[detail]
expand: %t -> Wed Aug 19 03:31:04 2009
++[detail] returns ok
rlm_sql
(sql): Reserving sql socket id: 1
[sqlippool] expand:
%{User-Name} -> testmaxm
[sqlippool] sql_set_user escaped user -->
'testmaxm'
[sqlippool] expand: START TRANSACTION
-> START TRANSACTION
rlm_sql_mysql: query: START
TRANSACTION
[sqlippool] expand: UPDATE
radippool SET expiry_time = NOW() + INTERVAL 3600 SECOND WHERE
nasipaddress = '%{Nas-IP-Address}' AND pool_key = '%{NAS-Port}' AND
username = '%{User-Name}' AND callingstationid =
'%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}' ->
UPDATE radippool SET expiry_time = NOW() + INTERVAL 3600 SECOND
WHERE nasipaddress = 'aaa.bbb.ccc.ddd' AND pool_key = '1' AND username =
'testmaxm' AND callingstationid = '' AND framedipaddress =
'192.168.0.29'
rlm_sql_mysql: query: UPDATE radippool SET
expiry_time = NOW() + INTERVAL 3600 SECOND WHERE nasipaddress =
'aaa.bbb.ccc.ddd' AND pool_key = '1' AND username = 'testmaxm' AND
callingstationid = '' AND framedipaddress =
'192.168.0.29'
[sqlippool] expand: COMMIT ->
COMMIT
rlm_sql_mysql: query: COMMIT
rlm_sql (sql): Released sql
socket id: 1
++[sqlippool] returns ok
[sql] expand:
%{User-Name} -> testmaxm
[sql] sql_set_user escaped user -->
'testmaxm'
[sql] expand: %{Acct-Input-Gigawords} ->
[sql] expand: %{Acct-Input-Octets} ->
906612
[sql] expand: %{Acct-Output-Gigawords} ->
[sql] expand: %{Acct-Output-Octets} ->
37033544
[sql]
expand: UPDATE
radacct
SET
framedipaddress =
'%{Framed-IP-Address}',
acctsessiontime =
'%{Acct-Session-Time}',
acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}'
<< 32
|
'%{%{Acct-Input-Octets}:-0}',
acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' <<
32
|
'%{%{Acct-Output-Octets}:-0}'
WHERE acctsessionid =
'%{Acct-Session-Id}'
AND username =
'%{SQL-User-Name}'
AND nasipaddress = '%{NAS-IP-Address}'
-> UPDATE
radacct
SET
framedipaddress =
'192.168.0.29',
acctsessiontime =
'600',
acctinputoctets = '0' << 32
|
'906612',
acctoutputoctets = '0' << 32
|
'37033544' WHERE
acctsessionid =
'4A8B6FA0721900' AND
username =
'testmaxm'
[sql] expand:
/var/log/radius/sqltrace.sql -> /var/log/radius/sqltrace.sql
rlm_sql
(sql): Reserving sql socket id: 0
rlm_sql_mysql:
query:
UPDATE radacct
SET
framedipaddress =
'192.168.0.29',
acctsessiontime =
'600',
acctinputoctets = '0' << 32
|
'906612',
acctoutputoctets = '0' << 32
|
'37033544' WHERE
acctsessionid =
'4A8B6FA0721900' AND
username =
'testmaxm' AND
nasipaddress = 'aaa.bbb.ccc.ddd'
rlm_sql (sql): Released
sql socket id: 0
++[sql] returns
ok
[attr_filter.accounting_response]
expand: %{User-Name} -> testmaxm
attr_filter: Matched entry DEFAULT
at line 12
++[attr_filter.accounting_response] returns updated
Sending
Accounting-Response of id 47 to aaa.bbb.ccc.ddd port 53637
Finished request
16.
Cleaning up request 16 ID 47 with timestamp +1965
Going to the next
request
Ready to process requests.
Given that the usages is now "37940156" e.g. 906612 +
3733544 and is more than the initial check value of Max-Monthly-Traffic := 10490000 I would have expected a Reply
Timeout being sent to disconnect me.
However on disconnecting and reconnecting I
get...
rlm_sqlcounter: (Check item - counter) is
less than zero
rlm_sqlcounter: Rejected user testmaxm, check_item=10490000,
counter=89021682
++[monthlytraffic] returns reject
Invalid user
(rlm_sqlcounter: Maximum monthly usage time reached): [testmaxm/<via
Auth-Type = mschap>] (from client VPN1-UK port 1)
rlm_sqlcounter: (Check item - counter) is
less than zero
rlm_sqlcounter: Rejected user testmaxm, check_item=10490000,
counter=89021682
++[monthlytraffic] returns reject
Invalid user
(rlm_sqlcounter: Maximum monthly usage time reached): [testmaxm/<via
Auth-Type = mschap>] (from client VPN1-UK port 1)
Any Ideas why I did not get disconnect
during the original session as this is what I'm
after.
Thx
Nev
================
CentOS 5.3
pptpd 1.3.4 / ppp
2.4.4
freeradius2 2.1.6
radiusclient-ng
0.5.6
daloRadius
0.9-8-SVN
================