(RADIATOR) Stumpted on SQL accounting logs.

[Jason Godsey]

Hugh,

Yes, that is what I want to do, only I need to store keyed off the account# not User-Name in SQL.

So, basically I need:
AcctColumnDef   USERNAME,User-Name

to be:
AcctColumnDef   USERNAME,(select i_account_id as RETURN from t_users where t_username ='%{User-Name}')

I am using MySQL 4.0 so I don't have subqueries and above isn't an option, but I need something that will give me like behavior.

I thought about doing the following but can't seem to find how, so really I just need a pointer to the right documentation because I obviously overlooked something critical :)

During authentication, return the i_account_id as an added Radius attribute like
VENDORATTR      9048    OSC-Uid  1 integer
ATTRIBUTE       Client-Id   90480014        string
ATTRIBUTE       Client-Identifier 90480015  string

Our current implementation doesn't use any of the above for anything.
Then, during accounting insertion into database, use %{OSC-Uid} where normally User-Name is logged.  This seemed the easiest solution unless that would depend on remote radius server passing it back in the accounting packet (which I'm not sure if it will).

Thank you

---------- Original Message ----------------------------------
From: Hugh Irvine <hugh at open.com.au>
Date:  Sat, 6 Mar 2004 18:08:10 +1100

>
>Hello Jason -
>
>I don't quite understand what you are trying to do in your script, but 
>why don't you have Radiator just write into the SQL database directly?
>
>Its a very simple AuthBy SQL setup, and there is an example in 
>"goodies/sql.cfg".
>
>regards
>
>Hugh
>
>
>On 6 Mar 2004, at 04:56, Jason Godsey wrote:
>
>> Starting with the default,
>> AcctColumnDef	 USERNAME,User-Name
>>
>> We currently dump accounting into daily text detail files, and 
>> post-process them  into an SQL server to run billing scripts.
>>
>> The one thing that my perl scripts do, and that I'm having dificulting 
>> figuring out is storing the account#, not User-Name in the SQL table.
>>
>> My perl script simply makes a hash $un2uid{"jason"}=100492; (populated 
>> by a single query off the users table in SQL).
>>
>> After this, I simply ($user)=(/User-Name = "([^"]+)/);
>> $user=$un2uid{$user};
>>
>> So, what I'm stumped on is how to obtain the field i_account_id from 
>> my user table, and then use it in the accounting fields.
>>
>> Thank you in advance.
>>
>> --
>> Archive at http://www.open.com.au/archives/radiator/
>> Announcements on radiator-announce at open.com.au
>> To unsubscribe, email 'majordomo at open.com.au' with
>> 'unsubscribe radiator' in the body of the message.
>>
>>
>
>NB: have you included a copy of your configuration file (no secrets),
>together with a trace 4 debug showing what is happening?
>
>-- 
>Radiator: the most portable, flexible and configurable RADIUS server
>anywhere. Available on *NIX, *BSD, Windows, MacOS X.
>-
>Nets: internetwork inventory and management - graphical, extensible,
>flexible with hardware, software, platform and database independence.
>-
>CATool: Private Certificate Authority for Unix and Unix-like systems.
>
>

--
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.