My friend, you have my sympathy. I feel your pain because I had to do the exact same thing about a year ago for the ISP that I work for, with a granularity of 5 minutes. Ow.

Here is how I did it.

We store RADIUS accounting STOP packets in a MySQL database. It works out to be around a million a day. Each row in the database contains all of the info from the stop packet, including what you need here, stop date and account session time. From those two you can figure out the start time.

My particular need was to break these numbers down by several different things, such as reseller and dialup location (which city they connected to) so that the pointy hairs could find out "How many users did reseller X have on in city Y at N o'clock". You may or may not have to worry about such things, and if you do, it just means that you have your main computing function inside a loop which is looping over the particular items (like cities or resellers).

The actual computation is done something like this: First I build my list of today's information. You could use a hash to store it, I use a temporary database table since my source data is already in a database anyway. Fields I store are username, start time, stop time. Then I have a while ($time < $midnight_tonight) loop that is incremented by 300 (5 minutes, adjust yours according to your needs) with each loop. Inside the loop it pulls all records from the database (or your hash) that start_date >= $time and stop_date <= $time, and inserts the count into a final database table.

Then, any interfaces you need to write can directly query that final, very small hopefully, database table to get the answers for the boss.

My script is around 1300 lines long, and I have not included any code on purpose due to this being written for my employer. If interested, I might can get permission to send you a "sanitized" copy of my code.

Update: If you have a large number of records that can be accessed remotely (such as in a database) then it might be an idea to have two scripts running an seperate machines, one doing times before noon, the other doing times after noon. Since walking through every 5 minutes of the day is more CPU intensive, splitting the load over two servers could help you out. Just an afterthought.

In reply to Re: How to do session times The Right Way by erasei
in thread How to do session times The Right Way by strider corinth

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.