Logfile analysis

[iking]

When we needed to find out what a pupil has been viewing on the web, we used a marvelous tool written by Andrew Virnuls (I hope I have spelled his name corrrectly) called Proxy. It was designed to take MS proxy server logs and search for usernames or "bad" words. Unfortunately we now have logs which are in Extended Squid format, and although I found Webalizer, it seems geared to analysis of server traffic, not spotting what naughty pupils have been up to. I've temporarily set up a simple Access database which converts unixtime to "normal" time, and finds entries by uername IP address etc, but it is a bit clunky and certainly not very user friendly. Does anyone know of a tool to do this job (or know if Andrew has written anything similar which will deal with this format?

 

Ian

[Geoff]

http://squidalyser.sourceforge.net/

 

You'll need a webserver that understands PHP and has MySQL. A LAMP solution is probably your best bet there.

[leuven]

We too use squid

We used to use something called Squidalyser (or similar) but found it very processor intensive. It automatically updated a MySQL database every day, and allowed searches by username. Also, you can then choose to see all sites per username, all traffic, or just images. You can also set up groups of users or keywords to search for. I guess it would work best with a dedicated machine to run the database - running it on the same machine as Squid got us lots of 'slow internet' reports.

Right now, I've got a couple of enthusiastic uni students manually checking the logs in their spare time, using 'grep' on linux Works well. Also, I have been invading ICT lessons to do a demo of how we check the logs, and how we can see exactly what they did. No problems recorded since!

I'm thinking of getting the head of ICT to build my demo into the curriculum. Will save me lots of time if the kids take it on board.

[iking]

We are running all this on an RM Connect system with NT servers. The machine which creates the logs is running linux, but we can't install anything on it, so squidalizer isn't an option for us. Anyone know of a windows alternative?

[Geoff]

Apache + PHP + MySQL will run on Windows too.

[DMcCoy]

Apache + PHP + MySQL will run on Windows too.

 

So does Squid 8)

[Alex]

Apache + PHP + MySQL will run on Windows too.

 

So does Squid 8)

 

off topic

 

/me looks at Avatar

 

What corp/char?

[DMcCoy]

Apache + PHP + MySQL will run on Windows too.

 

So does Squid 8)

 

off topic

 

/me looks at Avatar

 

What corp/char?

 

off topic reply (it involves computers I guess)

 

Didn't know if anyone would recognise the EVE avatars

 

my in game name is Windle Poons and I'm part of PIE Inc.

 

 

On topic

 

I just use Wingrep on the ISA logs.

[Alex]

Apache + PHP + MySQL will run on Windows too.

 

So does Squid 8)

 

off topic

 

/me looks at Avatar

 

What corp/char?

 

off topic reply (it involves computers I guess)

 

Didn't know if anyone would recognise the EVE avatars

 

my in game name is Windle Poons and I'm part of PIE Inc.

 

 

On topic

 

I just use Wingrep on the ISA logs.

 

Woah PIE rock man

 

Was talking to one of your guys at the fanfest, which reitterated this further.

 

<< Gunstar Zero, Reikoku, BoB.

 

too much to drinkies ofo to bedos

[iking]

Back on topic again.... I'm still struggling with this - I have the source - code for an ms proxy server log analysis program, but so far I have to convert extended squid format log files to csv format before it will read them. (The fields are in a different order of course but I've managed to fix that.) I need an example ms proxy server log file to play around with. Can anyone supply me with one?

[Geoff]

MS Proxy was dead and buried years ago I thought. Do you mean ISA server?

[Dos_Box]

It might be one of those 'Fraken-networks' where you have to power them up at midnight and spend hours scratching your head as the kids run rings around the 8 year old security model.

[Ric_]

@iking: Do you mean ISA or the MS Proxy log format (which you can select somewhere in ISA IIRC)

[iking]

Thanks - I'm looking for a n example log in ms proxy format

[Ric_]

@iking: If you setup your proxy to produce reports in this format your problem should be solved. Pester (PM) me when I'm at work and I'll have a look for the setting - off the top of my head I can only remember that it's in the same place as where you can tell it to log to an SQL database - something that would be more powerful if you ask me.

[iking]

The machine which is creating the logs is a Cisco Content Delivery Engine - a proprietary item over which we have little or no control, so I think that we have to accept the logs as they come. This item is a pain in the butt - every school in Scotland got one - supposedly to speed up Internet access and allow teachers to pre-book content for cacheing and to publish resources, however all we have noticed is a spectacular reduction in functionality as our connection now goes through the corporate IT connection.

[Ric_]

Sounds like an archaeic piece of kit. If you have to use it, stick a Linux box in front of it and use the Cisco thing as an upstream server.

 

In reality, the kit is probably just running an embedded version of Windows with MS Proxy server or ISA.

[RobC]

@Iking:

 

We've got our Cisco box in, but nothing's ever been done about it - no training, no software installed for it, not even a manual to look at, nothing.

 

Have you actually gone live with yours? And is it all bad, or are there some good points to it?

[Ric_]

@RobC: I'm sure that Cisco will have manuals on their web site - although it is the worst site in the world for finding stuff on! :?

[iking]

Well I managed to finish it eventually!

 

Andrew Virnuls kindly send me the source code for his program and I've adapted it to read logs from our SSDN content engine (extended squid format) you can search the log for a username, or by using a word list (text file) and it will output the results in Excel or Notepad. Ideal if you want to track the activities of a pupil, or just check for badness (and if you put the RM filter url in the wordlist you can check for attempted badness ) If anyone wants the program just drop me a note - probably more useful if you are in Scotland and have had a Content Engine installed as part of the SSDN

 

Ian

[Dos_Box]

@RobC: I'm sure that Cisco will have manuals on their web site - although it is the worst site in the world for finding stuff on! :?

 

Worse than Becta?