tutorial - Query Apache logfiles via SQL

The Apache SQL Analyser (ASQL) is designed to read Apache log files and dynamically convert them to SQLite format so you can analyse them in a more meaningful way. Using the cut, uniq and wc commands, you can parse a log file by hand to figure out how many unique visitors came to your site, but using Apache SQL Analyser is much faster and means that the whole log gets parsed only once. Finding unique addresses is as simple as a SELECT DISTINCT command.

In terms of requirements you will need only the Perl modules for working with SQLite database, and the Term::Readline module. Upon a Debian system you may install both via:

apt-get install libdbd-sqlite3-perl libterm-readline-gnu-perl

Usage
Once installed, either via the package or via the source download, please start the shell by typing "asql". Once the shell starts you have several commands available to you, enter help for a complete list. The three most commonly used commands would be: load, select & show

The following sample session provides a demonstration of typical usage of the shell, it demonstrates the use of the alias command which may be used to create persistent aliases:

asql v0.6 - type 'help' for help.
asql> load /var/logs/apache/access.log
Loading: /var/logs/apache/access.log
sasql> select COUNT(id) FROM logs
46
asql> alias hits SELECT COUNT(id) FROM logs
ALIAS hits SELECT COUNT(id) FROM logs
asql> alias ips SELECT DISTINCT(source) FROM logs;
ALIAS ips SELECT DISTINCT(source) FROM logs;
asql> hits
46
asql> alias
ALIAS hits SELECT COUNT(id) FROM logs
ALIAS ips SELECT DISTINCT(source) FROM logs;

(User-input is in bold for emphasis)

Free, facebook, tips, Links, blogging, Downloads, Google, facebookTips, money, news, apps, Social, Media, Website, Tricks, games, Android, software, PIctures, Internet, Security, Web, codes, Review, bloggers, SAMSUNG, Worldwide, Contest, Exitic, Phones, facebookTricks, hacking, London, Olympics, SEO, Youtube, iOS, Adsense, gadgets, iPHONE, widgets, Doodle, twitter, video, Deals, technology, Aircel, Airtel, iPAD, Angry, Birds, BSNL, TechLife, GMAIL, Idea, Microsoft, SmartPhones, Stress, Buster, Windows, Yahoo, Infolinks, Nokia, Scam, Uninor, browsers, Amazon, Euro, CUP, Chat, IDM, JOBS, Modem, Music, Reliance, Results, SSC, Tata, Docomo, bing, freebie, mobile, placements, AIEEE, AlertPay, Chrome, College, Competetive, Exam, Dehradun, Extension, FireFox, GPRS, HTC, IMPACT, Info, MTS, Mark, Zukerberg, Paypal, Promotional, Post, Torrent, UTU, Unlocking, VodaFone, Wall, Paper, apple, books, engineering, iCAR, iTunes, pinterest, rovio, AVG, Admit, Card, Adobe, Affiliate, Marketing, Akhilesh, Amul, Girl, BlackBerry, ChromeBook, Clixsense, Coupon, Digitallife, Discovery, Emoticons, Festival, GATE, GIMP, Income, Tax, International, JSS, JailBreaking, Kindle, Linux, Local, MAX, PAYNE, Mac, Mango, Memory, Speed, Nexus, Online, Shopping, Raakhi, Report, Rising, Stars, Sample, Science, Sony, Syllabus, TabletBooK, Teamviewer, Templates, Dark, Knight, Rises, USA, UPMT, Virgin, Xperia, ZTE, challan, counselling, course, btech, funny, iMOVE, registration

source:http://linuxpoison.blogspot.com/2009/07/13578175711787.html