Updating Multipe variables with one SQL Query

[Alan DeKok]

On Apr 7, 2019, at 11:07 AM, Richard J Palmer <richard at 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.