+ Post New Thread
Results 1 to 13 of 13
How do you do....it? Thread, Squid logs in Technical; We use Squid for some of our proxy servers and I'm trying to make more sense of the logs. A ...
  1. #1

    Join Date
    Aug 2005
    Location
    London
    Posts
    3,157
    Thank Post
    116
    Thanked 529 Times in 452 Posts
    Blog Entries
    2
    Rep Power
    124

    Squid logs

    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
    Code:
    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 :-)

  2. #2

    Geoff's Avatar
    Join Date
    Jun 2005
    Location
    Fylde, Lancs, UK.
    Posts
    11,812
    Thank Post
    110
    Thanked 585 Times in 506 Posts
    Blog Entries
    1
    Rep Power
    224

    Re: Squid logs

    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

  3. #3


    tom_newton's Avatar
    Join Date
    Sep 2006
    Location
    Leeds
    Posts
    4,485
    Thank Post
    867
    Thanked 854 Times in 675 Posts
    Rep Power
    197

    Re: Squid logs

    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

  4. #4

    Join Date
    Aug 2005
    Location
    London
    Posts
    3,157
    Thank Post
    116
    Thanked 529 Times in 452 Posts
    Blog Entries
    2
    Rep Power
    124

    Re: Squid logs

    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 :-)

  5. #5


    tom_newton's Avatar
    Join Date
    Sep 2006
    Location
    Leeds
    Posts
    4,485
    Thank Post
    867
    Thanked 854 Times in 675 Posts
    Rep Power
    197

    Re: Squid logs

    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

  6. #6

    Join Date
    Aug 2005
    Location
    London
    Posts
    3,157
    Thank Post
    116
    Thanked 529 Times in 452 Posts
    Blog Entries
    2
    Rep Power
    124

    Re: Squid logs

    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 :-)

  7. #7


    Join Date
    Jan 2006
    Posts
    8,202
    Thank Post
    442
    Thanked 1,032 Times in 812 Posts
    Rep Power
    339

    Re: Squid logs

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

    Code:
    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.

  8. #8

    Geoff's Avatar
    Join Date
    Jun 2005
    Location
    Fylde, Lancs, UK.
    Posts
    11,812
    Thank Post
    110
    Thanked 585 Times in 506 Posts
    Blog Entries
    1
    Rep Power
    224

    Re: Squid logs

    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?

  9. #9

    Join Date
    Aug 2005
    Location
    London
    Posts
    3,157
    Thank Post
    116
    Thanked 529 Times in 452 Posts
    Blog Entries
    2
    Rep Power
    124

    Re: Squid logs

    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.

  10. #10


    tom_newton's Avatar
    Join Date
    Sep 2006
    Location
    Leeds
    Posts
    4,485
    Thank Post
    867
    Thanked 854 Times in 675 Posts
    Rep Power
    197

    Re: Squid logs

    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 ;-)

  11. #11

    Join Date
    Aug 2005
    Location
    London
    Posts
    3,157
    Thank Post
    116
    Thanked 529 Times in 452 Posts
    Blog Entries
    2
    Rep Power
    124

    Re: Squid logs

    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!

  12. #12


    tom_newton's Avatar
    Join Date
    Sep 2006
    Location
    Leeds
    Posts
    4,485
    Thank Post
    867
    Thanked 854 Times in 675 Posts
    Rep Power
    197

    Re: Squid logs

    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?

  13. #13

    Join Date
    Aug 2005
    Location
    London
    Posts
    3,157
    Thank Post
    116
    Thanked 529 Times in 452 Posts
    Blog Entries
    2
    Rep Power
    124

    Re: Squid logs

    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 ..

SHARE:
+ Post New Thread

Similar Threads

  1. Moodle logs
    By alan-d in forum Virtual Learning Platforms
    Replies: 7
    Last Post: 21st November 2007, 02:30 PM
  2. NSS 9 - tutor PCs need setup for every user that logs on.
    By Halfmad in forum Network and Classroom Management
    Replies: 4
    Last Post: 23rd August 2007, 03:16 PM
  3. Checking internet filter logs...
    By _Bat_ in forum How do you do....it?
    Replies: 15
    Last Post: 13th December 2006, 04:13 PM
  4. ISA 2004 anonymous in web logs....
    By mullet_man in forum Wireless Networks
    Replies: 5
    Last Post: 6th December 2006, 05:15 PM
  5. ISA server logs
    By krisd32 in forum Windows
    Replies: 4
    Last Post: 27th September 2006, 02:29 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •