(RADIATOR) Question about Authlog SQL

[Darwin Hormillosa]

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

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