Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

Re: Web Logs Using DBI

by BMaximus (Chaplain)
on Sep 10, 2001 at 06:25 UTC ( #111377=note: print w/ replies, xml ) Need Help??


in reply to Web Logs Using DBI

This somewhat looks familiar. Kind of like what's on page 364 of "Writing Apache Modules with Perl and C" - A DBI Database Logger. Must be the table.

create table access_log ( `when` datetime not null, host varchar(255) not null, method varchar(4) not null, url varchar(255) not null, auth varchar(50), referer varchar(255), status smallint(3) default 0, bytes int(8) );
Yields:
+---------+--------------+------+-----+---------------------+-------+
| Field   | Type         | Null | Key | Default             | Extra |
+---------+--------------+------+-----+---------------------+-------+
| when    | datetime     |      |     | 0000-00-00 00:00:00 |       |
| host    | varchar(255) |      |     |                     |       |
| method  | varchar(4)   |      |     |                     |       |
| url     | varchar(255) |      |     |                     |       |
| auth    | varchar(50)  | YES  |     | NULL                |       |
| referer | varchar(255) | YES  |     | NULL                |       |
| status  | smallint(3)  | YES  |     | 0                   |       |
| bytes   | int(8)       | YES  |     | NULL                |       |
+---------+--------------+------+-----+---------------------+-------+

Note: since when is a reserved word in MySQL you'll have to escape it with backticks. This isn't explained in the Eagle book.

This is a good tutorial. However, please give credit where its due if you used a book or another article as a reference to write it.

BMaximus


Comment on Re: Web Logs Using DBI
Download Code
Re: Re: Web Logs Using DBI
by btrott (Parson) on Sep 10, 2001 at 12:51 UTC
    Two things:
    1. I don't have the Eagle book, so I didn't steal it from there. Furthermore, if you notice, my tutorial has a SEE ALSO section. That references two other articles one can look at for more information on logging to a database using DBI. When writing my tutorial I had already read through the articles mentioned in the SEE ALSO section. That's why I mentioned them in that section.

    2. Even had I not read through those articles, I venture to guess that I would have come up with pretty much the same table structure. And the reason why? Because those are the fields stored in the httpd access log, so presumably one would want the same information stored in the database.
    Anyway, thanks for the "good tutorial" bit, I'm glad you liked it.
      This is a neat article!!! You could really make things easier by writing a script to print all the info to the screen!!! Much easier that sorting through a .log file. You could even sort them by date and what not, so you only get that days, or even that time's specific error. Awesome!
        Hmmm, I've been logging using a similar approach (REAL similar, in fact. I think by default, you'd end up with the same table structure regardless of who put it together). But now the REAL question (and one I can't find ANY answer to: What magic SQL does one use to get anything worthwhile OUT of this data? Is there an SQLmonks.org that could help? Or is anybody interested in setting up a repository for useful open-sourced SQL code to handle log files? (i.e., glean the number of visitors? Number of visits? number of nonblack nonravens?) I've got a few queries I've hacked together, but nothing compared to the visions of loveliness I had in mind when I started logging this info a few months ago. -end fret-

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://111377]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (9)
As of 2014-08-20 14:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (116 votes), past polls