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

[Kiernan McColl]

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


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.open.com.au/pipermail/radiator/attachments/20091216/f0f6aa2c/attachment.html