[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

Marie von Ebner-Eschenbach<http://www.brainyquote.com/quotes/authors/m/marie_von_ebnereschenbac.html>
- "Even a stopped clock is right twice a day."

On Mon, Aug 10, 2009 at 3:27 PM, Jim <jim at scusting.com> 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 list
> radiator at open.com.au
> http://www.open.com.au/mailman/listinfo/radiator
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.open.com.au/pipermail/radiator/attachments/20090811/cb304251/attachment.html