.\" Automatically generated by Pod::Man 4.14 (Pod::Simple 3.40) .\" .\" 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 .\" ======================================================================== .\" .IX Title "DBILogger 3pm" .TH DBILogger 3pm "2021-01-07" "perl v5.32.0" "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" Apache::DBILogger \- Tracks what's being transferred in a DBI database .SH "SYNOPSIS" .IX Header "SYNOPSIS" .Vb 2 \& # Place this in your Apache\*(Aqs httpd.conf file \& PerlLogHandler Apache::DBILogger \& \& PerlSetVar DBILogger_data_source DBI:mysql:httpdlog \& PerlSetVar DBILogger_username httpduser \& PerlSetVar DBILogger_password secret \& PerlSetvar DBILogger_table requests .Ve .PP Create a database with a table named \fBrequests\fR like this: .PP \&\s-1CREATE TABLE\s0 requests ( server varchar(127) \s-1DEFAULT\s0 '' \s-1NOT NULL,\s0 bytes \fBmediumint\fR\|(9) \s-1DEFAULT\s0 '0' \s-1NOT NULL,\s0 user varchar(15) \s-1DEFAULT\s0 '' \s-1NOT NULL,\s0 filename varchar(200) \s-1DEFAULT\s0 '' \s-1NOT NULL,\s0 remotehost varchar(150) \s-1DEFAULT\s0 '' \s-1NOT NULL,\s0 remoteip varchar(15) \s-1DEFAULT\s0 '' \s-1NOT NULL,\s0 status \fBsmallint\fR\|(6) \s-1DEFAULT\s0 '0' \s-1NOT NULL,\s0 timeserved datetime \s-1DEFAULT\s0 '0000\-00\-00 00:00:00' \s-1NOT NULL,\s0 contenttype varchar(50) \s-1DEFAULT\s0 '' \s-1NOT NULL,\s0 urlpath varchar(200) \s-1DEFAULT\s0 '' \s-1NOT NULL,\s0 referer varchar(250) \s-1DEFAULT\s0 '' \s-1NOT NULL,\s0 useragent varchar(250) \s-1DEFAULT\s0 '' \s-1NOT NULL,\s0 usertrack varchar(100) \s-1DEFAULT\s0 '' \s-1NOT NULL,\s0 \s-1KEY\s0 server_idx (server), \s-1KEY\s0 timeserved_idx (timeserved) ); .PP Please note that for some databases (notably, PostgreSQL) you will need to double-quote the user column name (that is, to specify it as \f(CW\*(C`"user" varchar(15)\*(C'\fR) in order for the database not to mistake it with a keyword. .PP Its recommended that you include .PP use Apache::DBI; use \s-1DBI\s0; use Apache::DBILogger; .PP in your startup.pl script. Please read the Apache::DBI documentation for further information. .SH "DESCRIPTION" .IX Header "DESCRIPTION" This module tracks what's being transferred by the Apache web server in a \&\s-1SQL\s0 database (everything with a \s-1DBI/DBD\s0 driver). This allows one to get statistics (of almost everything) without having to parse the log files (like the Apache::Traffic module, just in a \*(L"real\*(R" database, and with a lot more logged information). .PP Apache::DBILogger will track the cookie from 'mod_usertrack' if it's there. .PP After installation, follow the instructions in the synopsis and restart the server. .PP The statistics are then available in the database. See the section \s-1VIEWING STATISTICS\s0 for more details. .SH "PREREQUISITES" .IX Header "PREREQUISITES" You need to have compiled mod_perl with the LogHandler hook in order to use this module. Additionally, the following modules are required: .PP .Vb 2 \& o DBI \& o Date::Format .Ve .SH "INSTALLATION" .IX Header "INSTALLATION" To install this module, move into the directory where this file is located and type the following: .PP .Vb 4 \& perl Makefile.PL \& make \& make test \& make install .Ve .PP This will install the module into the Perl library directory. .PP Once installed, you will need to modify your web server's configuration file so it knows to use Apache::DBILogger during the logging phase. .SH "VIEWING STATISTICS" .IX Header "VIEWING STATISTICS" Please see the bin/ directory in the distribution for a statistics script. .PP Some funny examples on what you can do might include: .IP "hit count and total bytes transferred from the virtual server www.company.com" 4 .IX Item "hit count and total bytes transferred from the virtual server www.company.com" .Vb 2 \& select count(id),sum(bytes) from requests \& where server="www.company.com" .Ve .IP "hit count and total bytes from all servers, ordered by number of hits" 4 .IX Item "hit count and total bytes from all servers, ordered by number of hits" .Vb 2 \& select server,count(id) as hits,sum(bytes) from requests \& group by server order by hits desc .Ve .IP "count of hits from macintosh users" 4 .IX Item "count of hits from macintosh users" .Vb 1 \& select count(id) from requests where useragent like "%Mac%" .Ve .ie n .IP "hits and total bytes in the last 30 days select count(id),sum(bytes) from requests where server=""www.company.com"" and \s-1TO_DAYS\s0(\s-1\fBNOW\s0()\fR) \- \s-1TO_DAYS\s0(timeserved) <= 30" 4 .el .IP "hits and total bytes in the last 30 days select count(id),sum(bytes) from requests where server=``www.company.com'' and \s-1TO_DAYS\s0(\s-1\fBNOW\s0()\fR) \- \s-1TO_DAYS\s0(timeserved) <= 30" 4 .IX Item "hits and total bytes in the last 30 days select count(id),sum(bytes) from requests where server=www.company.com and TO_DAYS(NOW()) - TO_DAYS(timeserved) <= 30" This is pretty unoptimal. It would be faster to calculate the dates in perl and write them in the sql query using f.x. Date::Format. .IP "hits and total bytes from www.company.com on mondays." 4 .IX Item "hits and total bytes from www.company.com on mondays." .Vb 2 \& select count(id),sum(bytes) from requests where \& server="www.company.com" and dayofweek(timeserved) = 2 .Ve .PP It's often pretty interesting to view the referer info too. .PP See your sql server documentation of more examples. I'm a happy mySQL user, so I would continue on .PP http://www.tcx.se/Manual_chapter/manual_toc.html .SH "LOCKING ISSUES" .IX Header "LOCKING ISSUES" MySQL 'read locks' the table when you do a select. On a big table (like a large httpdlog) this might take a while, where your httpds can't insert new logentries, which will make them 'hang' until the select is done. .PP One way to work around this is to create another table (f.x. requests_insert) and get the httpd's to insert to this table. .PP Then run a script from crontab once in a while which does something like this: .PP .Vb 4 \& LOCK TABLES requests WRITE, requests_insert WRITE \& insert into requests select * from requests_insert \& delete from requests_insert \& UNLOCK TABLES .Ve .PP You can use the moverows.pl script from the bin/ directory. .PP Please note that this won't work if you have any unique id field! You'll get duplicates and your new rows won't be inserted, just deleted. Be careful. .SH "TRAPS" .IX Header "TRAPS" I've experienced problems with 'Packets too large' when using Apache::DBI, mysql and DBD::mysql 2.00 (from the Msql-mysql 1.18x packages). The DBD::mysql module from Msql-mysql 1.19_17 seems to work fine with Apache::DBI. .PP You might get problems with Apache 1.2.x. (Not supporting post_connection?) .SH "MOD_PERL 2 SUPPORT" .IX Header "MOD_PERL 2 SUPPORT" The official version of this module, as Ask Bjoern Hansen last modified it, lacks support for the \s-1API\s0 changes introduced with Apache 2.x and the corresponding mod_perl 2.x \- Of course, this is quite understandable as this module was last updated in 1998 ;\-) But anyway, the module does its job still quite fine, and users still require its functionality. .PP For any help requests regarding this module on Apache 2 systems, contact Gunnar Wolf directly. If your system is based on Debian GNU/Linux, you can use the regular Debian bugtracking facilities, as the multi-API patch was introduced specifically for Debian. .SH "SUPPORT" .IX Header "SUPPORT" This module is supported via the mod_perl mailinglist (modperl@apache.org, subscribe by sending a mail to modperl\-request@apache.org). .PP I would like to know which databases this module have been tested on, so please mail me if you try it. .PP The latest version can be found on your local \s-1CPAN\s0 mirror or at \&\f(CW\*(C`ftp://ftp.netcetera.dk/pub/perl/\*(C'\fR .SH "AUTHOR" .IX Header "AUTHOR" Copyright (C) 1998, Ask Bjoern Hansen . All rights reserved. This module is free software; you may redistribute it and/or modify it under the same terms as Perl itself. .SH "SEE ALSO" .IX Header "SEE ALSO" \&\fBperl\fR\|(1), \fBmod_perl\fR\|(3)