[RADIATOR] Extract hourly data transferred by day from ACCOUNTING table

[Hugh Irvine]

Hello Kieran, Hello Paul -

You could simply add 24 columns to the user records and add an AcctSQLStatement to update the current hour for each accounting request for each user.

The special character to use for the column is "%H".

Each user record will then have the last 24 hours traffic totals.

regards

Hugh



On 16 Dec 2009, at 12:16, Kiernan McColl wrote:

> Hi,
>  
> I notice questions like this pop up occasionally.
>  
> It will take more than just SQL to pull this off. You will probably want to create a new table suitable for storing the hourly totals for users. Then write a script/daemon to go through the records in the existing ACCOUNTING table, work out how much was transferred in each hour for each individual session and enter it into the new table.
>  
> Kiernan
>  
>  
> From: radiator-bounces at open.com.au [mailto:radiator-bounces at open.com.au] On Behalf Of Paul Lattimer
> Sent: Wednesday, December 16, 2009 8:13 AM
> To: radiator at open.com.au
> Subject: [RADIATOR] Extract hourly data transferred by day from ACCOUNTING table
>  
> Hi List,
> Are there any reporting or query resources out there on the net that someone can point me to for the following problem?
>  
> BACKGROUND:
> My radiator servers dump accounting info to a mysql table called “ACCOUNTING”
> The data consists of hourly updates (Alive) AND Start/Stop/Alive accounting info.
> Gigaword accounting is turned ON
>  
> I can stumble my way through SQL queries, but I am not a DBA.
>  
> TASK:
> I’d like to grab data transferred (up and down) by HOUR for a particular USERNAME for a particular DATE
> So I will end up with 24 rows with the amount of data transferred in that hour.
>  
> TOOLS:
> mySQL
> Perl
>  
> DATASET:
> I’ve attached a sample of data for a 25 hour period (1 hour included from the previous day to find the starting amount of data)
> Data is in unix format CSV
>  
> Can anyone suggest further reading or some sample code for me?
>  
> Thanks in advance!
>  
> Paul
>  
>  
> _______________________________________________
> radiator mailing list
> radiator at open.com.au
> http://www.open.com.au/mailman/listinfo/radiator



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.
Includes support for reliable RADIUS transport (RadSec),
and DIAMETER translation agent.
-
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.