I’ve thought about it before, but until now, I’ve never researched it. Wouldn’t it be nifty to log all your apache logs for various virtual hosts into a MySQL database? Enter mod_log_sql. I was skeptical at first, because it appears there hasn’t been any recent development; however, the module compiled fine and I had no issues getting it to work. This seems like a very powerful tool to me and one that I will quite likely be using in the future. Some easy analytics can now be performed via a SQL shell. Say, for instance, that I want to see all the 404s that happened in the last 24 hours and their respective URIs. How about:
SELECT remote_host,request_uri,time_stamp,virtual_host FROM logs WHERE status=404 AND time_stamp>UNIX_TIMESTAMP()-86400;
Or maybe the last 20 hits to a certain host.
SELECT remote_host,request_uri,time_stamp FROM (SELECT remote_host,request_uri,time_stamp FROM logs WHERE virtual_host='paydensutherland.com' ORDER BY time_stamp DESC LIMIT 20) AS tmpTable ORDER BY time_stamp ASC
It seems to me, that I could now roll my own analytics engine in PHP quite a bit easier than it would be if I were to have to parse log files out and do the sorting manually. A project for another day