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 ================
Hi, Is there any way to change the following thing fron octects to bytes or bits? for example Acct-Input-Bytes/Bits or Acct-Output-Bytes/Bits Acct-Session-Time = 58392 Acct-Input-Octets = 101147 Acct-Output-Octets = 136624 Acct-Input-Packets = 7723 Acct-Output-Packets = 8367 Where should I configure in BRAS or in Freeradius? If i want information about uplink and downlink bit/Bytes how do i get this information logged in radius log file. Who will send this information to Freerdius BRAS? BR Ganesh
On 08/19/2009 09:45 AM, ganesh nagpure wrote:
Hi,
Hi Ganesh,
Is there any way to change the following thing fron octects to bytes or bits?
Octets are the same thing as bytes.
If i want information about uplink and downlink bit/Bytes how do i get this information logged in radius log file.
It will show up in your accounting logs, if you enable accounting. I personally account to a MySQL database, and there is a column for acctinputoctets and acctoutputoctets for each session, that you can easily query.
Who will send this information to Freerdius BRAS?
What is BRAS? Jonathan.
Hi Jonathan, Thanks fo your reply. BRAS is 7206 cisco brodband RAS we are integrating with free radius. We have two type of user prepaid and post paid . I am just worndering how can i define this in cisco-avpair += parameter. I also want to defin quota for uplink and down link if this exceed user should disconnect from BRAS i.e 7206 . Is it possible to do that? BR Ganesh --- On Wed, 8/19/09, Jonathan Gazeley <jonathan.gazeley@bristol.ac.uk> wrote:
From: Jonathan Gazeley <jonathan.gazeley@bristol.ac.uk> Subject: Re: logging in bit or To: freeradius-users@lists.freeradius.org Date: Wednesday, August 19, 2009, 8:50 PM On 08/19/2009 09:45 AM, ganesh nagpure wrote:
Hi,
Hi Ganesh,
Is there any way to change the following thing fron octects to bytes or bits?
Octets are the same thing as bytes.
If i want information about uplink and downlink bit/Bytes how do i get this information logged in radius log file.
It will show up in your accounting logs, if you enable accounting. I personally account to a MySQL database, and there is a column for acctinputoctets and acctoutputoctets for each session, that you can easily query.
Who will send this information to Freerdius BRAS?
What is BRAS?
Jonathan. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On 08/19/2009 06:37 PM, ganesh nagpure wrote:
Hi Jonathan,
Thanks fo your reply.
BRAS is 7206 cisco brodband RAS we are integrating with free radius. We have two type of user prepaid and post paid . I am just worndering how can i define this in cisco-avpair += parameter.
I'm afraid I can't help you with Cisco-specific parameters. You'll have to consult the documentation for the NAS.
I also want to defin quota for uplink and down link if this exceed user should disconnect from BRAS i.e 7206 .
Is it possible to do that?
This is possible, but you will need to write some additional scripts or modules. FreeRADIUS will put the accounting data in a database table but you need to write something, e.g. a perl script that can read this table, add up the values and work out if a user should be accepted or rejected upon authentication.
BR Ganesh
--- On Wed, 8/19/09, Jonathan Gazeley<jonathan.gazeley@bristol.ac.uk> wrote:
From: Jonathan Gazeley<jonathan.gazeley@bristol.ac.uk> Subject: Re: logging in bit or To: freeradius-users@lists.freeradius.org Date: Wednesday, August 19, 2009, 8:50 PM On 08/19/2009 09:45 AM, ganesh nagpure wrote:
Hi,
Hi Ganesh,
Is there any way to change the following thing fron
octects to bytes or bits?
Octets are the same thing as bytes.
If i want information about uplink and downlink
bit/Bytes how do i get this information logged in radius log file.
It will show up in your accounting logs, if you enable accounting. I personally account to a MySQL database, and there is a column for acctinputoctets and acctoutputoctets for each session, that you can easily query.
Who will send this information to Freerdius BRAS?
What is BRAS?
Jonathan. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-- ---------------------------- Jonathan Gazeley Systems Support Specialist ResNet | Wireless& VPN Team Information Services University of Bristol ----------------------------
Neville wrote:
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 '@' <mailto:'@'> 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 ================
------------------------------------------------------------------------
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
why cant you conbine two counter modules: The monthlytraffic counter The numberofdays counter and whichever comes first disconnect the user. goksie
participants (4)
-
ganesh nagpure -
Goke Aruna -
Jonathan Gazeley -
Neville