[squid-users] [Off Topic] Squidalyser - Not populating the mysql database??

Hi All,

 I have squidalyser installed on the current debian (3.1 - Sarge)
distro. It is on the same box running squid and all modules required
for Active Directory Authentication etc. Everything except squidalyser
is working correctly.

Mysql and Perl are all installed and functioning happily.

Squidalyser seems to be working.. sort of.

Squidalyser is not complaining at any stage. If I use the included
test data, everything checks out - I can query based on this test data
etc and all working.

When I clear the database of the test data and manually run
squidparse.pl it tells me that it imported a number of records etc.
Seems like it is working properly, but it is not populating the database!
(Note: If I manually populate the db from the command line - with
random test data- this works and I can report on it using web
interface. Running squidparse.pl deletes all of this data leaving a
completely empty db)

Db Usernames and passwords are correct.
I have tested squid to using both the 'squid native format' as well as
the 'standard http format'.

This seems to make no difference..

I have never used perl before, but am confident that I can follow the script.

The script seems to want to extract/store usernames in the rfc931
field. - This is parsed as the second field from the access.log file.

My sqid logs are not populating that 'field' with any usernames, only
a '-'. The usernames appear in the third field of the access.log file.
The squidparse.pl script hardcodes a '-' for this value so I loose all
username related data.

I have changed the script to either hardcode some test data, or
populate the rfc931 field based on the actual username field from the
access.log file.
This has not done anything top solve any mysql db population problems..

Using default squidparse.pl I get the following logs:

Simple Access Log: (In squid's standard http_server output)
-------------------------------------------------------------
10.0.0.143 - - [20/Jun/2006:11:06:04 +1000] "GET
http://www.google.com.au/ HTTP/1.0" 407 1839 TCP_DENIED:NONE
10.0.0.143 - - [20/Jun/2006:11:06:04 +1000] "GET
http://www.google.com.au/ HTTP/1.0" 407 1843 TCP_DENIED:NONE
10.0.0.143 - test.NTUser [20/Jun/2006:11:06:05 +1000] "GET
http://www.google.com.au/ HTTP/1.0" 200 3916 TCP_MISS:DIRECT
10.0.0.143 - - [20/Jun/2006:11:06:19 +1000] "GET
http://mail.google.com/mail/? HTTP/1.0" 407 1851 TCP_DENIED:NONE
10.0.0.143 - - [20/Jun/2006:11:06:19 +1000] "GET
http://mail.google.com/mail/? HTTP/1.0" 407 1855 TCP_DENIED:NONE
10.0.0.143 - test.NTUser [20/Jun/2006:11:06:20 +1000] "GET
http://mail.google.com/mail/? HTTP/1.0" 200 752 TCP_MISS:DIRECT
10.0.0.143 - - [20/Jun/2006:11:06:52 +1000] "POST
http://mail.google.com/mail/channel/bind? HTTP/1.0" 407 1887
TCP_DENIED:NONE
10.0.0.143 - - [20/Jun/2006:11:06:52 +1000] "POST
http://mail.google.com/mail/channel/bind? HTTP/1.0" 407 1891
TCP_DENIED:NONE
10.0.0.143 - test.NTUser [20/Jun/2006:11:06:53 +1000] "POST
http://mail.google.com/mail/channel/bind? HTTP/1.0" 200 472
TCP_MISS:DIRECT
---------------------------------------------------------------

Mysql log from above data only (after running squidparse.pl)
---------------------------------------------------------------
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
# at 4
#060620 11:59:10 server id 1 log_pos 4 Start: binlog v 3, server v
4.1.11-Debian_4sarge4-log created 060620 11:59:10 at startup
# at 79
#060620 12:03:52 server id 1 log_pos 79 Intvar
SET INSERT_ID=10974;
# at 107
#060620 12:03:52 server id 1 log_pos 107 Query thread_id=5
exec_time=0 error_code=0
use squid;
SET TIMESTAMP=1150769032;
INSERT INTO logfile (remotehost,rfc931,authuser,request,status,bytes,time)
 VALUES ( '10.0.0.143' , '-' , '-' , 'http://www.google.com.au/' ,
'407', '1839' , '1150769019' );
# at 335
#060620 12:03:52 server id 1 log_pos 335 Intvar
SET INSERT_ID=10975;
# at 363
#060620 12:03:52 server id 1 log_pos 363 Query thread_id=5
exec_time=0 error_code=0
SET TIMESTAMP=1150769032;
INSERT INTO logfile (remotehost,rfc931,authuser,request,status,bytes,time)
 VALUES ( '10.0.0.143' , '-' , '-' , 'http://www.google.com.au/' ,
'407', '1843' , '1150769019' );
# at 591
#060620 12:03:52 server id 1 log_pos 591 Intvar
SET INSERT_ID=10976;
# at 619
#060620 12:03:52 server id 1 log_pos 619 Query thread_id=5
exec_time=0 error_code=0
SET TIMESTAMP=1150769032;
INSERT INTO logfile (remotehost,rfc931,authuser,request,status,bytes,time)
 VALUES ( '10.0.0.143' , '-' , '-' , 'http://www.google.com.au/' ,
'200', '3960' , '1150769019' );
# at 847
#060620 12:03:52 server id 1 log_pos 847 Intvar
SET INSERT_ID=10977;
# at 875
#060620 12:03:52 server id 1 log_pos 875 Query thread_id=5
exec_time=0 error_code=0
SET TIMESTAMP=1150769032;
INSERT INTO logfile (remotehost,rfc931,authuser,request,status,bytes,time)
 VALUES ( '10.0.0.143' , '-' , '-' ,
'http://b.mail.google.com/mail/channel/bind?' , '200', '67259' ,
'1150769027' );
# at 1122
#060620 12:03:52 server id 1 log_pos 1122 Intvar
SET INSERT_ID=10978;
# at 1150
#060620 12:03:52 server id 1 log_pos 1150 Query thread_id=5
exec_time=0 error_code=0
SET TIMESTAMP=1150769032;
INSERT INTO logfile (remotehost,rfc931,authuser,request,status,bytes,time)
 VALUES ( '10.0.0.143' , '-' , '-' ,
'http://b.mail.google.com/mail/channel/bind?' , '407', '1893' ,
'1150769027' );
# at 1396
#060620 12:03:52 server id 1 log_pos 1396 Intvar
SET INSERT_ID=10979;
# at 1424
#060620 12:03:52 server id 1 log_pos 1424 Query thread_id=5
exec_time=0 error_code=0
SET TIMESTAMP=1150769032;
INSERT INTO logfile (remotehost,rfc931,authuser,request,status,bytes,time)
 VALUES ( '10.0.0.143' , '-' , '-' ,
'http://b.mail.google.com/mail/channel/bind?' , '407', '1897' ,
'1150769027' );
# at 1670
#060620 12:03:52 server id 1 log_pos 1670 Query thread_id=5
exec_time=0 error_code=0
SET TIMESTAMP=1150769032;
DELETE FROM logfile WHERE time < 1148349832;
---------------------------------------------------------------

WHY IS MYSQL NOT POPULATING THE DATABASE??

- Chris.
Received on Tue Jun 20 2006 - 17:51:40 MDT