#!/usr/bin/perl
#
# wl-sql - web server log to SQL insert calls.
#
# Take all entries in the web server log given on STDIN.  Parse them and
# prepare them for insert into an SQL database such as the below:
#
# 	CREATE TABLE httplogs (
# 		host VARCHAR(15),
# 		ident VARCHAR(10),
# 		authuser VARCHAR(10),
# 		accesstime timestamp,
# 		method VARCHAR(4),
# 		url VARCHAR(256),
# 		protocol VARCHAR(10),
# 		status INTEGER(3),
# 		bytes INTEGER(6),
# 		referrer VARCHAR(256),
# 		browser VARCHAR(256)
# 	);
#
# This and other hacks can be found at: http://oddgeek.info/
#
# Copyright (c) 2005 Jason A. Dour
#
# This software is provided 'as-is', without any express or implied warranty.
# In no event will the authors be held liable for any damages arising from the
# use of this software.
#
# Permission is granted to anyone to use this software for any purpose,
# including commercial applications, and to alter it and redistribute it
# freely, subject to the following restrictions:
#
#     1. The origin of this software must not be misrepresented; you must not
#     claim that you wrote the original software. If you use this software in a
#     product, an acknowledgment in the product documentation would be
#     appreciated but is not required.
#
#     2. Altered source versions must be plainly marked as such, and must not
#     be misrepresented as being the original software.
#
#     3. This notice may not be removed or altered from any source
#     distribution.
#

#
# Version Information
#
# 1.0	2005.05.26
#
# 	Put in comments.  Cleaned up formatting.
#
# primordial ooze
#
# 	Used every once in a while to dump web log data into an sqlite
# 	database.  Was tired of doing this on command line.
#

# For every web log entry line on STDIN...
while (<STDIN>) {
    # Parse the entry...
    ($host, $rfc931, $authuser, $timestamp, $request, $status, $bytes, $blahre, $client) =
    	/^(\S+) (\S+) (\S+) \[(.+)\] \"(.+)\" (\S+) (\S+) \"(.+)\" \"(.+)\"/;

    # Split the request into component parts...
    ($method, $url, $proto) = split(/ /,$request);

    # Output the sql insert line...
    print "insert into httplogs values (\"$host\",\"$rfc931\",\"$authuser\",\"$timestamp\",\"$method\",\"$url\",\"$proto\",\"$status\",\"$bytes\",\"$blahre\",\"$client\");\n";

}

# We're done.  Shibby!
exit(0);