Geek: Web logs

[andygates]

So it comes to pass (thick with irony) that I'm involved in the organisation's web logs and all that jazz. These logs are currently dumping out as text files from the proxy servers, three in all, so each day I get about 1.5Gb (no, really) of logs.

Currently I'm manually importing them into a MSSQL database and, for reasons of management's own, each day's logfile ends up in a separate table, ideal for difficult and tedious analysis. Clearly, I'll be automating that in just a few days' time.

But I mean, a gig and a half daily? That's half a terabyte in a year! I know we're enterprise-class, but that's a stuposterously humungous wodge of data. It's particularly unwieldy when (as has happened) I'm asked to mine it for, say, J Random User's access to see if he's been doing "anything naughty".

It strikes me that there's a trick we're missing. We need historical logs, because evidence of naughtiness is a long-term thing. But a terabyte database in a thousand tables is daft. How do real organisations handle this?

Comments

[thudthwacker.livejournal.com]

How do real organisations handle this?

I'm going to go out on a limb and propose "not using MSSQL." Now, I don't do this sort of thing (and hope I never have to), but I would think that this kind of data would be more suitable to a database that can put all the log data in a single database table (or smallish set of tables -- not one table per day, which is clearly insane), which was designed such that pulling a single user's traffic for a week some time last year out of a couple of terabytes of table data wouldn't be no thang. "Old" data (defined as "data which we don't need to be readily available but which can't be discarded yet") could be moved out of the "current" tables and into some data warehouse arrangement (which I say in such a vague way because I, personally, know nothing about this "data warehousing" thing).

[andygates.livejournal.com]

This witchery of which ye speke is exactly what I've recommended (month tables was my suggestion). It's still a hoaching great mass of data... though we could keep a manageable six months and run the rest to tape.

[thudthwacker.livejournal.com]

Honestly, that doesn't sound so bad -- if you only have to have the last six months of data live, I imagine most database servers will be okay. I thought that you might be in a position to have to answer a question like "How much time has Bill spent surfing LiveGirlsJournal since September of 2004?", which would require, well, rather a lot of live data.

[andygates.livejournal.com]

I have, though. In particular, I had to find "anything dodgy" for someone suspected of certain criminal acts; that data spread over about 4 months, but only because the kiddy-fiddler was a new member of staff.

"Anything dodgy", incidentally, burns the eyes and soul. I have to surf so much porn to check tha tit *is* porn that I may as well be at home :)

[gedhrel.livejournal.com]

Incidentally you ought to have a policy that explains why you keep these logs for so long. Which is to say, the law requires you to have a policy. If there are staff disciplinary aspects then they ought to be applied as uniformly as possible: certainly running logs to tape sounds like you hang onto stuff on the offchance that you need a reason to fire someone later. There ought to be a statute of limitations on that kind of thing.

I am, of course, a hand-wringing liberal. But I think the miniscule chance that I die in a tube explosion at the hands of lunatics is a risk worth paying to keep our society one in which our every move is recorded for later perusal to "find anything incriminating". I'm reminded of the hatchet job that is done on anyone the police shoot by accident. Or the similar thing Blunkett got the boys of the home office to do on Maxine Carr.

[gedhrel.livejournal.com]

That is to say: evidence of naughtiness is NOT a long-term thing, unless your directors are using the web proxy to embezzel billions. And your financial systems ought to be catching that anyway.

[andygates.livejournal.com]

They're still drafting the policy. I generally share the hand-wringing liberal approach, especially when seeing the casual daily abuse that can go on when logs and the like aren't wiped when they're no longer needed. "Way-hey, X likes fat birds!" around the office is clearly unprofessional.