Our frontend webservers are now logging all our weblogs into mySQL using mod_log_sql, freeing up thousands of "AccessLog" directives in our apache config (we're running between 600-900 virtual hosts on our servers now)
That being said, I'm trying to find a reasonable weblog analyzer that works with mod_log_sql. I've used webalizer & awstats for years and i really like them, however neither tool supports sql-based logging.
It doesn't have to be live-time, but it does atleast have to be able to grab data from a database table.
Anyone have any suggestions?
There is a php script called Skeith that does what you want.
Go here to download http://skeith.sourceforge.net/
Here is a snip from the site:
Skeith is a simple log analyzer and reporter. Specifically, Skeith works for the mod_log_sql module for Apache (it should work for mod_log_mysql too, but thus far testing has only been done with mod_log_sql).
Skeith's main feature that sets it apart from other log analyzers it that it can generate the log file for a given day or month on-the-fly. This way the sysadmin can look at the exact requests that may be questionable or harmful.
I would not recommend storing logs in any kind of SQL database. SQL storage engines are simply not fit for that, as the amount of data increases (as it surely will with near 1000 virtual hosts), the write speed will suffer from severe slowness. Deletion from the database is also a painful operation, as the table will get fragmented, further increasing read/write latency and decrease speed.
It you insist storing logs into SQL database, You will have to do your best filtering out as much unimportant data as much as you can.