All, I'm running MySQL + Apache/PHP on a very beefy box but using the out-of-box OSSEC DB schemas I'm experiencing significant latency pulling the alerts from the DB. I use the excellent OSSEC viewer (using Ext JS) [http://code.google.com/p/ossecdb-extjs/] to look at the last 30 days or so of alerts, and typically filter based on alert level. I'm not really performing complex queries, I'm merely trying to keep an eye on my servers and react as necessary. That said, I do like to keep all of the older alerts "on-line" to perform basic research when the need arises.
I'm not a MySQL expert nor do I have any desire to be one, but timely queries of my alerts is important to me--please help! The areas I'm currently researching and would love to hear from other OSSEC users (after all, I'm not looking to re-invent the wheel here): Partitioning scheme. I'm looking for something that automatically creates partitions for each month of the year (i.e. 12 per year; when we move into a new month the new partition is created automatically). For now, the best tutorial I could find was here: http://www.kickingtyres.com/words/mysql/mysql-partition-management/ Modifications to the existing indexes. The current indexes looked fine to me, given that most of my queries are simply based on timestamp and alert level, but I thought I'd ask. I already know that there are some general optimizations I can make to MySQL that will help alleviate some of my issues, but the above areas are also of interest to me. Thanks in advance, Chris P.s. Some may read my post and wonder why I'm not using logstash or Splunk. logstash is great for queries but generally difficult to read/use for casual log reviewing (IMHO)--I am considering standing it up for more complex searching in the future. Splunk has great search capabilities and I like the overall interface, but is not open source (and I think I'll eventually hit the 500 MB/day ceiling), requires Flash to view any graphs (seems counter-productive given all of the security issues the plugin has!) and splunkd has crashed quite frequently on me.
