On Tue, 28 Jan 2003 04:25 am, Dave Seddon wrote:
> Greetings,
>
> > What exactly is it that you want to do? Make Extent work better, or
> > switch to
> > freeradius.
>
> I want to make it easy for small dailup based ISPs to bill DSL
> customers. Most small ISPs use billing systems based on stop starts, so
> it would be good for DSL wholesalers to be able to generate simple
> radius packets for smaller ISPs.
OK. But what is the exact problem you are trying to solve. It seems to me that
you can do exactly what you want just by altering the SQL queries a little,
adding some table contstraints and stored procedures to the DB, and then
handing your ISP "customers" a CSV file with the billing records as dumped
form your database. If you felt the need you could write a very simple perl
script to output your DB records in radius detail format which would allow
them to be imported at will to other radius aware programs, although this
would seem to be a step backwards. A database is usually the requred end
result for billing, and freeradius supports exactly that.
> > 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.
>
> Different tables for different types of session? or was that for load
> reasons? Perhaps different tables for different realms?
StopVoIP records go in one table and StopTelephony goes into another so that I
can have a VIEW that does a JOIN on H323CallID and end up with a nice table
showing me Destination/Origination IP address along with actual POTS call
times billed from the telco. (Each call leg in VoIP generates its own Start
and Stop packets thus you get them for both the Telephony leg and the IP leg
of a VoIP call though a gateway). This is much faster and more efficient than
doing a SUB SELECT in a table with many millions of records being added oer
month.
I also split my ALIVE packets and START packets up the same way. I only check
ALIVE packets if there is a billing discrepency as I found the extra load on
the database from doing UPDATES with the ALIVE packets as opposed to INSERTS
just wasn't worth it for me. In your case though, its exactly what you want
to do.
> > 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.
> > 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.
>
> Very intersting. Thanks. Also thanks to Kostas Kalevras for his
> comment on MySQL. Looks like Postgres could be the go for lots of reasons.
>
> Which part of freeradius creates the timestamps?
from raddb/postgresql.conf. Look for %S.
accounting_start_query = "INSERT into ${acct_table1} (AcctSessionId,
AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType,
AcctStartTime, AcctSessionTime, A
cctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets,
AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause,
ServiceType, FramedProtocol, FramedIPA
ddress, AcctStartDelay, AcctStopDelay) values('%{Acct-Session-Id}',
'%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}',
'%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Ty
pe}', '%S', '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0',
'%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}',
'%{Framed-Protocol}', '%{Framed-IP-A
ddress}', '%{Acct-Delay-Time:-0}', '0')"
Instead of using this timestamp from freeradius (which includes accounting
delay and therefore requires extra calculation for acurate billin) I use the
h323starttime and h323stoptime as generated directly by the ciscos (which all
run NTP timesync) with the following command:
gw-accounting h323 vsa
There should be a similar command for DSL I would think. (Don't have time to
check now though sorry)
If you are using SQL accounting you REALLY need to sit down and understand how
ALL of the queries work so you can customise them for your needs. The queries
that are default are IMHO broken (I will probably cop some shit for this
comment) as they are only any good for a small dialin ISP running generic
radius. However, I have not patched them as my accounting needs are a bit
more specialised than the average ISP, I am using only cisco equipment and I
am not doing any standard ISP type dialin currently so I don't have the time
to test how my changes affect others. I have been planning to complete my
documentation howver and commit it as advanced_sql_accounting.txt or
something. Would other people be interested in this?
Now I have to go and do some work. I have 2.1GB worth of billing records (A
monthly DB dump from one site) to match/check today :-(
Hope that helps
-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
>
> ---------------------------------------------------------------------
> NEW to mBox, receive faxes to any email address!
> Find out more 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
