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

[Hugh Irvine]

Hello Jim -

Quite right - what I describe will only be accurate to whatever period the accounting alives are set to.

If the accounting alives are set to anything more than an hour the numbers will be useless.

And as you say, logons and logoffs will skew the results.

Any solution will have the same problem.

regards

Hugh


On 16 Dec 2009, at 18:58, jim at scusting.com wrote:

> What value would you store in the 24 rows - the bytes transfered 
> counter? Wouldnt you need a 25th column to store the last result of the 
> previous day?  To work out the usage in the 1st hour you would need to 
> know the value of the counter the previous hour in order to subtract 
> that I believe.
> 
> And what happens if a user logs off and on during the 24 hour therefore 
> resetting their counters?
> 
> Jim.
> 
> Hugh Irvine wrote:
>> 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 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.