SQL Query Examples for FreeRadius

[Alan DeKok]

On Sep 5, 2018, at 12:55 PM, 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,

  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.