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