SQL Counter access period reply CoA on Update Interim
Jose Luis Espinoza
info at ispcube.com
Tue Jun 9 22:45:16 CEST 2015
Thanks for the fast answer.!
Using Mikrotik as NAS. My main goal is to get users disconnected after some condition during acct update process.
In radcheck table I have
ZmeG0 Access-Period := 120
then in NAS I setup acct interim period in 1 minute.
Here you can see debug result when starting radiusd -X
Module: Linked to module rlm_sqlcounter
Module: Instantiating module "accessperiod" from file /etc/raddb/sql/mysql/counter.conf
sqlcounter accessperiod {
counter-name = "Max-Access-Period-Time"
check-name = "Access-Period"
reply-name = "Session-Timeout"
key = "User-Name"
sqlmod-inst = "sql"
query = "SELECT (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(AcctStartTime)) FROM radacct WHERE UserName = '%{%k}' ORDER BY AcctStartTime LIMIT 1"
reset = "never"
safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"
}
rlm_sqlcounter: Reply attribute Session-Timeout is number 27
rlm_sqlcounter: Counter attribute Max-Access-Period-Time is number 11273
rlm_sqlcounter: Check attribute Access-Period is number 11274
rlm_sqlcounter: Current Time: 1433881568 [2015-06-09 22:26:08], Next reset 0 [2015-06-09 22:00:00]
rlm_sqlcounter: Current Time: 1433881568 [2015-06-09 22:26:08], Prev reset 0 [2015-06-09 22:00:00]
Module: Checking preacct {...} for more modules to load
Then when NAS is doing Acct Update
rad_recv: Accounting-Request packet from host 190.97.25.169 port 36897, id=101, length=181
Acct-Status-Type = Interim-Update
NAS-Port-Type = Wireless-802.11
Calling-Station-Id = "2C:F0:EE:21:DE:06"
Called-Station-Id = "hotspot1"
NAS-Port-Id = "wlan1"
User-Name = "ZmeG0"
NAS-Port = 2148532243
Acct-Session-Id = "80100013"
Framed-IP-Address = 10.5.200.2
Mikrotik-Host-IP = 10.5.200.2
Event-Timestamp = "Jan 2 1970 05:36:56 CET"
Acct-Input-Octets = 617577
Acct-Output-Octets = 938225
Acct-Input-Gigawords = 0
Acct-Output-Gigawords = 0
Acct-Input-Packets = 4789
Acct-Output-Packets = 4075
Acct-Session-Time = 240
NAS-Identifier = "ISPCubeLAB"
Acct-Delay-Time = 0
NAS-IP-Address = 192.168.100.230
# Executing section preacct from file /etc/raddb/sites-enabled/default
+- entering group preacct {...}
++[preprocess] returns ok
[acct_unique] Hashing 'NAS-Port = 2148532243,Client-IP-Address = 190.97.25.169,NAS-IP-Address = 192.168.100.230,Acct-Session-Id = "80100013",User-Name = "ZmeG0"'
[acct_unique] Acct-Unique-Session-ID = "99c7e36288faa86b".
++[acct_unique] returns ok
[suffix] No '@' in User-Name = "ZmeG0", looking up realm NULL
[suffix] No such realm "NULL"
++[suffix] returns noop
++[files] returns noop
# Executing section accounting from file /etc/raddb/sites-enabled/default
+- entering group accounting {...}
[detail] expand: %{Packet-Src-IP-Address} -> 190.97.25.169
[detail] expand: /var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d -> /var/log/radius/radacct/190.97.25.169/detail-20150609
[detail] /var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d expands to /var/log/radius/radacct/190.97.25.169/detail-20150609
[detail] expand: %t -> Tue Jun 9 22:28:30 2015
++[detail] returns ok
++[unix] returns noop
[radutmp] expand: /var/log/radius/radutmp -> /var/log/radius/radutmp
[radutmp] expand: %{User-Name} -> ZmeG0
++[radutmp] returns ok
[sql] expand: %{User-Name} -> ZmeG0
[sql] sql_set_user escaped user --> 'ZmeG0'
[sql] expand: %{Acct-Input-Gigawords} -> 0
[sql] expand: %{Acct-Input-Octets} -> 617577
[sql] expand: %{Acct-Output-Gigawords} -> 0
[sql] expand: %{Acct-Output-Octets} -> 938225
[sql] expand: CALL acct_update( '%S', '%{Acct-Session-Time}', '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', 'Acct-Update', '%{Acct-Session-Id}', '%{SQL-User-Name}', '%{NAS-IP-Address}', '%{Acct-Unique-Session-Id}', '%{Realm}', '%{NAS-Port}', '%{NAS-Port-Type}', '%{Acct-Authentic}', '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}') -> CALL acct_update( '2015-06-09 22:28:30', '240', '0' << 32 | '617577', '0' << 32 | '938225', 'Acct-Update', '80100013', 'ZmeG0', '192.168.100.230', '99c7e36288faa86b', '', '2148532243', 'Wireless-802.11'
rlm_sql (sql): Reserving sql socket id: 2
rlm_sql (sql): Released sql socket id: 2
++[sql] returns ok
++[exec] returns noop
[attr_filter.accounting_response] expand: %{User-Name} -> ZmeG0
attr_filter: Matched entry DEFAULT at line 12
++[attr_filter.accounting_response] returns updated
Sending Accounting-Response of id 101 to 190.97.25.169 port 36897
Finished request 1.
Cleaning up request 1 ID 101 with timestamp +83
Going to the next request
Ready to process requests.
I need to mention that I have modified accounting_update_query and accounting_stop_query
accounting_update_query = " \
CALL acct_update( \
'%S', \
'%{Acct-Session-Time}', \
'%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', \
'%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', \
'Acct-Update', \
'%{Acct-Session-Id}', \
'%{SQL-User-Name}', \
'%{NAS-IP-Address}', \
'%{Acct-Unique-Session-Id}', \
'%{Realm}', \
'%{NAS-Port}', \
'%{NAS-Port-Type}', \
'%{Acct-Authentic}', \
'%{Called-Station-Id}', \
'%{Calling-Station-Id}', \
'%{Service-Type}', \
'%{Framed-Protocol}', \
'%{Framed-IP-Address}')"
accounting_stop_query = " \
CALL acct_stop( \
'%S', \
'%{Acct-Session-Time}', \
'%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', \
'%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', \
'%{Acct-Terminate-Cause}', \
'%{%{Acct-Delay-Time}:-0}', \
'%{Connect-Info}', \
'%{Acct-Session-Id}', \
'%{SQL-User-Name}', \
'%{NAS-IP-Address}')"
UPDATE SQL PROCEDURE
BEGIN
DECLARE Prev_Acct_Input_Octets BIGINT(20);
DECLARE Prev_Acct_Output_Octets BIGINT(20);
DECLARE Prev_Acct_Session_Time INT(12);
SELECT SUM(AcctInputOctets), SUM(AcctOutputOctets), SUM(AcctSessionTime)
INTO Prev_Acct_Input_Octets, Prev_Acct_Output_Octets, Prev_Acct_Session_Time
FROM radacct
WHERE AcctSessionId = Acct_Session_Id
AND UserName = SQL_User_Name
AND NASIPAddress = NAS_IP_Address
AND ( AcctStopTime > 0);
IF (Prev_Acct_Session_Time IS NULL) THEN
SET Prev_Acct_Session_Time = 0;
SET Prev_Acct_Input_Octets = 0;
SET Prev_Acct_Output_Octets = 0;
END IF;
UPDATE radacct SET AcctStopTime = S,
AcctSessionTime = (Acct_Session_Time - Prev_Acct_Session_Time),
AcctInputOctets = (Acct_Input_Octets - Prev_Acct_Input_Octets),
AcctOutputOctets = (Acct_Output_Octets - Prev_Acct_Output_Octets),
AcctTerminateCause = Acct_Terminate_Cause
WHERE AcctSessionId = Acct_Session_Id
AND UserName = SQL_User_Name
AND NASIPAddress = NAS_IP_Address
AND (AcctStopTime IS NULL OR AcctStopTime = 0);
INSERT INTO radacct
(AcctSessionId, AcctUniqueId, UserName,
Realm, NASIPAddress, NASPortId, NASPortType,
AcctStartTime, AcctStopTime, AcctSessionTime,
AcctAuthentic, AcctInputOctets, AcctOutputOctets,
CalledStationId, CallingStationId, AcctTerminateCause,
ServiceType, FramedProtocol, FramedIPAddress,
AcctStartDelay, AcctStopDelay)
VALUES
(Acct_Session_Id, Acct_Unique_Session_Id, SQL_User_Name,
Realm, NAS_IP_Address, NAS_Port, NAS_Port_Type,
S, '0', '0',
Acct_Authentic, '0', '0',
Called_Station_Id, Calling_Station_Id, '',
Service_Type, Framed_Protocol, Framed_IP_Address,
'0', '0');
END
STOP SQL PROCEDURE
BEGIN
DECLARE Prev_Acct_Input_Octets BIGINT(20);
DECLARE Prev_Acct_Output_Octets BIGINT(20);
DECLARE Prev_Acct_Session_Time INT(12);
SELECT SUM(AcctInputOctets), SUM(AcctOutputOctets), SUM(AcctSessionTime)
INTO Prev_Acct_Input_Octets, Prev_Acct_Output_Octets, Prev_Acct_Session_Time
FROM radacct
WHERE AcctSessionId = Acct_Session_Id
AND UserName = SQL_User_Name
AND NASIPAddress = NAS_IP_Address
AND ( AcctStopTime > 0);
IF (Prev_Acct_Session_Time IS NULL) THEN
SET Prev_Acct_Session_Time = 0;
SET Prev_Acct_Input_Octets = 0;
SET Prev_Acct_Output_Octets = 0;
END IF;
UPDATE radacct SET AcctStopTime = S,
AcctSessionTime = (Acct_Session_Time - Prev_Acct_Session_Time),
AcctInputOctets = (Acct_Input_Octets - Prev_Acct_Input_Octets),
AcctOutputOctets = (Acct_Output_Octets - Prev_Acct_Output_Octets),
AcctTerminateCause = Acct_Terminate_Cause,
AcctStopDelay = Acct_Delay_Time,
ConnectInfo_stop = Connect_Info
WHERE AcctSessionId = Acct_Session_Id
AND UserName = SQL_User_Name
AND NASIPAddress = NAS_IP_Address
AND (AcctStopTime IS NULL OR AcctStopTime=0);
END
> El 9/6/2015, a las 16:53, Alan DeKok <aland at deployingradius.com> escribió:
>
> On Jun 9, 2015, at 2:48 PM, Jose Luis Espinoza <info at ispcube.com> wrote:
>
>> Hi, I setup up a sql_counter fine… after access period is reached and active connections is reset user cannot login again.
>>
>> The issue is to ‘kill’ that connection just in the moment access period limit is reached.
>
> The sqlcounter module sends back a Session-Timeout attribute. The NAS is supposed to disconnect the users session after this time.
>
>> Can anybody explain me how to do that?
>
> CoA might work. But if the NAS doesn't support Session-Timeout, it probably won't support CoA.
>
> Alan DeKok.
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
More information about the Freeradius-Users
mailing list