Updating Multipe variables with one SQL Query
Hi Following on from yesterdays ippool query - I have had an issue with Session being updated & freed The cause it seems is what the Interim updates send to Freeradius - the Router (a firebrick LNS server) basically sends back just the 'variables' that have changed, the unique ID and the accounting session ID. This is find for accounting updates - however with pools I need the IP address / calling-station-ID etc - these are not returned. I can get round that in pre-acc by running: update request { User-Name := "%{sql1:SELECT UserName from radius..radacct WITH(NOLOCK) where AcctSessionId = '%{Acct-Session-Id}' AND NASIPAddress = '%{NAS-IP-Address}'}" Framed-IP-Address := "%{sql1:SELECT FramedIPAddress from radius..radacct WITH(NOLOCK) where AcctSessionId = '%{Acct-Session-Id}' AND NASIPAddress = '%{NAS-IP-Address}'}" Calling-Station-Id := "%{sql1:SELECT CallingStationId from radius..radacct WITH(NOLOCK) where AcctSessionId = '%{Acct-Session-Id}' AND NASIPAddress = '%{NAS-IP-Address}'}" } The query itself is very quick however that is having to run the same query three times to get the separate variables I can think of several ways to get round this (create this as a long string I pass into three parts etc ... However before I think of that - is there a built in way to return the three columns from sql and then within FreeRadius and assign them into the items as need be. Thanks again in advance Richard Palmer | Director | Merula Limited Company Registered in England and Wales No. 3243995 5 Avro Court, Huntingdon, Cambridgeshire, PE29 6XS Phone 01480 222940 | Support 0845 330 0666 Support Email support@merula.net
On Apr 7, 2019, at 11:07 AM, Richard J Palmer <richard@merula.net> wrote:
The cause it seems is what the Interim updates send to Freeradius - the Router (a firebrick LNS server) basically sends back just the 'variables' that have changed, the unique ID and the accounting session ID. This is find for accounting updates - however with pools I need the IP address / calling-station-ID etc - these are not returned.
That's not only unusual, it's unfriendly. Most RADIUS clients send everything they know about the session in every accounting packet.
I can get round that in pre-acc by running:
update request { User-Name := "%{sql1:SELECT UserName from radius..radacct WITH(NOLOCK) where AcctSessionId = '%{Acct-Session-Id}' AND NASIPAddress = '%{NAS-IP-Address}'}"
It doesn't event send the User-Name? What a piece of garbage.
Framed-IP-Address := "%{sql1:SELECT FramedIPAddress from radius..radacct WITH(NOLOCK) where AcctSessionId = '%{Acct-Session-Id}' AND NASIPAddress = '%{NAS-IP-Address}'}" Calling-Station-Id := "%{sql1:SELECT CallingStationId from radius..radacct WITH(NOLOCK) where AcctSessionId = '%{Acct-Session-Id}' AND NASIPAddress = '%{NAS-IP-Address}'}"
}
The query itself is very quick however that is having to run the same query three times to get the separate variables
I can think of several ways to get round this (create this as a long string I pass into three parts etc ... However before I think of that - is there a built in way to return the three columns from sql and then within FreeRadius and assign them into the items as need be.
You can grab the 3 parameters in one SQL query, and then separate them via a regex. But that's about it. Alan DeKok.
On Sun, 2019-04-07 at 14:47 -0400, Alan DeKok wrote:
On Apr 7, 2019, at 11:07 AM, Richard J Palmer <richard@merula.net> wrote:
The cause it seems is what the Interim updates send to Freeradius - the Router (a firebrick LNS server)
That's not only unusual, it's unfriendly. Most RADIUS clients send everything they know about the session in every accounting packet.
I can get round that in pre-acc by running:
It doesn't event send the User-Name? What a piece of garbage.
Richard, have you tried contacting RevK/Firebrick and asking if they can add that? As Alan says, it's pretty unusual not to include those attributes (even though I'm not sure if any RFCs actually specify they _have_ to be included). Firebricks are nice bits of kit (I've got a 2900), I'm guessing this is a case of "no need to do that if not explicitly stated in the RFCs".
is there a built in way to return the three columns from sql and then within FreeRadius and assign them into the items as need be.
You can grab the 3 parameters in one SQL query, and then separate them via a regex. But that's about it.
Can use a map{} in v4. Or (in v3) backend it with Redis, which will pull this stuff back before SQL has even parsed the query. -- Matthew
On Apr 7, 2019, at 3:07 PM, Matthew Newton <mcn@freeradius.org> wrote:
As Alan says, it's pretty unusual not to include those attributes (even though I'm not sure if any RFCs actually specify they _have_ to be included).
Firebricks are nice bits of kit (I've got a 2900), I'm guessing this is a case of "no need to do that if not explicitly stated in the RFCs".
The RFCs are silent on *so* many topics that it's ridiculous. You could just about anything, and technically be RFC compliant. The issue for Firebrick is that they're not compatible with everyone else's RADIUS systems. All of the other clients sent (mostly) sane data in the accounting packets. All of the other RADIUS servers expect the clients to be mostly sane. Or, at least, not deliberately retarded. This seems to be like it's another case of "dump the junior engineer on RADIUS, and then claim it works". Well, it doesn't. Alan DeKok.
participants (3)
-
Alan DeKok -
Matthew Newton -
Richard J Palmer