Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Querying Apache logfiles via SQL

by skx (Parson)
on Dec 20, 2007 at 23:53 UTC ( #658331=CUFP: print w/ replies, xml ) Need Help??

One of the things that I've learned over the past few years, hand in hand with Perl, has been the use of SQL. I find it very natural to approach some problems via the use of SQL, and find it relatively straightforward to created reasonably complex SQL queries.

With that in mind I thought it might be useful to create SQL queries against Apache logfiles - without having to lose the plain logfiles I currently have. (ie. run SQL queries against Apache "Common" logfiles, not against something such as mod_log_sql which I don't use).

So I created the simple asql tool. This is a simple shell program which allows you to load Apache logfiles into a "temporary" SQLite database and then dynamically query against it.

The code is now contained in the Debian unstable distribution, and there is a mercurial repository. The code itself is mostly trivial, and so I won't post it here. However this is a representative sample of usage:

asql> load /var/log/apache2/acc* Creating tables Loading: /var/log/apache2/access.log Loading: /var/log/apache2/access.log.1 asql> SELECT referer,COUNT(referer) AS number from logs GROUP BY refe +rer ORDER BY number DESC,referer - 4807 http://localhost/stats/ 2 http://foo.ocm/stats/ 2 asql>
Steve
--

Comment on Querying Apache logfiles via SQL
Download Code
Re: Querying Apache logfiles via SQL
by moritz (Cardinal) on Dec 21, 2007 at 07:26 UTC
    ++, this is really cool, and I noticed with pleasure that it can even load gzip'ped files. And there's even a Debian repository - a user's heaven.

    Thanks for the great tool.

Re: Querying Apache logfiles via SQL
by Jenda (Abbot) on Jan 07, 2008 at 14:38 UTC

    Good work! It might be nice to provide a shortcut to add an index. Of course it's always possible to call

    CREATE INDEX idx_referer on logs (referer)
    so maybe it's not that important, but it still may be good to make it more prominent.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: CUFP [id://658331]
Approved by moritz
Front-paged by moritz
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (7)
As of 2014-09-23 01:43 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (210 votes), past polls