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@deployingradius.com> wrote:
On Feb 22, 2020, at 6:49 PM, One Zero Art <onezeroartapps@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