On 15/05/2021 08:46, Mark Antony via Freeradius-Users wrote:

I have made further progress and believe this could be a solution. But it seems I have a syntax issue:

accounting { switch "%{Acct-Status-Type}" { case 'Stop' { query = "SELECT TRUE;" } }

Error:

/etc/freeradius/3.0/sites-enabled/default[650]: Unknown action 'SELECT TRUE;'.

That's configuration for rlm_sql, not unlang. You can't mix the two. Try something like accounting { if (&Acct-Status-Type == "Stop") { if ("%{sql:SELECT TRUE}" == "") { noop } } } You could also just modify the queries in e.g. raddb/mods-config/sql/main/mysql/queries.conf -- Matthew

On May 15, 2021, at 8:16 AM, Mark Antony via Freeradius-Users <freeradius-users@lists.freeradius.org> wrote:

I just tried it and it works. And I tried a second use case, where instead of stopping the user session, I turned on the AirPlane Mode and cut off the internet.

Freeradius didn't register that as Stop

(11) accounting { (11) if (&Acct-Status-Type == "Stop") { (11) if (&Acct-Status-Type == "Stop") -> FALSE

So... what does the REST of the debug output say? This isn't difficult. The server prints out the packets it receives. Including the value it gets for Acct-Status-Type. Reading the debug output will tell you exactly why it failed. So... read the debug output. ALL OF IT. Alan DeKok.

Sorry, here is the complete output: I believe this is triggered after Acct-Interim-Interval kicks in. Alternatively is there another way to capture session timeouts? (2) Received Accounting-Request Id 99 from 127.0.0.1:43277 to 127.0.0.1:1813 length 188 (2) User-Name = "00000000-5bd8-09b6-0000-00005bd809b6" (2) NAS-IP-Address = 127.0.0.1 (2) NAS-Port = 1 (2) Service-Type = Outbound-User (2) Framed-Protocol = PPP (2) Framed-IP-Address = 10.8.0.6 (2) Calling-Station-Id = "89.32.xxx.xxx" (2) NAS-Identifier = "OpenVpn" (2) Acct-Status-Type = Interim-Update (2) Acct-Input-Octets = 10604 (2) Acct-Output-Octets = 9778 (2) Acct-Session-Id = "036006CA0BC2B0671C5EE095232E5CB8" (2) Acct-Session-Time = 30 (2) Acct-Input-Gigawords = 0 (2) Acct-Output-Gigawords = 0 (2) NAS-Port-Type = Virtual (2) # Executing section preacct from file /etc/freeradius/3.0/sites-enabled/default (2) preacct { (2) update control { (2) EXPAND %l (2) --> 1621084330 (2) &Current-Timestamp := 1621084330 (2) } # update control = noop (2) update request { rlm_sql (sql): Reserved connection (1) rlm_sql (sql): Released connection (1) Need 3 more connections to reach 10 spares rlm_sql (sql): Opening additional connection (7), 1 of 25 pending slots used rlm_sql_mysql: Starting connect to MySQL server rlm_sql_mysql: Connected to database 'radius_db' on 3.64.143.170 via TCP/IP, server version 8.0.24, protocol version 10 (2) EXPAND %{User-Name} (2) --> 00000000-5bd8-09b6-0000-00005bd809b6 (2) SQL-User-Name set to '00000000-5bd8-09b6-0000-00005bd809b6' rlm_sql (sql): Reserved connection (6) (2) Executing select query: SELECT COALESCE((SELECT UNIX_TIMESTAMP(expires_at) FROM master_db.device d WHERE d.id='00000000-5bd8-09b6-0000-00005bd809b6'), 0) rlm_sql (sql): Released connection (6) (2) EXPAND %{sql:SELECT COALESCE((SELECT UNIX_TIMESTAMP(expires_at) FROM master_db.device d WHERE d.id='%{User-Name}'), 0)} (2) --> 1652905680 (2) &Expires-Timestamp := 1652905680 rlm_sql (sql): Reserved connection (2) rlm_sql (sql): Released connection (2) (2) EXPAND %{User-Name} (2) --> 00000000-5bd8-09b6-0000-00005bd809b6 (2) SQL-User-Name set to '00000000-5bd8-09b6-0000-00005bd809b6' rlm_sql (sql): Reserved connection (3) (2) Executing select query: SELECT COALESCE(MAX(is_banned), 0) as banned FROM master_db.device d WHERE d.id = '00000000-5bd8-09b6-0000-00005bd809b6' rlm_sql (sql): Released connection (3) (2) EXPAND %{sql:SELECT COALESCE(MAX(is_banned), 0) as banned FROM master_db.device d WHERE d.id = '%{User-Name}'} (2) --> 0 (2) &Banned := 0 (2) } # update request = noop (2) if (&request:Banned == 1) { (2) if (&request:Banned == 1) -> FALSE (2) if (&control:Current-Timestamp > &request:Expires-Timestamp) { (2) if (&control:Current-Timestamp > &request:Expires-Timestamp) -> FALSE (2) [preprocess] = ok (2) policy acct_unique { (2) update request { (2) &Tmp-String-9 := "ai:" (2) } # update request = noop (2) if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) && ("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i)) { (2) EXPAND %{hex:&Class} (2) --> (2) EXPAND ^%{hex:&Tmp-String-9} (2) --> ^61693a (2) if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) && ("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i)) -> FALSE (2) else { (2) update request { (2) EXPAND %{md5:%{User-Name},%{Acct-Session-ID},%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}},%{NAS-Identifier},%{NAS-Port-ID},%{NAS-Port}} (2) --> f356baede3958f26785787121a820d38 (2) &Acct-Unique-Session-Id := f356baede3958f26785787121a820d38 (2) } # update request = noop (2) } # else = noop (2) } # policy acct_unique = noop (2) suffix: Checking for suffix after "@" (2) suffix: No '@' in User-Name = "00000000-5bd8-09b6-0000-00005bd809b6", looking up realm NULL (2) suffix: No such realm "NULL" (2) [suffix] = noop (2) } # preacct = ok (2) # Executing section accounting from file /etc/freeradius/3.0/sites-enabled/default (2) accounting { (2) if (&Acct-Status-Type == "Stop") { (2) if (&Acct-Status-Type == "Stop") -> FALSE (2) if (&Acct-Status-Type == 15) { (2) if (&Acct-Status-Type == 15) -> FALSE (2) detail: EXPAND /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d (2) detail: --> /var/log/freeradius/radacct/127.0.0.1/detail-20210515 (2) detail: /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d expands to /var/log/freeradius/radacct/127.0.0.1/detail-20210515 (2) detail: EXPAND %t (2) detail: --> Sat May 15 13:12:10 2021 (2) [detail] = ok (2) [unix] = noop (2) sql: EXPAND %{tolower:type.%{%{Acct-Status-Type}:-%{Request-Processing-Stage}}.query} (2) sql: --> type.interim-update.query (2) sql: Using query template 'query' rlm_sql (sql): Reserved connection (4) (2) sql: EXPAND %{User-Name} (2) sql: --> 00000000-5bd8-09b6-0000-00005bd809b6 (2) sql: SQL-User-Name set to '00000000-5bd8-09b6-0000-00005bd809b6' (2) sql: EXPAND UPDATE radacct SET acctupdatetime = (@acctupdatetime_old:=acctupdatetime), acctupdatetime = FROM_UNIXTIME(%{%{integer:Event-Timestamp}:-%l}), acctinterval = %{%{integer:Event-Timestamp}:-%l} - UNIX_TIMESTAMP(@acctupdatetime_old), framedipaddress = '%{Framed-IP-Address}', framedipv6address = '%{Framed-IPv6-Address}', framedipv6prefix = '%{Framed-IPv6-Prefix}', framedinterfaceid = '%{Framed-Interface-Id}', delegatedipv6prefix = '%{Delegated-IPv6-Prefix}', acctsessiontime = %{%{Acct-Session-Time}:-NULL}, acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}' WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' (2) sql: --> UPDATE radacct SET acctupdatetime = (@acctupdatetime_old:=acctupdatetime), acctupdatetime = FROM_UNIXTIME(1621084330), acctinterval = 1621084330 - UNIX_TIMESTAMP(@acctupdatetime_old), framedipaddress = '10.8.0.6', framedipv6address = '', framedipv6prefix = '', framedinterfaceid = '', delegatedipv6prefix = '', acctsessiontime = 30, acctinputoctets = '0' << 32 | '10604', acctoutputoctets = '0' << 32 | '9778' WHERE AcctUniqueId = 'f356baede3958f26785787121a820d38' (2) sql: Executing query: UPDATE radacct SET acctupdatetime = (@acctupdatetime_old:=acctupdatetime), acctupdatetime = FROM_UNIXTIME(1621084330), acctinterval = 1621084330 - UNIX_TIMESTAMP(@acctupdatetime_old), framedipaddress = '10.8.0.6', framedipv6address = '', framedipv6prefix = '', framedinterfaceid = '', delegatedipv6prefix = '', acctsessiontime = 30, acctinputoctets = '0' << 32 | '10604', acctoutputoctets = '0' << 32 | '9778' WHERE AcctUniqueId = 'f356baede3958f26785787121a820d38' rlm_sql_mysql: Rows matched: 1 Changed: 1 Warnings: 1 (2) sql: SQL query returned: success (2) sql: 1 record(s) updated rlm_sql (sql): Released connection (4) (2) [sql] = ok (2) [exec] = noop (2) attr_filter.accounting_response: EXPAND %{User-Name} (2) attr_filter.accounting_response: --> 00000000-5bd8-09b6-0000-00005bd809b6 (2) attr_filter.accounting_response: Matched entry DEFAULT at line 12 (2) [attr_filter.accounting_response] = updated (2) } # accounting = updated (2) Sent Accounting-Response Id 99 from 127.0.0.1:1813 to 127.0.0.1:43277 length 0 (2) Finished request (2) Cleaning up request packet ID 99 with timestamp +36 Ready to process requests Thanks, Mark Sent with ProtonMail Secure Email. ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Saturday, 15 May 2021 13:38, Matthew Newton <mcn@freeradius.org> wrote:

On 15/05/2021 13:16, Mark Antony via Freeradius-Users wrote:

...

So I've added the Failed, which didn't work either. (4) accounting { (4) if (&Acct-Status-Type == "Stop") { (4) if (&Acct-Status-Type == "Stop") -> FALSE (4) if (&Acct-Status-Type == "Failed") { (4) if (&Acct-Status-Type == "Failed") -> FALSE Docs say: https://freeradius.org/rfc/rfc2866.html#Acct-Status-Type Seems like "Reserved for Failed" is the one, but that string is rejected right away by Freeradius and doesn't startup.

And the debug output says...?

"Failed" is in the dictionary, so it should work fine assuming you used the correct syntax. Though TBH I've never seen a NAS send Acct-Status-Type "Failed". Is it even sending anything?

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Matthew

--------

List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Hi Matthew, Yes, as you say "Failed" is in the dictionary. As you correctly stated, the NAS has no way to send the Acct-Status-Type, because the internet connection is no longer present. I have expended the variable and it says. (4) EXPAND %{Acct-Status-Type} (4) --> Interim-Update I was just wondering if Freeradius was able to trigger a timeout event after certain time has passed. In other words do you know of any way to capture timeouts in a different way? If that's not possible, that's alright. I have to think of another way. Thanks, Mark Sent with ProtonMail Secure Email. ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Saturday, 15 May 2021 14:24, Alan DeKok <aland@deployingradius.com> wrote:

On May 15, 2021, at 9:18 AM, Mark Antony via Freeradius-Users freeradius-users@lists.freeradius.org wrote:

...

Sorry, here is the complete output:

I suggested thatyou read it. The answer to your question is in it.

If instead you just go "I dunno, I'll post it to the list so you guys will tell me what's up", then no... we won't read it. If you can't be bothered to read it, then why should we?

And this isn't rocket surgery. If the value of Acct-Status-Type doesn't match "stop", then read the debug output to see what's in the packet. It's what I said to do. Why not do that?

...

I believe this is triggered after Acct-Interim-Interval kicks in.

Yes.

...

Alternatively is there another way to capture session timeouts?

FreeRADIUS gets packets from the NAS. When the NAS decides to send them. The contents of the packets are controlled by the NAS.

So your question is really "when does the NAS send session timeouts, and what's in those packets?"

The answer is "we don't know, and we don't know".

There are thousands of NAS vendors, each of whom has thousands of products, each of which has dozens of firmware revisions. You have to read the NAS documentation and the debug output to see what the NAS sends, and when.

Alan DeKok.

What does that mean? Is your NAS behind a flaky internet connection?

Sort of. The NAS is OpenVPN (Software), mobile phones that connect to it create the user-sessions. Mobile phones tend to loose connection.

If the user goes away, the NAS is supposed to sent an accounting packet which says "session is stopped". If the NAS doesn't do that, then throw it in the garbage, and buy one that works.

Not every NAS is a piece of hardware. (see above) But what you have said above, gave me an idea to double check the timeout of OpenVPN. It is indeed a tad longer than the timeout of Acct-Interim-Interval on Freeradius. In other words after Freeradius triggers an update via Acct-Interim-Interval, the NAS is still connected and hasn't declared the timeout yet. That discrepancy threw me off. Now I can see that it throws a Stop after OpenVPN decided that the session is timed out. That's perfect. (10) EXPAND %{Acct-Status-Type} (10) --> Stop I think I have covered all cases now. A session can either be stopped by the user or can time out, and both lead to stop. Hence I can now implement the SQL Update statement under Stop and I have achieved my task. accounting { if (&Acct-Status-Type == "Stop") { "%{sql:UPDATE ... WHERE device_id = '%{User-Name}'}" } Out of curiosity when you say see 'cron', do you mean a plain crontab in Linux or a specific functionality of Freeradius? I couldn't find it in documentation: https://wiki.freeradius.org/search?q=cron Thanks everyone for your help today, Mark

...

I have expended the variable and it says. (4) EXPAND %{Acct-Status-Type} (4) --> Interim-Update

Which means that the session is still active.

...

I was just wondering if Freeradius was able to trigger a timeout event after certain time has passed. In other words do you know of any way to capture timeouts in a different way?

See 'cron'.

Alan DeKok.

Your questions are vague, and use inconsistent / wrong terminology. This makes it difficult to help you.

It's ok, I was helped here and closed it off thanking all people involved.

The problem is that when you use confusing terminology, I have no idea what you mean. Please don't use that as an excuse to explain basic concepts to someone who's been doing RADIUS for ~25 years. It's not polite.

I can tell you are doing this for 25 years. You probably think everyone has the same level of understanding as you do. I'm doing this for short while and I wasn't comparing myself to you, in case I have hurt your pride, that wasn't my intention. I didn't intend to say anything that was impolite. We are having a normal conversation. Speaking of impolite, the way you continue patronising me is not polite.

No, FreeRADIUS does not trigger an update. That's not how RADIUS works.

You probably misunderstood me. I didn't say that. In other words the timeout declaration of NAS was longer than the Acct-Interim-Interval of Freeradius. This is what threw me off. It is all working now. All good. Thanks Mark

If you keep complaining, you will be banned from the list. I have zero patience for people who don't follow instructions, try to lord it over me, and who complain when told that their behavior is inappropriate.

I wasn't complaining. I was admiring you. You are already an internet celebrity. People blog about you. In case you didn't know about your reputation: https://networkingpills.wordpress.com/2019/06/20/how-ive-got-banned-from-fre...