Malcolm: SQL for the command line: "show"

Malcolm: SQL for the command line: "show"

It is funny how people get hooked on using SQL for querying logfiles. Last year or so I wrote asql which I use for producing adhoc statistics form Apache logfiles.

Simple usage is:

$ asql 
asql v1.2 - type 'help' for help.
asql> load /home/www/www.steve.org.uk/logs/access.log
Loading: /home/www/www.steve.org.uk/logs/access.log
asql> SELECT source,SUM(size) AS Number FROM logs GROUP BY source ORDER BY Number DESC, source LIMIT 0,10;
67.195.37.112 4681922
74.6.17.185 2353628
87.120.8.52 2066975
77.36.6.72 1859180
...

Finding the top ten referers becomes:

asql> SELECT referer,COUNT(referer) AS number from logs WHERE referer NOT LIKE '%steve.org.uk%' GROUP BY referer ORDER BY number DESC,referer LIMIT 0,10;
- 1888
http://www.gnu.org/software/gnump3d/download.html 12
http://community.livejournal.com/lotr_tattoos/?skip=20 5
http://lua-users.org/wiki/LibrariesAndBindings 4

Although SQL is often not the most natural way to query things I found it very useful and natural in this context.