[RADIATOR] Query/Sumarise accounting records

[Jose Borges Ferreira]

 I use something like this:

    AcctInsertQuery     INSERT DELAYED INTO %0 (%1) VALUES (%2) \
                          ON DUPLICATE KEY UPDATE \

bytesin=VALUES(bytesin),bytesout=VALUES(bytesout), \

sessiontime=VALUES(sessiontime),accttype=VALUES(accttype);


    AccountingTable ACCOUNTING_%Y%m

    AcctColumnDef   username,User-Name
    AcctColumnDef   callingid,Calling-Station-Id
    AcctColumnDef   calledid,Called-Station-Id
    AcctColumnDef
session_start,UNIX_TIMESTAMP()-0%{Acct-Delay-Time}-0%{Acct-Session-Time},literal
    AcctColumnDef   accttype,Acct-Status-Type
    AcctColumnDef   bytesin,0%{Acct-Input-Gigawords}*4294967296
+0%{Acct-Input-Octets},literal
    AcctColumnDef   bytesout,0%{Acct-Output-Gigawords}*4294967296
+0%{Acct-Output-Octets},integer
    AcctColumnDef   sessionid,Acct-Session-Id
    AcctColumnDef   sessiontime,Acct-Session-Time,integer
    AcctColumnDef   nas,NAS-IP-Address
    AcctColumnDef   ip,Framed-IP-Address


Note:

   - Here i have the overall session in one table per month.
   "AccountingTable ACCOUNTING_%Y%m" Provides me that.
   - "sessionid" is the primary key for this table.
   - I have one cronjob that creates next month table.
   - If you need dailly data, just have to add a column , populate it with
   the day ( AcctColumnDef   session_day,'%Y%m%d' ,literal ) and add
   session_day  to the primary key.


José Borges Ferreira

Jim wrote:

Hi,

Not really a Radiator specific question but I expect plenty of you have
done this.

At the moment all our Radius accounting data gets logged to a MySQL
database with a table per month.  I'd like to be able to easily pull
data on individual users to see how much they have downloaded on a day
or month, I would also like to be able to pull Top downloaders per month
etc.  I'm struggling to work out an easy way to do this as adding up
every accounting Start/Alive/Stop and subtracting traffic counters for
data done in the previous day/month is going to have a big overhead and
running this on a per user basis for 100k users will be a huge overhead.

Does anyone have any advice on ways to implement this that are going to
be slick and scale to many users?  Maybe processing each Radius
accounting record as they are inserted and updating a seperate table
contraining usage for that current month/day?

Any pointers appreciated as I have been looking at this a while and
struggling to come up with a neat way of doing it.

Thanks.

Jim.
_______________________________________________
radiator mailing
listradiator at open.com.auhttp://www.open.com.au/mailman/listinfo/radiator


-- 
Cumprimentos,
José Borges Ferreira


- - -
José Borges Ferreira
AnubisNetworks
Rua Alexander Fleming, 5B
1600-054 Lisboa
Portugal
Tel. : +351 21 725 2110
Mobile : +351 91 950
2037jose.ferreira at anubisnetworks.comhttp://www.anubisnetworks.com


Join our community at http://world.anubisnetworks.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.open.com.au/pipermail/radiator/attachments/20090811/8e780701/attachment.html