(RADIATOR) Question about Authlog SQL

[Hugh Irvine]

Hello Darwin -

Note that the AuthLog only is called for authentication requests, not  
accounting requests, so you will not be able to include any attributes  
that are only present in the authentication requests. You show below  
attributes like %{Acct-Input-Octets} - these attributes will _never_ be  
present in authentication requests, hence you cannot use them with an  
AuthLog clause.

The answers to questions 2 and 3 were answered yesterday and are shown  
below in my reply.

regards

Hugh


On 11 Jan 2005, at 06:12, Darwin Hormillosa wrote:

> Hi Hugh,
> Thanks for the immediate reply.  I will just like to confirm that coz  
> we
> added before a line under Authlog SQL such as this
>
> SuccessQuery InsertCallDetails
> '%B','%n','%a','%{Framed-IP-Address}','%{Acct-Input-Octets}','%{Acct- 
> Output-
> Octets}',1,'%P',%1,'%C','%{Calling-Station-Id}','%{Called-Station- 
> Id}','%h'
>
> problem was we can't get the ip address, input/output octets when we  
> used
> authlog sql but we get it using Authby SQL.
>
> Any ideas for questions 2, and 3..
>
> Thanks in advance.
>
>
>
>
>
>
> Actually we did try before to add
> '%{Framed-IP-Address}','%{Acct-Input-Octets}','%{Acct-Output-Octets}'
> ----- Original Message -----
> From: "Hugh Irvine" <hugh at open.com.au>
> To: "Darwin Hormillosa" <dhormillosa at owtel.com>
> Cc: <radiator at open.com.au>
> Sent: Monday, January 10, 2005 9:11 PM
> Subject: Re: (RADIATOR) Question about Authlog SQL
>
>
>>
>> Hello Darwin -
>>
>> You can use any of the attributes in the Access-Request in the
>> SuccessQuery or the FailureQuery.
>>
>> Obviously if there is no corresponding attribute in the request, the
>> special characters will not work.
>>
>> Most of the attributes you show below are accounting attributes that
>> will _never_ be present in an Access-Request.
>>
>> More comments below.
>>
>>
>> On 10 Jan 2005, at 13:33, Darwin Hormillosa wrote:
>>
>>> Hi,
>>>
>>>
>>> Here is part of our radius.config:
>>>
>>>
>>> <AuthLog SQL>
>>>
>>> DBSource dbi:ODBC:isp
>>>
>>> DBUsername username
>>>
>>> DBAuth password
>>>
>>> Table CallDetails
>>>
>>> LogSuccess 1
>>>
>>> SuccessQuery InsertCallDetails
>>> '%B','%n','%a',1,'%P',%1,'%C','%{Calling-Station-Id}','%{Called-
>>> Station-Id}','%h'
>>>
>>> LogFailure 1
>>>
>>> FailureQuery InsertCallDetails
>>> '%B','%n','%a',0,'%P',%1,'%C','%{Calling-Station-Id}','%{Called-
>>> Station-Id}', '%h'
>>>
>>> </AuthLog>
>>>
>>> <AuthBy SQL>
>>>
>>> # Adjust DBSource, DBUsername, DBAuth to suit your DB
>>>
>>> DBSource dbi:ODBC:databasename
>>>
>>> DBUsername sa
>>>
>>> DBAuth fireproof
>>>
>>>  # Only one session per user at a time
>>>
>>> DefaultSimultaneousUse 1
>>>
>>> # Let the user in if they have any time left, set
>>>
>>> # the Session-timeout to the time left
>>>
>>> AuthSelect CheckISPUser %0
>>>
>>>  AuthColumnDef 0,User-Password,check
>>>
>>> AuthColumnDef 1,ValidTo,check
>>>
>>> AuthColumnDef 2,Reply-Message,reply
>>>
>>>
>>>
>>> #session-timeout
>>>
>>> AddToReply Service-Type=Framed-User, Framed-Protocol=PPP, \
>>>
>>> Framed-MTU=1500, Session-Timeout="until ValidTo", Idle-Timeout=3600
>>>
>>> # Adjust the time left when they log out
>>>
>>> # AcctSQLStatement update SUBSCRIBERS set
>>> TIMELEFT=TIMELEFT-0%{Acct-Session-Time} where USERNAME='%n'
>>>
>>> HandleAcctStatusTypes Start,Stop
>>>
>>> AcctSQLStatement UpdateValidityDate '%n'
>>>
>>> AcctSQLStatement InsertBilling
>>> '%{User-Name}','%B','%{NAS-Port}','TIME-USED','%{Framed-IP-
>>> Address}','%{Framed-IP-Address}',0%{Acct-Session-
>>> Time},'%N','%C','%{Calling-Station-Id}','%{Called-Station-
>>> Id}','%{Acct-Input-Octets}','%{Acct-Output-Octets}','%{Acct- 
>>> Terminate-
>>> Cause}','%{Acct-Session-Id}','%{Acct-Status-Type}','%{Access-Accept}'
>>>
>>>
>>>
>>> # AcctSQLStatement InsertBilling
>>> '%{User-Name}','%B',0%{Acct-Session-Time},0%{Acct-Session-
>>> Time},'%c',"time
>>> used",'%N','%N','%{Acct-Terminate-Cause}','%{Acct-Session-
>>> Id}','%{Acct-Input-Octets}','%{Acct-Output-Octets}','%{Calling-
>>> Station-Id}'
>>>
>>> </AuthBy>
>>>
>>>
>>>
>>> Here are my questions:
>>>
>>> 1. My question is, we were able to log the ip
>>> address,acct-input-octet,acct-output -octet using Authby SQL but we
>>> can't get those particulars using Authlog SQL. We tried the same
>>> parameters used in Authby SQL such as
>>> '%{Framed-IP-Address}','%{Acct-Input-Octets}','%{Acct-Output- 
>>> Octets}',
>>> but it won't work in Authlog SQL. Do you think it is normal since
>>> Authby XXX defines how to authenticate and record accounting
>>> information while Authlog SQL defines how to log authentication
>>> failures and successes. Or the Authlog SQL really does not support
>>> these parameters?
>>>
>>
>> See the explanation above.
>>
>>>
>>>
>>> 2. We have a line on our radius.config e.g AuthColumnDef 2,
>>> Reply-Message,reply. How can we pass the value of the Reply-Message
>>> generated in Radiator logs to the AcctSQLStatement?
>>>
>>
>> You can use the Class attribute for this purpose.
>>
>> Any Class attribute sent in an Access-Accept will be included in all
>> the subsequent accounting requests for the session.
>>
>>> 3. We also want to log what config file our radiator radius server is
>>> using to be log on our SQL database, is it possible, how can we do
>>> that, is there any parameters which we can use to log this particular
>>> item.
>>
>> You could use a GlobalVar to store the configuration file name and  
>> then
>> use it as you would any other special characters. You can pass in the
>> GlobalVar definition on the command line when you start radiusd. See
>> the relevant sections in the manual.
>>
>> regards
>>
>> Hugh
>>
>>
>>> Thanks in advance.
>>>
>>> Darwin
>>>
>>
>> NB:
>>
>> Have you read the reference manual ("doc/ref.html")?
>> Have you searched the mailing list archive
>> (www.open.com.au/archives/radiator)?
>> Have you had a quick look on Google (www.google.com)?
>> 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 read the reference manual ("doc/ref.html")?
Have you searched the mailing list archive  
(www.open.com.au/archives/radiator)?
Have you had a quick look on Google (www.google.com)?
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.



NB:

Have you read the reference manual ("doc/ref.html")?
Have you searched the mailing list archive  
(www.open.com.au/archives/radiator)?
Have you had a quick look on Google (www.google.com)?
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.