Create New Session Based On Accounting Update
One Zero Art
onezeroartapps at gmail.com
Sun Feb 23 14:59:42 CET 2020
Hi Alan,
Thank you for your response.
In debug mode I can see that freeradius updating radacct table row but not
inserting new session.
I am not sure if I missed any config in SQL or somewhere else.
*This is my issue. *
Currently, the user offline in freeradius ==* acctstoptime is not null*,
but the user online in the router so the router sending data to the
server continuously.
In SQL queries.conf I can see there is the SQL query to insert a new
session when interim update received by server but for me it's not
happening.
*SQL queries.conf file *
*interim-update secion *
# The update condition matched no existing sessions. Use
# the values provided in the update to create a new session.
#
query = "\
INSERT INTO ${....acct_table1} \
(${...column_list}) \
VALUES \
*My debug code*
(0) Received Accounting-Request Id 12 from 192.168.43.120:45516 to
192.168.43.100:1813 length 255
(0) Service-Type = Framed-User
(0) Framed-Protocol = PPP
(0) NAS-Port = 15728640
(0) NAS-Port-Type = Ethernet
(0) User-Name = "@114"
(0) Calling-Station-Id = "08:00:27:B9:4B:F2"
(0) Called-Station-Id = "pppoe"
(0) NAS-Port-Id = "lan"
(0) MS-CHAP-Domain = "114"
(0) Acct-Session-Id = "81400000"
(0) Framed-IP-Address = 192.168.10.254
(0) Acct-Authentic = RADIUS
(0) Event-Timestamp = "Feb 24 2020 00:24:17 +06"
(0) Acct-Session-Time = 150
(0) Idle-Timeout = 0
(0) Session-Timeout = 2513708
(0) X-Ascend-Data-Rate = 2000000
(0) Ascend-Xmit-Rate = 2000000
(0) Mikrotik-Rate-Limit = "2M/2M"
(0) Mikrotik-Address-List = "2Mb"
(0) Acct-Input-Octets = 7616
(0) Acct-Input-Gigawords = 0
(0) Acct-Input-Packets = 103
(0) Acct-Output-Octets = 248
(0) Acct-Output-Gigawords = 0
(0) Acct-Output-Packets = 9
(0) Acct-Status-Type = Interim-Update
(0) NAS-Identifier = "MikroTik"
(0) Acct-Delay-Time = 0
(0) Mikrotik-Realm = "114"
(0) NAS-IP-Address = 192.168.43.120
(0) # Executing section preacct from file
/etc/freeradius/3.0/sites-enabled/default
(0) preacct {
(0) [preprocess] = ok
(0) policy acct_unique {
(0) update request {
(0) &Tmp-String-9 := "ai:"
(0) } # update request = noop
(0) if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) &&
("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i)) {
(0) EXPAND %{hex:&Class}
(0) -->
(0) EXPAND ^%{hex:&Tmp-String-9}
(0) --> ^61693a
(0) if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) &&
("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i)) -> FALSE
(0) else {
(0) update request {
(0) EXPAND
%{md5:%{User-Name},%{Acct-Session-ID},%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}},%{NAS-Identifier},%{NAS-Port-ID},%{NAS-Port}}
(0) --> a5d3e659ce0819c39a40d66dd53f5e5b
(0) &Acct-Unique-Session-Id := a5d3e659ce0819c39a40d66dd53f5e5b
(0) } # update request = noop
(0) } # else = noop
(0) } # policy acct_unique = noop
(0) suffix: Checking for suffix after "@"
(0) suffix: Looking up realm "114" for User-Name = "@114"
(0) suffix: No such realm "114"
(0) [suffix] = noop
(0) [files] = noop
(0) } # preacct = ok
(0) # Executing section accounting from file
/etc/freeradius/3.0/sites-enabled/default
(0) accounting {
(0) detail: EXPAND
/var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d
(0) detail: --> /var/log/freeradius/radacct/
192.168.43.120/detail-20200223
(0) detail:
/var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d
expands to /var/log/freeradius/radacct/192.168.4
(0) detail: EXPAND %t
(0) detail: --> Sun Feb 23 19:41:22 2020
(0) [detail] = ok
(0) [unix] = noop
(0) sql: EXPAND %{tolower:type.%{Acct-Status-Type}.query}
(0) sql: --> type.interim-update.query
(0) sql: Using query template 'query'
rlm_sql (sql): Reserved connection (1)
(0) sql: EXPAND %{User-Name}
(0) sql: --> @114
(0) sql: SQL-User-Name set to '@114'
(0) sql: EXPAN*D UPDATE radacct SET acctupdatetime * =
(@acctupdatetime_old:=acctupdatetime), acctupdatetime =
FROM_UNIXTIME(%{integer:Event-Timestamp}), acctiestamp} -
UNIX_TIMESTAMP(@acctupdatetime_old), framedipaddress =
'%{Framed-IP-Address}', acctsessiontime = %{%{Acct-Session-Time}:-NULL},
acctinputoctets = '% 32 | '%{%{Acct-Input-Octets}:-0}', acctoutputoctets =
'%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}'
WHERE AcctUniqueId = '%{Acct-Uni
(0) sql: --> *UPDATE radacct SET acctupdatetime* =
(@acctupdatetime_old:=acctupdatetime), acctupdatetime =
FROM_UNIXTIME(1582482257), acctinterval = 158datetime_old),
framedipaddress = '192.168.10.254', acctsessiontime = 150, acctinputoctets
= '0' << 32 | '7616', acctoutputoctets = '0' << 32 | '248' WHERE
Acc0d66dd53f5e5b'
(0) sql: Executing query: UPDATE radacct SET acctupdatetime =
(@acctupdatetime_old:=acctupdatetime), acctupdatetime =
FROM_UNIXTIME(1582482257), acctintervaMP(@acctupdatetime_old),
framedipaddress = '192.168.10.254', acctsessiontime = 150, acctinputoctets
= '0' << 32 | '7616', acctoutputoctets = '0' << 32 |
'248'e0819c39a40d66dd53f5e5b'
rlm_sql_mysql: Rows matched: 1 Changed: 1 Warnings: 0
(0) sql: SQL query returned: success
(0) sql: 1 record(s) updated
rlm_sql (sql): Released connection (1)
Need 4 more connections to reach 10 spares
rlm_sql (sql): Opening additional connection (6), 1 of 26 pending slots used
rlm_sql_mysql: Starting connect to MySQL server
rlm_sql_mysql: Connected to database 'radius' on Localhost via UNIX socket,
server version 5.7.29-0ubuntu0.18.04.1, protocol version 10
(0) [sql] = ok
(0) [exec] = noop
(0) attr_filter.accounting_response: EXPAND %{User-Name}
(0) attr_filter.accounting_response: --> @114
(0) attr_filter.accounting_response: Matched entry DEFAULT at line 12
(0) [attr_filter.accounting_response] = updated
(0) Sent Accounting-Response Id 12 from 192.168.43.100:1813 to
192.168.43.120:45516 length 0
(0) Finished request
(0) Cleaning up request packet ID 12 with timestamp +9
Ready to process requests
Thank you
On Sun, Feb 23, 2020 at 7:05 PM Alan DeKok <aland at deployingradius.com>
wrote:
> On Feb 22, 2020, at 6:49 PM, One Zero Art <onezeroartapps at gmail.com>
> wrote:
> > I am trying to create a new session and insert it into radacct table
> based
> > on an accounting request from NAS.
>
> That's what the server does automatically.
>
> > When NAS sends Interim Update I want to check if that user already in
> > radacct table and null or not null. If not null then create a new session
> > and make that user online in freeradius.
>
> The default queries for the SQL modules do this. Just run the server in
> debug mode, and watch the server do its magic.
>
> > I am not sure if it's possible or even the right thing to do.
>
> It's the right thing to do. It's possible with *zero* work on your part.
>
> Alan DeKok.
>
>
> -
> List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html
More information about the Freeradius-Users
mailing list