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.

Fortunately, my needs aren't going to be nearly that complex, nor the data at that sort of volume. I really like the idea of using a database view or temp table for it; I don't use databases as part of my programming logic much, and I ought to do it more. If this problem gets that intense, I certainly will.

Thanks for the sympathy, and the response. =)
--

Love justice; desire mercy.

I don't know anything about RADIUS, so I can't comment there, but I do know a bit about the Web, so I'll skew my response accordingly. About a year and a half ago, I was asked to do something very similar, but for what I considered to be unethical reasons: billing paying customers for the time they spent on our Web site (different company from where I now work). The Web, of course, is a stateless protocol and there is no way to tell if someone if a given session reflects use of a site.

The problem with most of your options: what if someone leaves their browser open and walks away, or what if the browser crashes? The person isn't recorded as being logged out and your session data is not accurate. Heck, what if someone logs in, gets called away for a meeting a logs out when they get back. That's an hour of "usage" that isn't. What I wound up doing is parsing my log files, checking the log in and log out times and, if they didn't log out, assume that they had used the service for a 'minimum' period of time. Since I was using Web access logs, I could also verify roughly when they were active. However, I was writing a command line tool that didn't require instant results, so I had the luxury of sacrificing performance.

Perhaps a better way of looking at this problem is considering what the real problem to be solved is? For example, if you need to determine traffic, a better metric may be to track 'hits' (which raises a whole slough of different questions, but I digress). If you needed to track what people are interested in, "hits per page" may be better.

Cheers,
Ovid

Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

Ironically enough, my last project (completed yesterday and now in the polishing phase) involved logging web hits. =)

I had meant my question to be general because it applies to a number of scenarios: RADIUS and the web are only two of them. The problem I need to solve specifically (for which I'm currently using one of the methods I described) is actually to find out how many sessions are open on the web server at a time. They're not web sessions in the sense of those tracked by IP, but login sessions to an application server. Since the server keeps a certain amount of information in memory for each open session (each of which expire after a certain amount of time) we need to know how many sessions it has open.

The admin console has an interface that'll just tell you, but it isn't scriptable. The only other option is to use the session logs, which log session creation and destruction. It's accurate, but annoying. =)

I totally agree with your point on ethics, by the way. Using your method, I can assume a logout after X as well, which works for my application. As a matter of fact, the server will log that as a session close; I'm just looking for a Better Way.
--

Love justice; desire mercy.

For the "How many people on between time x and y", I don't see any way around storing a list of begin-end pairs extracted from the weblogs and computing your functions from these.

For the math involved, you wish to consult some of the open source log analyzers, such as analog.

-Mark

I'm often asked to to supply quantative data to suits. 90% of the time they don't actually care, so I stopped trying. Instead I just grab instance data and I graph it. And the suits love it. I've done graphs for mail spools, ups load, and of course all the bandwidth stats, etc., etc.

The tool I use called CACTI, and uses RRD tool to do the graphing. If you can write a script that returns some numbers, cacti can handle it. Alas it's not perl.

Cacti is available here.

--
TTFN, FNORD

xaphod

As for how to make the parsing efficient, I suspect it will be fast enough for most situations to just start at the beginning and go forward until you hit that time, but you could also use a binary search algorithm. This is sorted data, so you can take advantage of that.

How about something like this? You want to avoid having to parse the whole log to find an instant in time, but you don't want to save all the instants in time that you could possibly need.

A compromise would be to have a process that follows the log (or parses it from the start), saving checkpoints once in a while. So say, once a day, the process says that "Right now, the following 12 people are online, and they logged on at the corresponding times: ...report...".

Then, when you want to find a specific interval, you only need to parse from the preceeding checkpoint, until the end of the interval. Depending on how frequently you checkpoint, this could be very simple.

-- Dan

For intervals, I'm impressed: this is a really inventive solution. I definitely think it would work well for that. The only problems I see are if I'm looking for a point in time, or if a user is logged in for a shorter period of time than the interval of the checkpoints. The point won't be accurate because logoff times are only available by the hour (we don't know if the user was logged off by, say, 1:32:56). The short time won't work because no data will be recorded on it at all.

I think a slight modification of this system would work well for all situations. At each interval (each hour, let's say) let the system log all the users online and the time they logged on. The same record is updated with the stop times of all users who off during that hour. That way, to find out how many users were on at 1:32:56, the script only needs to look at the entry for 1:00. Any users logged on at 1:00 who hadn't yet logged off at 1:32:56 were online at that time.

The only problem left in that scenario is of users who log on after 1:00 and off before 2:00, leaving only a logoff record in the 2:00 entry. If each entry logs any logons for that hour as well, the problem is solved with just a little more computing, and still looking only at one record.
--

Love justice; desire mercy.

I think either I'm not understanding where you see the problem, or you're not seeing my solution :)

