SQL fields not lining up

[Arran Cudbard-Bell]

On 20 Oct 2013, at 06:46, Scott Pettit - Vorco <SPettit at vorco.net> wrote:

> Thanks.  Yes someone else did build these configs and that snippet has got me to the right place.
> 
> FWIW here is what was wrong:
> 
>       UPDATE ${acct_table2} SET \
>                acctstoptime       = '%S', \
>                acctsessiontime    = '%{Acct-Session-Time}', \
>                acctinputoctets    = '%{%{Acct-Input-Gigawords}:-0}' << 32 | \
>                                     '%{%{Acct-Input-Octets}:-0}', \
>                acctoutputoctets   = '%{%{Acct-Output-Gigawords}:-0}' << 32 | \
>                                     '%{%{Acct-Output-Octets}:-0}', \
>                acctterminatecause = '%{Acct-Terminate-Cause}', \
>                acctstopdelay      = '%{%{Acct-Delay-Time}:-0}', \
>                connectinfo_stop   = '%{Connect-Info}', \
>                nastxspeed         = '%{Cisco-AVPair[1]}', \
>                nasrxspeed         = '%{Cisco-AVPair[2]}', \
>                disccauseext       = '%{Cisco-AVPair[3]}', \
>                ciscoavpairother1  = '%{Cisco-AVPair[4]}', \
>                ciscoavpairother2  = '%{Cisco-AVPair[0]' \
>          WHERE acctsessionid      = '%{Acct-Session-Id}' \
>          AND username             = '%{SQL-User-Name}' \
>          AND nasipaddress         = '%{NAS-IP-Address}’"
> 
> The Cisco-AVPair[0] wasn’t closed off with } so the query was inputting junk.
> 
> It also appears that those Cisco-AVPair attributes would be better handled with rewrites as the contents of those attributes can change around depending on what the BRAS sends for that user.
> 

Just to say, using fixed indexes of Cisco-AVPair is not a robust way or writing a policy. Firmware updates, or configuration changes may result in the ordering of the Cisco-AVPair to change, and your configuration will no longer function as expected.

For 2.x.x you need to break out into something like perl, and assign the values to internal attributes (defined in raddb/dictionary).

For 3.x.x you can use a foreach loop to do the assignment.

-Arran

Arran Cudbard-Bell <a.cudbardb at freeradius.org>
FreeRADIUS Development Team