[squid-users] access.log to squid solution

Hi All,

I have create a a set of perl files that can parse a access.log file and stores
the data into a relational database (PostgreSQL).
I have seen many of this packages but this one will NOT create reports, there
are already many programs that can do just that!!!

Currently it consists of a parser and a retreiver, the parser will simply parse
the access.log file and store the data into the database. It will also store
some additional elements for better searching like the FQDN of the client, a a
TIMESTAMP field so PostgreSQL can use a WHERE clause on the column. Some data
is seperated into seperate tables to keep the database small and get faster
searching into it.

The retreiver will restore from the SQL back ento a squid type access.log file,
this is great so other analysis software can use it to generate the stats based
on YOUR rules.

Here is how you can do it:

Step1: Invoke the parser by using this command
~$whale-parser.perl
This will store the current access log into the database, you can call it as
many times as you want on a day and at least just before squid rotates the log,
it will detect the last line inserted into the db

Step2: Extrect a squid type log from the database:
~$whale-retreiver -o /tmp/squid.out
Will return the complete database ( not very usefull....)
whale-retreiver.perl can do some advanced (SQL based ) filtering like this:

# will get yesterday's lcomplete log
~$whale-retreiver -o /tmp/squid.out -f "(DATE = YESTERDAY)"

# Will get yesterdays log exept between 12:15 and 1300 (our lunch break..)
~$whale-retreiver -o /tmp/squid.out -f "(DATE = YESTERDAY) AND NOT (TIME
BETWEEN '12:15' AND '13:00')"

# To get only these users in the subdomain 'tek.*' (example
tek030.domainname.nl or tek00001.domainname.nl') generated yesterday
~$whale-retreiver -o /tmp/squid.out -f "(DATE = YESTERDAY) AND NOT (AND (DOMAIN
LIKE 'tek%.domainname.nl'"

I also use it to generate statistics with webalizer and so I can create
statistics for a seperate groups within our office ( also the lunche break is
filtered out..)

If anyone is interested ytou can mail me or take a look at
http://sourceforge.net/projects/whale-sqlfilter/

I didn't release any files yes but on the homepage you can download all
perl/sql files.
Since this is still alpha software it doesn't have a nifty installer yet so I
expect you to have some basic PostgreSQL and perl skills.

Lett me know what you think and if it usefull at all, if you think it will
start a flame please send me the info at whale@rvt.dds.nl

Ries van Twisk
 
 
Received on Wed Feb 26 2003 - 04:27:43 MST