(RADIATOR) Stumpted on SQL accounting logs.

[Hugh Irvine]

Hello Jason -

You can use the radius "Class" attribute to do exactly what you 
describe.

In your authentication you should look up the account_id and and return 
it in a Class attribute. The Class attribute will then be included by 
the NAS in the subsequent accounting and you can use it directly in the 
AcctColumnDef's.

Ie.

	<AuthBy SQL>
		.....
		AuthSelect select PASSWORD, ACCOUNT_ID \
			from SUBSCRIBERS where USERNAME = %0
		AuthColumnDef 0, Password, check
		AuthColumnDef 1, Class, reply
		AccountingTable ACCOUNTING
		AcctColumnDef USERNAME, Class
		AcctColmnDef .....
		.....
	</AuthBy>

Hope that helps.

regards

Hugh


On 7 Mar 2004, at 01:21, Jason Godsey wrote:

> 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.
>
>

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.