Create New Session Based On Accounting Update
Hi, I am trying to create a new session and insert it into radacct table based on an accounting request from NAS. 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. I am not sure if it's possible or even the right thing to do. Want to do that because sometimes user online in NAS but offline in freeradius. Thank you
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.
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
On Feb 23, 2020, at 8:59 AM, One Zero Art <onezeroartapps@gmail.com> wrote:
In debug mode I can see that freeradius updating radacct table row but not inserting new session.
If there's no existing session, it should INSERT a new one.
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.
So why is there anything in acctstoptime? FreeRADIUS doesn't invent accounting data. It simply stores what the NAS sends it. The problem is very likely to be that the NAS is sending the wrong information to FreeRADIUS. OR the NAS is sending the *same* data for two different sessions.
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.
Run the server in debug mode to see why...
*SQL queries.conf file *
I don't need to see the configuration files. I *do* already have access to them,
*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) Acct-Session-Id = "81400000"
Those are all attributes which identify a particular user session. Is the NAS sending the *same* attributes for *different* user sessions? Again, read the debug output to see. ...
(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
That is pretty clear. There is an existing session for the user in SQL. So, the server updates it. This is what it's supposed to do. If the row in the DB is for a *different* user session, then how does FreeRADIUS know that? i.e. you might think it's for a different session, but why? What *RADIUS* information makes it a different session? Alan DeKok.
participants (2)
-
Alan DeKok -
One Zero Art