The only problems I see are if I'm looking for a point in time, or if a user is logged in for a shorter period of time than the interval of the checkpoints.

Why is there a problem? Say you want to know who was logged on at 13:05 (with hourly checkpoints). So your log will look something like this:

...
12:58:03 User 1 sign on
12:59:03 User 1 sign off
12:59:25 User 2 sign on
12:59:50 User 3 sign on
13:00:00 REPORT: online: (2-12:59:25,3-12:59:50)
13:02:23 User 4 sign on
13:03:12 User 3 sign off
13:04:23 User 4 sign off
13:04:34 User 5 sign on
13:06:52 User 5 sign off 
...
[download]

(The REPORT line is probably external to the log file, and would probably keep the offset in the file where you should start reading from)

First, the program needs to look at the checkpoint preceeding the beginning of the interval (or the point in time, same thing) - so it knows that at 13:00, there were 2 users online. Then it starts reading the log file from after the checkpoint, keeping track of logons and logoffs, until it passes the end point of the interval (or the point in time). So when it reaches 13:06:52, it stops, and just dumps out it's data:

Users online: 2
User 2 (since 12:59:25)
User 5 (since 13:04:34)
[download]

I think my point is, the checkpoints only keep record of historical data up to that point, so you don't have to parse the log file from the beginning of time. It just "primes" your data structure. But from the checkpoint on, you just parse the log file as usual, modifying your data as you go along.

-- Dan

for($date){
   ($h,$m,$s)=split/:/;
   s/\:\d\d$//;  #throw out the seconds, we don't need them
   $m="00" if ($m<15);
   $m="15" if (($m>=15) && ($m<30));
   $m="30" if (($m>=30) && ($m<45));
   $m="45" if ($m>=45);
   s/\:\d\d\/\:$m/; #replace the minutes with the generalized value
}
[download]

$parseddata{$date}++ unless grep{$_ eq "$date|$session"}@seen;
push @seen,"$date|$session";
[download]

my @keys = sort keys %parseddata;
my @values;
for(@keys){
 push @values,$parseddata{$_};
}
my @data=([@keys],[@values]);
my $graph=GD::Graph::area->new(1000,600);
#set all the graph options(labels, colors etc...)
my $gd = $graph->plot(\@data);
open(GRAPH, ">/graphs/$datestamp.png) or die "Can't open output file f
+or graph: $!";
binmode GRAPH;
print GRAPH $gd->png;
[download]

We use Cricket graphing (like MRTG) to check dialup line usage for all of our gear. This tells us numerically how busy we are. We have a full report for all of our cities statically written to a website and re-gen'd daily.

We also have all the RADIUS accounting data in a sql db and scripts in front of it to check based on username, ip and start/stop date. It's not fast, but it does give us the info we want. The db stores username, ip, start time, stop time and all the other goop with radius. the search page lets you search on username, ip, start and stop times only.

This may not solve your programming problem, but it may give the higher ups enough information to get what they need without you having to break your back and reinvent the wheel 18 times

start_time     , stop_time      , userid
20020901101111 , 20021002121201 , fred
20020904102010 , NULL           , johnwhoneverlogsoff
[download]

select count(*) from sessions where start_time < $end_of_segment and (
+stop_time > $start_of_segment || stop_time IS NULL);
[download]

I would have thought such a table would be a common part of an ISP setup, but if it isn't, you may find the table advantageous for other kinds of session related data as well.

In either case, the only task left is to take your logs and shunt them into a database table of the above format (matching as it goes the appropriate stops with starts, although I am told that since the session time is included in the stop message, you could also just scan stops and calculate the start time from that), something that could be done once per hour, per day, or on demand as necessary. A bit of intelligence in the parser will allow it to continue parsing from where it left off last time, resulting in little CPU burn to get up to date. Hope that helps.

That is really just the second option I suggested (translate "data structure" to "database"). I'll admit that it's faster than I had been envisioning, since I wasn't thinking exactly along the lines of databases, but it still isn't what I think of as an elegent solution, hence my initial post. I'm more interested in this from a programming theory standpoint than from a practical one. I guess I should've made that more clear in my original post: I want to know if this be done in Perl, without the use of other technologies.

Another assumption you (and others) have made is that I'm at an ISP, and that I'm using RADIUS. I'm not. =)

On the plus side, your answer is certainly the sort the person I'm doing this work for would like. It's simple, and it uses technology already available to us. Thanks for taking the time to respond with it.
--

Love justice; desire mercy.

I think the primary issue you have is that you have an unbounded problem. At any given point in time, you cannot state "a session operating at this point in time could not possibly have started any earlier than X", you have no choice but to parse the entire logset up until X in order to determine what sessions are open.

Now, you can distribute this cost as you will, either paying it every time you want an answer, or in manageable blocks via checkpointing, database insertion, whatever, but there is no way to avoid paying that cost, its built into the problem.

The solution I offered is the most elegant I can think of, in that it provides an arbitrary insertion model (no particular limitations like once a day) allowing you to distribute the fixed cost as you see fit, and a storage method that provides very low O access to the data you require.

Except for extremely simple data sets, there are no methods I'm aware of that can improve on this in the general case.

There are special cases wherein the unbounded nature is not so relevant. For example, consider the case where there is only one user, in this situation it is unnecessary to start from the begining, you can start from the X point and simply run backwards through the logs until you find a start or end for that user, then you can say "1", or "0".

For a small number of users, it may be practical to extend this to track the current status of each user. This still worst-cases to parsing every line of the logs from X to the start in the instance that a given user has never logged in. With a large number of users however you increase the likelyhood of the worst-case scenario.

It is also possible to bound the answers statistically. In order to do this, you run a pre-processor on your current log set which determines a bell curve or equivalent for the average session times. You then tell your parser "I would like the number of users at time X to a 95% certainty", this allows your parser, with the help of the averages, to calculate how far it must trace back in the logs at the most to achieve that level of certainty, given a number of users unaccounted for at that point.

However, as they say, keep it simple. Daily log parsing, insertion, and then nice simple select queries makes this by far the most effective and dynamic solution, and one that is prone to being automated by 10 lines of PHP so your executives can pull up their web browser and make their own request, instead of calling you :)

