log_db_daemon - Database logging daemon for Squid
+Version 0.5.
+
=head1 SYNOPSIS
log_db_daemon DSN [options]
-=head1 DESCRIPTOIN
+=head1 DESCRIPTION
This program writes Squid access.log entries to a database.
-Presently only accepts the B<squid> native format
-
-=over 8
-
-=item B<DSN>
+Presently only accepts the B<squid> native log format.
-Database DSN encoded as a path. This is sent as the access_log file path.
+The script has been developed and tested in the following environment:
-Sample configuration:
- access_log daemon:/host/database/table/username/password squid
+=over 4
- to leave a parameter unspecified use a double slash:
- access_log daemon://database/table/username/password squid
+=item squid-2.7 Squid-3.2
-Default "DBI:mysql:database=squid"
+=item mysql 5.0.26 and 5.1
-=item B<--debug>
+=item perl 5.8.8
-Write debug messages to Squid stderr or cache.log
+=item OpenSUSE 10.2
=back
-=cut
-
-# the first argument to this script is the log file path describing the DSN
-my $log_file = shift;
-
-# others may be options
-my $debug = 0;
-GetOptions(
- 'debug' => \$debug,
- );
-
-
-# utility routine to print messages on stderr (so they appear in cache log)
-# without using warn, which would clutter the log with source line numbers
-sub log_info {
- my $msg = shift;
- print STDERR "$msg\n";
-}
-
-# we use logfile to pass database access information to this script
-# sample configuration:
-# access_log daemon:/host/database/table/username/password squid
-# to let a parmeter unspecified, e.g. the database host, use a double slash:
-# access_log daemon://database/table/username/password squid
-my ( $host, $database, $table, $user, $pass ) = $log_file =~ / \/(.*?)\/(.*?)\/(.*?)\/(.*?)\/(.*?) \z /xms;
-
-if ( !$host ) {
- $host = 'localhost';
- log_info("Database host not specified. Using $host.");
-}
-
-if ( !$database ) {
- $database = 'squid_log';
- log_info("Database name not specified. Using $database.");
-}
-
-if ( !$table ) {
- $table = 'access_log';
- log_info("Table parameter not specified. Using $table.");
-}
-
-if ( !$user ) {
- $user = 'squid';
- log_info("User parameter not specified. Using $user.");
-}
-
-if ( !$pass ) {
- log_info('No password specified. Connecting with NO password.');
-}
-
-# fields that we should have in the table
-# Order here must match the order of fields in the Log format and parse() output array.
-my @db_fields = qw(
- id
- time_since_epoch
- time_response
- ip_client
- squid_request_status
- http_status_code
- http_reply_size
- http_method
- http_url
- http_username
- squid_hier_status
- ip_server
- http_mime_type
-);
-
-# perform db connection
-my $dsn = "DBI:mysql:database=$database" . ($host ne "localhost" ? ":$host" : "");
-my $dbh;
-my $sth;
-eval {
- warn "Connecting... dsn='$dsn', username='$user', password='...'";
- $dbh = DBI->connect($dsn, $user, $pass, { AutoCommit => 1, RaiseError => 1, PrintError => 1 });
-};
-if ($EVAL_ERROR) {
- die "Cannot connect to database: $DBI::errstr";
-}
-
-
-# a simple test to assure the specified table exists
-eval {
- my $q = 'SELECT ' . join(',',@db_fields) . " FROM $table LIMIT 1";
- my $sth = $dbh->prepare($q);
- $sth->execute;
-};
-if ($EVAL_ERROR) {
- # run a query to create the table of required syntax
- my $create_query = 'CREATE TABLE ' . $table . ' (' .
- " id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY," .
- " time_since_epoch DECIMAL(15,3)," .
- " time_response INTEGER," .
- " ip_client CHAR(15)," .
- " ip_server CHAR(15)," .
- " http_status_code VARCHAR(10)," .
- " http_reply_size INTEGER," .
- " http_method VARCHAR(20)," .
- " http_url TEXT," .
- " http_username VARCHAR(20)," .
- " http_mime_type VARCHAR(50)," .
- " squid_request_status VARCHAR(50)," .
- " squid_hier_status VARCHAR(20)" .
- ");" ;
+=head1 OPTIONS
- $sth = $dbh->prepare($create_query);
- $sth->execute;
- # test again and fail hard if it is still broken.
- eval {
- my $q = 'SELECT ' . join(',',@db_fields) . " FROM $table LIMIT 1";
- my $sth = $dbh->prepare($q);
- $sth->execute;
- };
- if ($EVAL_ERROR) {
- die "Error initializing database table: $EVAL_ERROR";
- };
-}
-# test
+=over 12
-# for better performance, prepare the statement at startup
-eval {
- my $q = "INSERT INTO $table (" . join(',',@db_fields) . ") VALUES(NULL" . ',?' x (scalar(@db_fields)-1) . ')';
- #$sth = $dbh->prepare("INSERT INTO $table VALUES(NULL,?,?,?,?,?,?,?,?,?,?,?,?)");
- $sth = $dbh->prepare($q);
-};
-if ($EVAL_ERROR) {
- die "Error while preparing sql statement: $EVAL_ERROR";
-}
-
-sub parse($) {
- my ($line) = @_;
- my (@t) = $line =~ /^L(\d+\.\d+) *(\d+?) (.*?) (.*?)\/(\d+?) (\d+?) (.*?) (.*?) (.*?) (.*?)\/(.*?) (.*)$/;
-}
-
-# main loop
-while (my $line = <>) {
- chomp $line;
+=item B<DSN>
- my $cmd = substr($line, 0, 1); # extract command byte
+Database DSN encoded as a path. This is sent as the access_log file path.
- if ( $cmd eq 'L' ) {
- my @log_entry = parse($line);
- eval { # we catch db errors to avoid crashing squid in case something goes wrong...
- $sth->execute(@log_entry) or die $sth->errstr
- };
- if ( $EVAL_ERROR ) { # leave a trace of the error in the logs
- warn $EVAL_ERROR . " values=(" . join(', ', @log_entry) . ')';
- }
- }
-}
+Sample configuration:
+ access_log daemon:/host/database/table/username/password squid
-$dbh->disconnect();
+ to leave a parameter unspecified use a double slash:
+ access_log daemon://database/table/username/password squid
-__END__
+Default "DBI:mysql:database=squid"
-=pod
+=item B<--debug>
-=head1 DESCRIPTION
+Write debug info to stderr.
-This module exploits the new logfile daemon support available in squid 2.7 and 3.2 to store access log entries in a MySQL database.
+=back
=head1 CONFIGURATION
squid_request_status VARCHAR(20)
);
-=head1 VERSION INFORMATION
-
-This document refers to C<log_db_daemon> script version 0.5.
-
-The script has been developed and tested in the following environment:
-
-=over 4
-
-=item squid-2.7 Squid-3.2
-
-=item mysql 5.0.26 and 5.1
-
-=item perl 5.8.8
-
-=item OpenSUSE 10.2
-
-=back
-
=head1 DATA EXTRACTION
=head2 Sample queries.
WHERE squid_request_status LIKE '%MISS%')
/
(SELECT COUNT(*) FROM access_log)*100
- AS pecentage;
+ AS percentage;
=item Response time ranges
=back
+=head1 KNOWN ISSUES
+
=head2 Speed issues
The MyISAM storage engine is known to be faster than the InnoDB one, so although it doesn't support transactions and referential integrity, it might be more appropriate in this scenario. You might want to append "ENGINE=MYISAM" at the end of the table creation code in the above SQL script.
Indexes should be created according to the queries that are more frequently run. The DDL script only creates an implicit index for the primary key column.
-=head1 TODO
-
=head2 Table cleanup
This script currently implements only the C<L> (i.e. "append a line to the log") command, therefore the log lines are never purged from the table. This approach has an obvious scalability problem.
-One solution would be to implement e.g. the "rotate log" command in a way that would calculate some summary values, put them in a "summary table" and then delete the lines used to caluclate those values.
+One solution would be to implement e.g. the "rotate log" command in a way that would calculate some summary values, put them in a "summary table" and then delete the lines used to calculate those values.
Similar cleanup code could be implemented in an external script and run periodically independently from squid log commands.
=head1 AUTHOR
-Marcello Romani, marcello.romani@libero.it
-Amos Jeffries, amosjeffries@squid-cache.org
+This program was written by
+I<Marcello Romani <marcello.romani@libero.it>> ,
+I<Amos Jeffries <amosjeffries@squid-cache.org>>
-=head1 COPYRIGHT AND LICENSE
+=head1 COPYRIGHT
- * Copyright (C) 1996-2017 The Squid Software Foundation and contributors
+ * Copyright (C) 1996-2023 The Squid Software Foundation and contributors
*
* Squid software is distributed under GPLv2+ license and includes
* contributions from numerous individuals and organizations.
it under the same terms as Perl itself, either Perl version 5.8.8 or,
at your option, any later version of Perl 5 you may have available.
+=head1 QUESTIONS
+
+Questions on the usage of this program can be sent to the I<Squid Users mailing list <squid-users@lists.squid-cache.org>>
+
+=head1 REPORTING BUGS
+
+Bug reports need to be made in English.
+See http://wiki.squid-cache.org/SquidFaq/BugReporting for details of what you need to include with your bug report.
+
+Report bugs or bug fixes using http://bugs.squid-cache.org/
+
+Report serious security bugs to I<Squid Bugs <squid-bugs@lists.squid-cache.org>>
+
+Report ideas for new improvements to the I<Squid Developers mailing list <squid-dev@lists.squid-cache.org>>
+
+=head1 SEE ALSO
+
+squid (8), GPL (7),
+
+The Squid FAQ wiki http://wiki.squid-cache.org/SquidFaq
+
+The Squid Configuration Manual http://www.squid-cache.org/Doc/config/
+
=cut
+
+# the first argument to this script is the log file path describing the DSN
+my $log_file = shift;
+
+# others may be options
+my $debug = 0;
+GetOptions(
+ 'debug' => \$debug,
+ );
+
+
+# utility routine to print messages on stderr (so they appear in cache log)
+# without using warn, which would clutter the log with source line numbers
+sub log_info {
+ my $msg = shift;
+ print STDERR "$msg\n";
+}
+
+# we use logfile to pass database access information to this script
+# sample configuration:
+# access_log daemon:/host/database/table/username/password squid
+# to let a parameter unspecified, e.g. the database host, use a double slash:
+# access_log daemon://database/table/username/password squid
+my ( $host, $database, $table, $user, $pass ) = $log_file =~ / \/(.*?)\/(.*?)\/(.*?)\/(.*?)\/(.*?) \z /xms;
+
+if ( !$host ) {
+ $host = 'localhost';
+ log_info("Database host not specified. Using $host.");
+}
+
+if ( !$database ) {
+ $database = 'squid_log';
+ log_info("Database name not specified. Using $database.");
+}
+
+if ( !$table ) {
+ $table = 'access_log';
+ log_info("Table parameter not specified. Using $table.");
+}
+
+if ( !$user ) {
+ $user = 'squid';
+ log_info("User parameter not specified. Using $user.");
+}
+
+if ( !$pass ) {
+ log_info('No password specified. Connecting with NO password.');
+}
+
+# fields that we should have in the table
+# Order here must match the order of fields in the Log format and parse() output array.
+my @db_fields = qw(
+ id
+ time_since_epoch
+ time_response
+ ip_client
+ squid_request_status
+ http_status_code
+ http_reply_size
+ http_method
+ http_url
+ http_username
+ squid_hier_status
+ ip_server
+ http_mime_type
+ );
+
+# perform db connection
+my $dsn = "DBI:mysql:database=$database" . ($host ne "localhost" ? ":$host" : "");
+my $dbh;
+my $sth;
+eval {
+ warn "Connecting... dsn='$dsn', username='$user', password='...'";
+ $dbh = DBI->connect($dsn, $user, $pass, { AutoCommit => 1, RaiseError => 1, PrintError => 1 });
+ };
+if ($EVAL_ERROR) {
+ die "Cannot connect to database: $DBI::errstr";
+}
+
+
+# a simple test to assure the specified table exists
+eval {
+ my $q = 'SELECT ' . join(',',@db_fields) . " FROM $table LIMIT 1";
+ my $sth = $dbh->prepare($q);
+ $sth->execute;
+ };
+if ($EVAL_ERROR) {
+ # run a query to create the table of required syntax
+ my $create_query = 'CREATE TABLE ' . $table . ' (' .
+ " id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY," .
+ " time_since_epoch DECIMAL(15,3)," .
+ " time_response INTEGER," .
+ " ip_client CHAR(15)," .
+ " ip_server CHAR(15)," .
+ " http_status_code VARCHAR(10)," .
+ " http_reply_size INTEGER," .
+ " http_method VARCHAR(20)," .
+ " http_url TEXT," .
+ " http_username VARCHAR(20)," .
+ " http_mime_type VARCHAR(50)," .
+ " squid_request_status VARCHAR(50)," .
+ " squid_hier_status VARCHAR(20)" .
+ ");" ;
+
+ $sth = $dbh->prepare($create_query);
+ $sth->execute;
+ # test again and fail hard if it is still broken.
+ eval {
+ my $q = 'SELECT ' . join(',',@db_fields) . " FROM $table LIMIT 1";
+ my $sth = $dbh->prepare($q);
+ $sth->execute;
+ };
+ if ($EVAL_ERROR) {
+ die "Error initializing database table: $EVAL_ERROR";
+ };
+}
+# test
+
+# for better performance, prepare the statement at startup
+eval {
+ my $q = "INSERT INTO $table (" . join(',',@db_fields) . ") VALUES(NULL" . ',?' x (scalar(@db_fields)-1) . ')';
+#$sth = $dbh->prepare("INSERT INTO $table VALUES(NULL,?,?,?,?,?,?,?,?,?,?,?,?)");
+ $sth = $dbh->prepare($q);
+ };
+if ($EVAL_ERROR) {
+ die "Error while preparing sql statement: $EVAL_ERROR";
+}
+
+sub parse($) {
+ my ($line) = @_;
+ my (@t) = $line =~ /^L(\d+\.\d+) *(\d+?) (.*?) (.*?)\/(\d+?) (\d+?) (.*?) (.*?) (.*?) (.*?)\/(.*?) (.*)$/;
+}
+
+# main loop
+while (my $line = <>) {
+ chomp $line;
+
+ my $cmd = substr($line, 0, 1); # extract command byte
+
+ if ( $cmd eq 'L' ) {
+ my @log_entry = parse($line);
+ eval { # we catch db errors to avoid crashing squid in case something goes wrong...
+ $sth->execute(@log_entry) or die $sth->errstr
+ };
+ if ( $EVAL_ERROR ) { # leave a trace of the error in the logs
+ warn $EVAL_ERROR . " values=(" . join(', ', @log_entry) . ')';
+ }
+ }
+}
+
+$dbh->disconnect();
+
+__END__
projects / thirdparty / squid.git / blobdiff