.\" Automatically generated by Pod::Man 4.10 (Pod::Simple 3.35) .\" .\" Standard preamble: .\" ======================================================================== .de Sp \" Vertical space (when we can't use .PP) .if t .sp .5v .if n .sp .. .de Vb \" Begin verbatim text .ft CW .nf .ne \\$1 .. .de Ve \" End verbatim text .ft R .fi .. .\" Set up some character translations and predefined strings. \*(-- will .\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left .\" double quote, and \*(R" will give a right double quote. \*(C+ will .\" give a nicer C++. Capital omega is used to do unbreakable dashes and .\" therefore won't be available. \*(C` and \*(C' expand to `' in nroff, .\" nothing in troff, for use with C<>. .tr \(*W- .ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p' .ie n \{\ . ds -- \(*W- . ds PI pi . if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch . if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\" diablo 12 pitch . ds L" "" . ds R" "" . ds C` "" . ds C' "" 'br\} .el\{\ . ds -- \|\(em\| . ds PI \(*p . ds L" `` . ds R" '' . ds C` . ds C' 'br\} .\" .\" Escape single quotes in literal strings from groff's Unicode transform. .ie \n(.g .ds Aq \(aq .el .ds Aq ' .\" .\" If the F register is >0, we'll generate index entries on stderr for .\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index .\" entries marked with X<> in POD. Of course, you'll have to process the .\" output yourself in some meaningful fashion. .\" .\" Avoid warning from groff about undefined register 'F'. .de IX .. .nr rF 0 .if \n(.g .if rF .nr rF 1 .if (\n(rF:(\n(.g==0)) \{\ . if \nF \{\ . de IX . tm Index:\\$1\t\\n%\t"\\$2" .. . if !\nF==2 \{\ . nr % 0 . nr F 2 . \} . \} .\} .rr rF .\" .\" Accent mark definitions (@(#)ms.acc 1.5 88/02/08 SMI; from UCB 4.2). .\" Fear. Run. Save yourself. No user-serviceable parts. . \" fudge factors for nroff and troff .if n \{\ . ds #H 0 . ds #V .8m . ds #F .3m . ds #[ \f1 . ds #] \fP .\} .if t \{\ . ds #H ((1u-(\\\\n(.fu%2u))*.13m) . ds #V .6m . ds #F 0 . ds #[ \& . ds #] \& .\} . \" simple accents for nroff and troff .if n \{\ . ds ' \& . ds ` \& . ds ^ \& . ds , \& . ds ~ ~ . ds / .\} .if t \{\ . ds ' \\k:\h'-(\\n(.wu*8/10-\*(#H)'\'\h"|\\n:u" . ds ` \\k:\h'-(\\n(.wu*8/10-\*(#H)'\`\h'|\\n:u' . ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'^\h'|\\n:u' . ds , \\k:\h'-(\\n(.wu*8/10)',\h'|\\n:u' . ds ~ \\k:\h'-(\\n(.wu-\*(#H-.1m)'~\h'|\\n:u' . ds / \\k:\h'-(\\n(.wu*8/10-\*(#H)'\z\(sl\h'|\\n:u' .\} . \" troff and (daisy-wheel) nroff accents .ds : \\k:\h'-(\\n(.wu*8/10-\*(#H+.1m+\*(#F)'\v'-\*(#V'\z.\h'.2m+\*(#F'.\h'|\\n:u'\v'\*(#V' .ds 8 \h'\*(#H'\(*b\h'-\*(#H' .ds o \\k:\h'-(\\n(.wu+\w'\(de'u-\*(#H)/2u'\v'-.3n'\*(#[\z\(de\v'.3n'\h'|\\n:u'\*(#] .ds d- \h'\*(#H'\(pd\h'-\w'~'u'\v'-.25m'\f2\(hy\fP\v'.25m'\h'-\*(#H' .ds D- D\\k:\h'-\w'D'u'\v'-.11m'\z\(hy\v'.11m'\h'|\\n:u' .ds th \*(#[\v'.3m'\s+1I\s-1\v'-.3m'\h'-(\w'I'u*2/3)'\s-1o\s+1\*(#] .ds Th \*(#[\s+2I\s-2\h'-\w'I'u*3/5'\v'-.3m'o\v'.3m'\*(#] .ds ae a\h'-(\w'a'u*4/10)'e .ds Ae A\h'-(\w'A'u*4/10)'E . \" corrections for vroff .if v .ds ~ \\k:\h'-(\\n(.wu*9/10-\*(#H)'\s-2\u~\d\s+2\h'|\\n:u' .if v .ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'\v'-.4m'^\v'.4m'\h'|\\n:u' . \" for low resolution devices (crt and lpr) .if \n(.H>23 .if \n(.V>19 \ \{\ . ds : e . ds 8 ss . ds o a . ds d- d\h'-1'\(ga . ds D- D\h'-1'\(hy . ds th \o'bp' . ds Th \o'LP' . ds ae ae . ds Ae AE .\} .rm #[ #] #H #V #F C .\" ======================================================================== .\" .IX Title "LOG_DB_DAEMON 8" .TH LOG_DB_DAEMON 8 "2021-05-31" "perl v5.28.1" "User Contributed Perl Documentation" .\" For nroff, turn off justification. Always turn off hyphenation; it makes .\" way too many mistakes in technical documents. .if n .ad l .nh .SH "NAME" log_db_daemon \- Database logging daemon for Squid .PP Version 0.5. .SH "SYNOPSIS" .IX Header "SYNOPSIS" log_db_daemon \s-1DSN\s0 [options] .SH "DESCRIPTION" .IX Header "DESCRIPTION" This program writes Squid access.log entries to a database. Presently only accepts the \fBsquid\fR native log format. .PP The script has been developed and tested in the following environment: .IP "squid\-2.7 Squid\-3.2" 4 .IX Item "squid-2.7 Squid-3.2" .PD 0 .IP "mysql 5.0.26 and 5.1" 4 .IX Item "mysql 5.0.26 and 5.1" .IP "perl 5.8.8" 4 .IX Item "perl 5.8.8" .IP "OpenSUSE 10.2" 4 .IX Item "OpenSUSE 10.2" .PD .SH "OPTIONS" .IX Header "OPTIONS" .IP "\fB\s-1DSN\s0\fR" 12 .IX Item "DSN" Database \s-1DSN\s0 encoded as a path. This is sent as the access_log file path. .Sp Sample configuration: access_log daemon:/host/database/table/username/password squid .Sp .Vb 2 \& to leave a parameter unspecified use a double slash: \& access_log daemon://database/table/username/password squid .Ve .Sp Default \*(L"DBI:mysql:database=squid\*(R" .IP "\fB\-\-debug\fR" 12 .IX Item "--debug" Write debug info to stderr. .SH "CONFIGURATION" .IX Header "CONFIGURATION" .SS "Squid configuration" .IX Subsection "Squid configuration" \fIaccess_log directive\fR .IX Subsection "access_log directive" .PP The path to the access log file is used to provide the database connection parameters. .PP .Vb 1 \& access_log daemon:/mysql_host:port/database/table/username/password squid .Ve .PP The 'daemon' prefix is mandatory and tells squid that the \fBlogfile_daemon\fR helper is to be used instead of the normal file logging. .PP The last parameter tells squid which log format to use when writing lines to the log daemon. Presently \fBsquid\fR format is supported. .IP "mysql_host:port" 4 .IX Item "mysql_host:port" Host where the mysql server is running. If left empty, 'localhost' is assumed. .IP "database" 4 .IX Item "database" Name of the database to connect to. If left empty, 'squid_log' is assumed. .IP "table" 4 .IX Item "table" Name of the database table where log lines are stored. If left empty, 'access_log' is assumed. .IP "username" 4 .IX Item "username" Username to use when connecting to the database. If left empty, 'squid' is assumed. .IP "password" 4 .IX Item "password" Password to use when connecting to the database. If left empty, no password is used. .PP To leave all fields to their default values, you can use a single slash: .PP .Vb 1 \& access_log daemon:/ squid .Ve .PP To specify only the database password, which by default is empty, you must leave unspecified all the other parameters by using null strings: .PP .Vb 1 \& access_log daemon://///password squid .Ve .PP \fIlogfile_daemon directive\fR .IX Subsection "logfile_daemon directive" .PP This is the current way of telling squid where the logfile daemon resides. .PP .Vb 1 \& logfile_daemon /path/to/squid/libexec/logfile\-daemon_mysql.pl .Ve .PP The script must be copied to the location specified in the directive. .SS "Database configuration" .IX Subsection "Database configuration" Let's call the database 'squid_log' and the log table 'access_log'. The username and password for the db connection will be both 'squid'. .PP \fIDatabase\fR .IX Subsection "Database" .PP Create the database: .PP .Vb 1 \& CREATE DATABASE squid_log; .Ve .PP \fIUser\fR .IX Subsection "User" .PP Create the user: .PP .Vb 2 \& GRANT INSERT,SELECT,CREATE ON squid_log.* TO \*(Aqsquid\*(Aq@\*(Aqlocalhost\*(Aq IDENTIFIED BY \*(Aqsquid\*(Aq; \& FLUSH PRIVILEGES; .Ve .PP Note that only \s-1CREATE, INSERT\s0 and \s-1SELECT\s0 privileges are granted to the 'squid' user. This ensures that the logfile daemon script cannot change or modify the log entries. .PP \fITable\fR .IX Subsection "Table" .PP The Daemon will attempt to initialize this table if none exists when it starts. .PP The table created should look like: .PP .Vb 10 \& CREATE TABLE access_log ( \& 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_hier_status VARCHAR(20), \& squid_request_status VARCHAR(20) \& ); .Ve .SH "DATA EXTRACTION" .IX Header "DATA EXTRACTION" .SS "Sample queries." .IX Subsection "Sample queries." .IP "Clients accessing the cache" 4 .IX Item "Clients accessing the cache" .Vb 1 \& SELECT DISTINCT ip_client FROM access_log; .Ve .IP "Number of request per day" 4 .IX Item "Number of request per day" .Vb 6 \& SELECT \& DATE(FROM_UNIXTIME(time_since_epoch)) AS date_day, \& COUNT(*) AS num_of_requests \& FROM access_log \& GROUP BY 1 \& ORDER BY 1; .Ve .IP "Request status count" 4 .IX Item "Request status count" To obtain the raw count of each request status: .Sp .Vb 4 \& SELECT squid_request_status, COUNT(*) AS n \& FROM access_log \& GROUP BY squid_request_status \& ORDER BY 2 DESC; .Ve .Sp To calculate the percentage of each request status: .Sp .Vb 6 \& SELECT \& squid_request_status, \& (COUNT(*)/(SELECT COUNT(*) FROM access_log)*100) AS percentage \& FROM access_log \& GROUP BY squid_request_status \& ORDER BY 2 DESC; .Ve .Sp To distinguish only between HITs and MISSes: .Sp .Vb 10 \& SELECT \& \*(Aqhits\*(Aq, \& (SELECT COUNT(*) \& FROM access_log \& WHERE squid_request_status LIKE \*(Aq%HIT%\*(Aq) \& / \& (SELECT COUNT(*) FROM access_log)*100 \& AS percentage \& UNION \& SELECT \& \*(Aqmisses\*(Aq, \& (SELECT COUNT(*) \& FROM access_log \& WHERE squid_request_status LIKE \*(Aq%MISS%\*(Aq) \& / \& (SELECT COUNT(*) FROM access_log)*100 \& AS percentage; .Ve .IP "Response time ranges" 4 .IX Item "Response time ranges" .Vb 10 \& SELECT \& \*(Aq0..500\*(Aq, \& COUNT(*)/(SELECT COUNT(*) FROM access_log)*100 AS percentage \& FROM access_log \& WHERE time_response >= 0 AND time_response < 500 \& UNION \& SELECT \& \*(Aq500..1000\*(Aq, \& COUNT(*)/(SELECT COUNT(*) FROM access_log)*100 AS percentage \& FROM access_log \& WHERE time_response >= 500 AND time_response < 1000 \& UNION \& SELECT \& \*(Aq1000..2000\*(Aq, \& COUNT(*)/(SELECT COUNT(*) FROM access_log)*100 AS percentage \& FROM access_log \& WHERE time_response >= 1000 AND time_response < 2000 \& UNION \& SELECT \& \*(Aq>= 2000\*(Aq, \& COUNT(*)/(SELECT COUNT(*) FROM access_log)*100 AS percentage \& FROM access_log \& WHERE time_response >= 2000; .Ve .IP "Traffic by mime type" 4 .IX Item "Traffic by mime type" .Vb 6 \& SELECT \& http_mime_type, \& SUM(http_reply_size) as total_bytes \& FROM access_log \& GROUP BY http_mime_type \& ORDER BY 2 DESC; .Ve .IP "Traffic by client" 4 .IX Item "Traffic by client" .Vb 6 \& SELECT \& ip_client, \& SUM(http_reply_size) AS total_bytes \& FROM access_log \& GROUP BY 1 \& ORDER BY 2 DESC; .Ve .SH "KNOWN ISSUES" .IX Header "KNOWN ISSUES" .SS "Speed issues" .IX Subsection "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 \*(L"ENGINE=MYISAM\*(R" at the end of the table creation code in the above \s-1SQL\s0 script. .PP Indexes should be created according to the queries that are more frequently run. The \s-1DDL\s0 script only creates an implicit index for the primary key column. .SS "Table cleanup" .IX Subsection "Table cleanup" This script currently implements only the \f(CW\*(C`L\*(C'\fR (i.e. \*(L"append a line to the log\*(R") command, therefore the log lines are never purged from the table. This approach has an obvious scalability problem. .PP One solution would be to implement e.g. the \*(L"rotate log\*(R" command in a way that would calculate some summary values, put them in a \*(L"summary table\*(R" and then delete the lines used to calculate those values. .PP Similar cleanup code could be implemented in an external script and run periodically independently from squid log commands. .SS "Testing" .IX Subsection "Testing" This script has only been tested in low-volume scenarios (single client, less than 10 req/s). Tests in high volume environments could reveal performance bottlenecks and bugs. .SH "AUTHOR" .IX Header "AUTHOR" This program was written by \&\fIMarcello Romani , \&\fIAmos Jeffries .SH "COPYRIGHT" .IX Header "COPYRIGHT" .Vb 5 \& * Copyright (C) 1996\-2019 The Squid Software Foundation and contributors \& * \& * Squid software is distributed under GPLv2+ license and includes \& * contributions from numerous individuals and organizations. \& * Please see the COPYING and CONTRIBUTORS files for details. .Ve .PP Copyright (C) 2008 by Marcello Romani .PP This library is free software; you can redistribute it and/or modify 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. .SH "QUESTIONS" .IX Header "QUESTIONS" Questions on the usage of this program can be sent to the \fISquid Users mailing list .SH "REPORTING BUGS" .IX Header "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. .PP Report bugs or bug fixes using http://bugs.squid\-cache.org/ .PP Report serious security bugs to \fISquid Bugs .PP Report ideas for new improvements to the \fISquid Developers mailing list .SH "SEE ALSO" .IX Header "SEE ALSO" squid (8), \s-1GPL\s0 (7), .PP The Squid \s-1FAQ\s0 wiki http://wiki.squid\-cache.org/SquidFaq .PP The Squid Configuration Manual http://www.squid\-cache.org/Doc/config/