On Mon, 27 Jan 2003, Peter Nixon wrote:
> Hi Dave
>
> What exactly is it that you want to do? Make Extent work better, or switch to
> freeradius. It sounds like freeradius is doing exactly what it is configured
> to do by default, and that is to UPDATE and exisiting session record when it
> recieves a ALIVE packet, NOT add a new record. I suggest you have a good read
> of sql.conf (or mysql.conf of whatever) and understand the queries that are
> being executed at different stages.
> I myself am using Freeradius with a large VoIP setup, and I found that the
> default queries were useless to me as they would kill the database. I
> switched all queries to INSERTS, set different types of records to go to
> different tables, and and threw away most of the default fields that were
> being stored and replaced them with Cisco VoIP specific attributes (VSAs).
>
> You should not have to use a cron script to parse your detail files. Just
> modify the freeradius queries so it stores the information that you want.
>
> I also found that MySQL simply could not handle the load I was throwing at it,
> so I switched to Postgres and have been happy ever since. The fact that
> postgres can do sub selects and views, makes it much more usefull if you have
> split your radacct table up into multiple tables too.
Just to comment on that. If you add a few more indexes and use InnoDB tables
instead of the default MyISAM (row level instead of table level locking) things
can improve a lot. We have around 200000 rows a day in radacct, do double login
detection through the sql module and still get nice performance.
> Not to mention that I use the start and stop times as reported by the ciscos
> instead of having freeradius timestamp the records, which is much more
> accurate, and postgres supports cisco timestamp format while mysql does not.
>
> HTH
>
> Peter
>
>
> On Sun, 26 Jan 2003 04:16 am, Dave Seddon wrote:
> > Greetings,
> >
> > Still wondering how to convert DSL interum updates to standard dail-up
> > type radius accounting.
> >
> > I've done some digging through the source code, and have decided that
> > perhaps I need to create a module, perhaps "rlm_alive_to_dialup". If
> > the new module was based on rlm_detail, it would just be a matter of
> > linking to a mysql database to see the last update, calculate the
> > difference, then generate the new radius packets, for start and stop.
> >
> > I'm also wondering if this should be part of the proxy (which seems to
> > be in the realm code) functionality, eg. Make the proxy feature break
> > RFC and allow it to modify the 'alive' and create a 'start' and
> > a 'stop'.
> >
> > Any thoughts on whether it should be a seperate module or a
> > modification to the proxy code?
> >
> > thanks,
> >
> > Dave Seddon
> >
> > ----- Original Message -----
> > From: Dave Seddon <[EMAIL PROTECTED]>
> > Date: Saturday, January 25, 2003 4:20 pm
> > Subject: DSL Accouting?
> >
> > > Greetings,
> > >
> > > I'm new to the list. I have two issues:
> > > -Problem logging accounting
> > > -Alive packet processing and integration with dial-up billing
> > >
> > > I use Freeradius(7.0) with MySQL(3.23.54) on FreeBSD(4.7) to
> > > authenticate lots of xDSL PPP sessions via an L2TP tunnel
> > > terminated on
> > > a big Cisco box. It works very well, however for some reason
> > > accounting records do not get put in the 'radacct' mysql table.
> > > There
> > > are some records in the table, but no where near as many as their
> > > should be since Interim updates or Alive packets get sent by the
> > > Cisco
> > > every 10 minutes. However I do get all the accouting records
> > > in /var/log/radacct/ip_address/detail.
> > >
> > > Here is some of the /usr/local/etc/raddb/radius.conf. The
> > > accounting
> > > section seems correct. The sql.conf is untouched from the example
> > > (except for the password and username).
> > > ----------------------------------------
> > > authorize {
> > > preprocess
> > > suffix
> > > sql
> > > files
> > > }
> > > authenticate {
> > > }
> > > preacct {
> > > preprocess
> > > suffix
> > > files
> > > }
> > > accounting {
> > > detail
> > > # unix
> > > sql
> > > radutmp
> > > }
> > > ----------------------------------------
> > > So what could be wrong?
> > >
> > > To see what data I was getting in the detail log, I wote a little
> > > perl
> > > script to parse the detail log and stick the data in MySQL so I
> > > could
> > > easily do select statements. I discovered that the records I
> > > created
> > > where structured differently, so perhaps that's why it's not going
> > > to
> > > the Freeradius radacct table? Essentially, the difference is
> > > the "Tunnel" attributes.
> > >
> > > The database structure I created is:
> > > -----------------------------
> > > drop database radiusaccounting;
> > > create database radiusaccounting;
> > > use radiusaccounting;
> > >
> > > CREATE TABLE radacct (
> > > RadAcctId int unsigned NOT NULL auto_increment,
> > > NASIPAddress varchar(15) NOT NULL default '',
> > > NASPortId tinyint unsigned default NULL,
> > > NASPortType varchar(32) default NULL,
> > > UserName varchar(64) NOT NULL default '',
> > > AcctStatusType varchar(20) NOT NULL default '',
> > > AcctAuthentic varchar(20) NOT NULL default '',
> > > ServiceType varchar(32) default NULL,
> > > AcctSessionID varchar(12) NOT NULL default '',
> > > FramedProtocol varchar(6) default NULL,
> > > TunnelServerEndpoint varchar(15) NOT NULL default '',
> > > TunnelClientEndpoint varchar(15) NOT NULL default '',
> > > TunnelType varchar(10) NOT NULL default '',
> > > TunnelClientAuthID varchar(25) NOT NULL default '',
> > > TunnelServerAuthID varchar(25) NOT NULL default '',
> > > AcctTunnelConnection int unsigned default NULL,
> > > FramedIPAddress varchar(15) NOT NULL default '',
> > > AcctInputOctets int unsigned default NULL,
> > > AcctOutputOctets int unsigned default NULL,
> > > AcctInputPackets int unsigned default NULL,
> > > AcctOutputPackets int unsigned default NULL,
> > > AcctSessionTime int unsigned default NULL,
> > > AcctDelayTime int unsigned default NULL,
> > > ClientIPAddress varchar(15) NOT NULL,
> > > TimeStamp bigint unsigned default NULL,
> > > HumanTime varchar(10) default NULL,
> > > PRIMARY KEY (RadAcctId),
> > > KEY UserName (UserName)
> > > );
> > > ---------------------
> > >
> > > So I've kind of solved the problem of getting the accouting data
> > > into
> > > the MySQL database, however it's a bit crap cos I need to process
> > > the
> > > logs with a cron job, instead of automatically inserting from
> > > FreeRadius.
> > >
> > > My company has lots of dialup also, and an ISP billing system
> > > called
> > > Extent (with built in radius) that works fine fo these dialup
> > > customers, however is unaware of 'Alive' packets. I'd really like
> > > to
> > > feed the accounting data from Freeradius to the Extent billing
> > > package. I'm thinking that for every "Alive" packet recieved from
> > > the
> > > RAS box perhaps I could calculate the difference in Octets between
> > > now
> > > and the last 'Alive', and then send a fake radius start and stop
> > > record
> > > to Extent, such that Extent would think the DSL user had dialed up
> > > for
> > > 10 minutes, used X amount of data, and hungup. This way the
> > > standard
> > > way of calculating usage would occur, and usage graphs, etc, would
> > > all
> > > work fine. It would be very nice to build this functionality into
> > > Freeradius. -- Perhaps I should email the developers list about
> > > how to
> > > do this?
> > >
> > > thanks,
> > >
> > > Dave Seddon
> > >
> > >
> > > -------------------------------------------------------------------
> > > --
> > > Would you like to receive faxes to your personal email address?
> > > You can with mBox. Visit http://www.mbox.com.au/fax
> > >
> > > -
> > > List info/subscribe/unsubscribe? See
> > > http://www.freeradius.org/list/users.html
> >
> > ---------------------------------------------------------------------
> > Never lose a fax again, receive faxes to your personal email account!
> > Visit http://www.mbox.com.au/fax
> >
> > -
> > List info/subscribe/unsubscribe? See
> > http://www.freeradius.org/list/users.html
>
> --
>
> Peter Nixon
> http://www.peternixon.net/
> PGP Key: http://www.peternixon.net/public.asc
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>
--
Kostas Kalevras Network Operations Center
[EMAIL PROTECTED] National Technical University of Athens, Greece
Work Phone: +30 210 7721861
'Go back to the shadow' Gandalf
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
