apalog (apalogretrieve)

Overview

The program apalogretrieve (binary-name: apalog) retrieves data from an Apache logfile with a syntax, that is derived from (and a subset of) the SQL language.

SQL is used for relational databases normally. But here we use a SQL-like language (SQL-subset) for Webserver-logfile-queries.

There are a lot of possibilities to retrieve data from an apache logfile.
To have an overview on the requests (with graphics support also) very quickly, you can use webalizer. It's a nice tool but as every tool it has not only advantages but also disadvantages.
Webalizer does averaging. For getting detailed infromation in certain queries, this is a bad choice.

You could switch back to grep, awk or Perl or use specialzed tools, which you implement in a common programming language, which does some specialized analyzes.
But grep has some limitations here, because it does string matching linewise, not on certain fields. awk has no domain-specific named fields. Even if you can use associative arrays in awk, which makes things easier to handle, there is no predefined name for the logentry-fields. Also you may get problems with parsing the data, because you have to find out the appropriate way to select the fields.
apalogretrieve brings you specific names for each field. And if you want to use specialized tools, they might be to narrow in focus, even if possibly good in what they are intended to do.

If you want to make concise, but flexible queries, wether webalizer nor grep nor awk nor specialized tools might be the best choice, IMHO.

What apalogretrieve makes possible here is to retrieve the fields by their name and use filters (WHERE-clause) as well as boolean operators (AND, OR, NOT). You also have a simple regular expression mechanism, like the like-operator from SQL.
IMHO this makes data retrieval in lookups for some special entries very convenient.

Application Examples

News from apalog (31th January 2008):

• 31/01/2008: New Release: version 0-9-6_4 is available. Changed Lexer and Parser for the logfile as well as for the REPL-loop.
  • Logfile-Parser corrected to read correctly even strange files.
  • Command-Parser: uses now "ORDER BY" instead of "GROUP BY" (the latter was, when following SQL instead of not common sense /natural language ;-) a misnomer).
• 09/01/2008: New Release: version 0-9-6_2 is available. LaTeX-Converter completed, and unnecessary prints in align-output removed.
• 08/01/2008: New Release: version 0-9-6 is now available! 08/01/2008 Apalog has always worked on the COMBINED logfile format,
  not on the COMMON logfile format. Sorry for the misnomer in the documentation!
  But I grabbed out only the parts of the common logfile format and ignored the both (very seldom used) fields logname and userid.
  Now I grab all parts out of the logfile, not ignoring logname and userid.
  And I furthermore work on the combined logfile format like before. The combined logfile format is a very common format (hence my misnomer).
• 07/01/2008 Apalog (non-released version) can now read gzip-files transparently.
  Aligned ASCII-output also is ready now.
• 06/01/2008 Apalog (non-released version) has now the possibility to redirect the output to a file.
  It also now has the possibility to output the results as a HTML-Table. The release will follow later; I first want to implement LaTeX-output also, as well as aligned ASCII-output.
• 24/11/2007 Release 0.9.4: now GROUP BY is implemented! :)
  Some other changes also.
• 09/08/2007 New release: 0.9.2 has now the WHERE-clause filter-condition inside the logfile lexer, which means less memory usage, if you read large logfiles.

How to Use apalog

Keywords

Implemented SQL-statements (subset of SQL)

• SELECT
• FROM
• WHERE
• GROUP
• BY
• LIKE

Other Query-Keywords

• AND
• OR
• NOT

Field-Names

• host
• lname
• user
• date
• request
• status
• size
• referrer
• client

Other Keywords

• quit
• format
• output
• default
• align
• html
• latex

Usage Helper

apalog has no line-editing functionality implemented.
If you want to have this feature, please use the ledit-tool. :) (It's also written in OCaml :))
Debian-package for ledit

Usage Example

Example 1

Example 2

Example 3:

The task: Look for all entries with domainnames ending in ".it", ignoring the entries for icons ("/icons/back.gif", "/icons/folder.gif", "/icons/blank.gif", "/favicon.ico" and so on)

Example 4: There is no "DISTINCT" clause - example on that?

Necessary to mention ("disclaimer")

• I should also mention: the date is compared string-like; it is NOT a true comparison on the dates (at least not in version 0.9.4).
  I may change this in later releases (as I didn't use this feature often, it was not really necessry for me; implementing "like" was more interesting for me, so this was my priority.)
• Size of log-entries with size == "-" are handled as size = -1. This means, if you wish to look for entries that have "-" entries,look for size 0.
  I may later change this to 0.

Download

Implementation

If you want to give feedback (feature wishes, bug report or if you like the tool and where you use it), do not hesitate to contact me.