Re: SQL Query Examples for FreeRadius
All of this data should be in an SQL database. You can use normal SQL queries to find this information.
The schema for FreeRADIUS is well documented.
Hello Alan, thanks for replying to my e-mail. First time I am using an email list, I hope this reply-to attempt works as intended... 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, point is if there was a documented collection of SQL queries freeradius users generally rely on, I could have avoided a bit of my time being wasted. So to put it into context what I would like to do is create a page with useful queries, crowdsourced from the freeradius community to help new commers like myself and those after me. I hope this explains it a bit better? PS: You need to read this email from the perspective of me being completely new to this, I am a windows user :P
On Sep 5, 2018, at 12:55 PM, me@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,
Then fix the NAS so that it closes those sessions. The server is designed to work as well as possible, in as many situations as possible. The problem is that NAS vendors don't have that attitude. The server can *only* work with the information it has. If the NAS lies to the server, or the NAS never sends information to the server, then FreeRADIUS has no way of knowing that.
point is if there was a documented collection of SQL queries freeradius users generally rely on, I could have avoided a bit of my time being wasted.
You're looking for a solution to the problem of bad records in SQL. The solution is *not* to create SQL queries which ignore the bad data. The solution is to not get the bad data in the first place. Or maybe, to clean up the old sessions manually.
So to put it into context what I would like to do is create a page with useful queries, crowdsourced from the freeradius community to help new commers like myself and those after me.
The Wiki is available for this. Or, if you write scripts to do things, you can send a pull request to include the script into the next release. We have many, many, options open for people to contribute.
I hope this explains it a bit better?
Maybe. But I wish to be sure that you're operating from a correct understanding of how things work. If your mental model of RADIUS + SQL is wrong, then your questions will be wrong, and your solutions will be wrong.
PS: You need to read this email from the perspective of me being completely new to this, I am a windows user :P
That's nice. But it helps to understand that data in SQL is just data in SQL. Anyone can query it using standard SQL tools. There are few "RADIUS specific" queries that you can run. Alan DeKok.
Hi, On 9/5/2018 9:55 AM, me@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
Sorry, one other thing, for clearing the stale sessions, there is a script bundled with DaloRADIUS that does this, which you can download and use separately: https://github.com/lirantal/daloradius/blob/master/contrib/scripts/maintenan... Simply set up a crontab that runs that, making sure that your interim update interval is set properly on the MikroTik NAS units first.
participants (3)
-
Alan DeKok -
me＠erikthiart.com -
Michael Ducharme