SQL Query Examples for FreeRadius

[Michael Ducharme]

Hi,


On 9/5/2018 9:55 AM, me at erikthiart.com wrote:
>> I understand what you are saying, I think my question should’ve been more clear, I am looking for go to queries that the veterans reply on, in my case I had to bump my head when my online users query did not return the data I thought, example my query was:
>> SELECT username FROM radacct WHERE acctstoptime IS NULL
>>
>> Being completely new to freeradius I thought this would work (the logic in my mind checked out), turns out there is a ton of garbage sessions that never closed, mikrotik got shutdown unexpectedly or for whatever other reason,
We use this query with MikroTik to determine online users. It is pretty 
accurate as long as you do the following:
  - Use QoS to prioritize the RADIUS accounting packets so that if you 
have congestion between the NAS and RADIUS server that the accounting 
packet will not be dropped

Also, to handle issues such as loss of connectivity between the NAS and 
FreeRADIUS or sudden power loss of the NAS (which would result in no 
Accounting-Stop packets being sent), you can do the following:
  - Add a query to the FreeRADIUS config to close stale sessions for the 
same user/mac combination, ex. in /etc/raddb/sites-available/default, at 
the end of the "authorize" section:

     if(User-Name){
             if("%{sql:UPDATE radacct set 
acctstoptime=ADDDATE(acctstarttime,INTERVAL acctsessiontime SECOND), 
acctterminatecause='Clear-Stale Session' WHERE username='%{User-Name}' 
and callingstationid='%{Calling-Station-Id}' and acctstoptime is NULL}"){
         }
     }

  (The above will run when the user reconnects and update the database 
to clear the stale session)
  - Set all of your interim update intervals on the MikroTiks to 
something like 15 minutes, and have a script run every 15 minutes 
checking to see if there were no updates for the past 45 minutes, to 
clear the stale session using a similar query to the one above. You can 
expect that if you have the interim update set at 15 minutes and 3 in a 
row are dropped, the customer in question is probably no longer online.

Once you do those things, you should be able to rely better on your 
"SELECT username FROM radacct WHERE acctstoptime IS NULL" query

Michael