Squid logs

[srochford]

We use Squid for some of our proxy servers and I'm trying to make more sense of the logs.

A relatively new feature is the logformat part of squid.conf - amongst other things this lets you include the logtime as something recognisable rather than the Unix way of counting seconds since "day zero"

The default for "real" time gives

11/Apr/2007:15:17:48 GMT Daylight Time

which is readable but won't import nicely into SQL (and if Squid could log to SQL then life would be so much better :-))

Has anyone used this? If so, can you tell me what I put in place of %tl to get a format like 2007-04-11 15:17:48

there are endless web sites which just reproduce the squid.conf file but it doesn't have any examples in it and I'm now stuck :-)

[Geoff]

Here's the developer docs.

http://devel.squid-cache.org/customlog/logformat.html

And here's the actual patch if you want to refer back to the code.

http://devel.squid-cache.org/cgi-bin...omlog-2_5?s2_5

[tom_newton]

seconds-since-the-epoch *should* go into SQL ok...

What DBMS are you using? If it's mysql I can probably parse that date in SQL for you

[srochford]

I'm using Microsoft SQL but if you can show me how to read a line into MySQL then I'm sure I can make it work with MS SQL.

I'd like to make it work with a "human" date form just because then I can browse what's going on more easily - and because the docs say you can do it I'd quite like to work out how to do it (increasing the number of things I know always seems like a good idea).

I've looked at the developer docs but (as is all too often the case!) there are no examples there of how you use it. I have had a look at the code but I'm afraid it's way too complicated for me :-)

[tom_newton]

Steve,

First thing is to make sure the column you're importing into is of the DATETIME datatype. On Mysql that will usually accept a seconds-from-epoch OR a formatted date.

I think if you can't get SQL server to accept seconds, then your best bet is to use the squid log to get a date in the right format:
%{%F %T}tl

that was constructed using Geoff's link:
http://devel.squid-cache.org/customlog/logformat.html
and the strftime man page
http://man.he.net/man3/strftime

Finally, getting mysql to parse the date, you could use:
STR_TO_DATE(str,format)
see: http://dev.mysql.com/doc/refman/4.1/...functions.html

but I doubt SQL server does that.

Alternatively, take a look at SmoothWall's content filtering solution - that has full log analysis etc. built in

Tom

[srochford]

Thanks - I'm now moving in the right direction - I now have dates I can recognise!

logformat squid %{%Y-%m-%d %H:%M:%S}tl %6tr %>a %Ss/%03Hs %<st %rm %ru %un %Sh/%<A %mt

has worked for me and gives results like:
2007-04-12 15:52:20 31 10.0.10.93 TCP_MISS/304 293 GET http://www.edugeek.net/images/blocks/visitors.gif - DIRECT/213.171.219.189 -

How would I have known to use the {braces} - the .conf examples say "{arg} argument such as header name etc" which doesn't seem relevant - or do I just know what it's about??

I've now realised that the real reason I gave up on this before was that I can't parse the rest of the line easily because it's not fixed width or delimited - I presumably have to use some kind of regular expression to extract what's there.

I will have a look at Smoothwall some day but it's Linux which just puts me off - everything is just such hard work for a mere mortal like me :-)

[CyberNerd]

lots of scripts for squid logfile analysis here:
http://www.squid-cache.org/Scripts/

I will have a look at Smoothwall some day but it's Linux which just puts me off - everything is just such hard work for a mere mortal like me Smile

no linux knowledge required for smoothwall - its all done through a perl based GUI. there is a shell if you want it though. btw, I have a team of 12yr olds who arn't put off by linux, so I don't see why you should be.

[Geoff]

How would I have known to use the {braces} - the .conf examples say "{arg} argument such as header name etc" which doesn't seem relevant - or do I just know what it's about??

It was clearly explained in the dev docs...

%{format}tl
Date of request, strftime format (localtime)

I've now realised that the real reason I gave up on this before was that I can't parse the rest of the line easily because it's not fixed width or delimited

So doesn't whitespace count as a delimiter?

[srochford]

Now you highlight that section I can see what it's supposed to mean; it's one of those areas where an example or two could save much wasted time. For someone who's not a C or Perl programmer most of the documents might as well not exist - it's too hard (at least for me) to work out what they mean!

whitespace is obviously a delimiter but I ideally I want to use SQL bulk copy (bcp) to import the file and this wants a single (or at least a fixed number of characters) as delimiter. Haven't looked a bit more, though, I think I can just add (say) backslashes in between each field and then tell bcp to recognise that.

thanks for your help.

[tom_newton]

Steve - add something like a tab - that may be achieved with \t or %t in the squid format.

Thats one of the bad things about most linux-docs - including man pages, they lack examples, but examples (good ones) are HARD to write!

If you wanna look at Smoothie as a test, do feel free to call us and ask for an eval. mean time, i'm sure Geoff and I can get you through parsing some logs if you keep bashing away ;-)

[srochford]

Thanks for all the help - I've now got things working pretty well.
I've written about what I've done at http://techinfo.cnwl.ac.uk/Squid%20Proxy/ - at some point perhaps someone else will google for squid logformat and find it useful :-)

I had tried using \t and %t but they don't work so for now I've just used a single backslash. I'm sure that will come back and bite me at some time but it's working now so I'll leave it alone!

[tom_newton]

Steve - well done, and good on you for documenting it for others.

Just out of "professional interest" are you doing any filtering, or just logging?

[srochford]

A bit of both.

We use ISA 2004 for our main proxies and they are then fed from the Janet filtering service (run by RM).

We use Squid for bits of software which won't work nicely with NTLM authentication from ISA but also because Janet filtering blocks all exe files so downloading drivers etc becomes a problem.

What I've found is that a few people (staff!!) who shouldn't know about our Squid proxies have found them and are abusing them so I've put a small amount of filtering in place but the logging is more important so that I can go and smack heads of people who are not playing nicely :-)

Documenting is one of my big things - I'm keen to make sure that when I get knocked down by a bus that others can find out what I've done. Internally we use Sharepoint but for stuff which is not confidential I've tried to put it on that web site. I don't always get time to put things there and sometimes what I do is too specific to my college but I am trying!

I will look at Smoothwall one day - it's just that there are always more urgent things to do ..