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

[jim at scusting.com]

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?
>
>