Hi Chris, You mention logstash and Splunk, but have you looked at ELSA? http://code.google.com/p/enterprise-log-search-and-archive/
Regards, Doug On Sun, Apr 1, 2012 at 8:45 PM, Decker Christopher <ch...@chris-decker.com> wrote: > 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. -- Doug Burks | http://securityonion.blogspot.com Don't miss SANS SEC503 Intrusion Detection In-Depth in Augusta GA 6/11 - 6/16 | 10% discount for ISSA Members! http://augusta.issa.org/drupal/SANS-Augusta-2012