This attribute is a unique Accounting ID to make it easy to match start and stop records in a log file. The start and stop records for a given session MUST have the same Acct-Session-Id. An Accounting-Request packet MUST have an Acct-Session-Id. An Access-Request packet MAY have an Acct-Session-Id; if it does, then the NAS MUST use the same Acct-Session-Id in the Accounting-Request packets for that session.

(http://www.freeradius.org/rfc/rfc2866.html#Acct-Session-Id)

So, for each accounting request packet, you've got a timestamp, and know how many users are on the BRAS at ayone point in time, simply by counting the acct-sess-id.

Be warned tho', the amount of data can be huge, and you may want to look at something like partitioning, or summary tables for historical data.

Update: Further to this, i never explicitly mentioned its prolly a good idea to stick it into an RDBMS, where you can do any number of different queries to produce different statistics at the drop of a hat. You also have the advantage of being able to leverage of any number of dba utilities to maintain the database.

On trick for approaching this problem is to consider who is asking the question, and how much accuracy they really need. (They might say they want to be able to ask for the number of sessions at an arbitrary time, but will the go away happy if you give them a session count to the nearest five-minute interval?)

And if your "higher ups" are anything like what mine have been, they communicate using PowerPoint. Consider answering their questions in a similar way -- with a one page graphic.

Instead of building a fancy query facility, scan the logs once, and emit enough data to feed into your favorite graphing tool (I've used Excel) to produce a graph they can look at. Dropping out a datapoint every 5 minutes gives you more than enough data to build a nice line chart. You can even automate the process via Win32::OLE.

My bosses have appreciated it when I've given them stuff they can copy/paste into a PowerPoint slide.

Well, yeah. The thing is that they weren't asking the question I posed, really. =)

I know what they're looking for, and for their purposes, one of the three methods I mentioned would serve. But none of them is the Best Way. I've had to deal with this problem for years, and my needs have changed over the course of time. I want something that will work for all of the situations I've been in.

Think of it this way: you can write a script to do something once. I've done that (I think) twice for this kind of problem. What I want is a way to help everybody do this kind of calculation. So I'm thinking of the problem more in this sense: "how can I make this a module?" Somebody will need that high granularity at some point. It might be me.

You're definitely right in terms of work (and also relationships, by the way ;). Slways try to figure out what's really being asked, rather than what's being said: it's always better to understand than just to listen.
--

Love justice; desire mercy.

I would also recommend this document NetGenesis White Paper that describes many different types of metrics that you should find interesting.

Richard

There are three types of people in this world, those that can count and those that cannot. Anon

The best option would seem to be logging the number of user each second, but your concerned with the size of the datastructure this would create.

Does it really have to be so big?

Assuming that you have a reliable way of determining when a user session ends if they just disconnect without logging off--which is a problem you'd have to solve with any solution--if your maximum user count at any given time is less than 65k, storing a 16-bit count in a tied array for each second of the day is going to take 168k/day. The nice thing is that retrieval would be very fast.

If that's too much, then you could store 16-bit count at the top of each days file showing the number of sessions when the 'day' started and then store an 8-bit/second (or even a 4-bit/second) delta giving a filesize of 84k (42k)depending on how far back you need to keep on-line, this shouldn't be too much. Again the retreival would be fast, although not as fast as you'd need to process the days file from the beginning. With 8-bit delta you would allow for a maximum change of logins of ±128 users in any given second which is probably ample for most sites. If the delta grew beyond this value in any given second, you could write the maximum for the delta chosen in that given second and carry any remainder over to the next, that allows your running totals to remain correct at the cost of minor inaccuracies during periods of heavy usage.

You might even take this a step further and store 7 days (or how ever far back you need to go) in a single file and then rotate through it ensuring that you only ever need a set size datastore updating the total count each time you start to overwrite the deltas. The logic gets a little tricky if you need to get data for periods in the file prior to the last update to the master count, as you would then need to read backwards and invert the signs on the deltas.

The downside of this is that you would have to(potentially) read through through an entire rotation periods data to calculate the current number. However, given say 7 days worth of 8-bit deltas, a quick experiment show that perl can slurp the 600k file in a couple of seconds and apply the deltas to the base count in 11 seconds.

If 16-bits is enough to hold your absolute number of sessions, and you can afford the 168k/day filesize, the retrieval of any given second would be very nearly instantaneous. You could always archive the files to tape or CD if you need to. Even from a CD, the calculation for any second, or even a whole hour would be trivial and pretty fast.

Wow. You really went to a lot of thought with your response. Thanks for taking the time. You're right about storage; it wouldn't be too much on disk. Thinking in terms of a practical solution, the tied array idea is a really good one. I guess I was just looking for a compromise between that sort of solution (storing data each second, which is in most cases a lot more data proportionally than storing data for each logon and logoff) and not having to run through the whole dataset to find the answer. Fortunately, I think I found what I'm looking for.

Thanks for such a thoughtful answer! =)
--

Love justice; desire mercy.

while my vote goes to the DBMS solution, I would like to point out that, if the only question you want an answer to is "how many", you just need to record the time at which variations happen, without no "granularity" at all (and the data set gets much smaller.
This means, keep a running count of sessions and, whenever someone logs in/out update it and write it down (to a db table, for instance). This data should be enough to answer all the questions of the form you mentioned.
Cheers,
alf

Ah, but don't these two answers simplify to solutions 1 and 2 in my original post? The DBMS solution corresponds to solution 2 (though I suppose it would be a bit faster), and the running count solution is the same as solution 1, I think. Am I missing something?
--

Love justice; desire mercy.

Greetings,

Solution 2 does in fact refer to the idea of keeping a running count, however, the idea of "running through" sort of goes away.

I am proposing of keeping a two column (list, table, tabulation...):

Time	Count
00:15:48	2
03:20:12	1
03:30:00	0
07:25:46	4
...	...

Which is a succint (I venture to speculate it may in fact be nearly optimal) representation of the stairstep time function that you are interested in. Of course you have to look at your data set in order to compute it - but that is the minimal requirement for the task at hand, short of oracular intervention (thinking Delphi - the place - not Mountain view). Also you keep your table updated by processing only the events that took place from the latest vaiation you recorded. This could easily be accomplished (on redhat linux) by hooking ino the daily logrotate job.

If this seems unelegant, the proces doing the event logging could perhaps be hacked to also increment/decrement the count as the events arrive. (Of course, in a situation where you may not have reliable termination events - like weblogs - everything beomes more complex...)

The use of a DBMS does in fact mean that you set up a data structure for your data (your #1 idea), but the critical pint is that it is a data structure that is highly optimized for searching, sorting and counting - and that is not a trivial addition. Besides, the kind of tables that other have proposed have the virtue of making many more type of questions answerable by simple SQL means. OTOH, the table outlined above is minimally tuned to answer the "how many" questions without any information loss (entailed by any periodic sampling)

And I should mention that, if I had to do it, I would probably try to set it up in MRTG, which is probably already 75% there. Of course, MRTG does use periodic sampling and reaggregates its data set on the basis of a larger time step for the weekly and monthly stats, which is a lossy process.
Cheers,
alf

---

You can't have everything: where would you put it?