• Support
  • Forums
  • Blogs
A New Community Experience is Coming! For more information, please see our announcement.

Optimizing the SIEM Events Page: 'x Total Events in Database' query

ricky.boonericky.boone

New Life Form
+2

I've been looking into poor response time issues for certain areas of my USM/OSSIM systems. One thing I noticed when watching the running MySQL queries when hitting the SIEM events page was a query that effectively always takes a significant time to return a result. Now, the environments I'm in have separate issues (the event backups weren't running, causing the retention and cleanup processes to not run...), so the respective databases are on the large side. That aside, the query itself still seems to be less than optimal.

The query I'm seeing is:

image

I can find the corresponding function that's calling it in /usr/share/ossim/www/forensics/includes/base_state_query.inc.php (the GetCalcRows() function). I haven't had a chance to dig into where this function is used beyond this page, but it appears to be used for the 'x Total Events in Database' message. On larger databases, this query can cause the page to take several seconds to a couple minutes to render (on top of any other queries, but even with the "default" last day).

I'm not an expert by any means, and I'm not super familiar with the development of OSSIM/USM and why certain decisions were made, but this seems to be an area that could be optimized a bit. Based on some of the comments in the code, this appears to have been an issue in the past ("Optimization Update: faster than GetNumResultRows()").

Perhaps this is an area where approximations is better than an exact row count? Events are being added to my environment all the time, so an exact count on this page isn't really useful (although for some reports I would expect it). Perhaps instead of scanning through the entire table or index of the table to generate a row count, we just ask MySQL what the row count is? It won't be exact for some engines (InnoDB, TokuDB, etc.), but is that so bad?

Instead of using the query for this like the one above, where it's having to calculate a sum of everything in the 'cnt' column in ac_acid_event, we have the following query:

image

Thoughts?  

Sorry for using images instead of actual SQL... AlienVault's forums (or more specifically their CloudFlare WAF config) kept blocking me when it was in plain text.  >:(

Share post:

Comments

  • Dangit... I flipped the images around accidentally, and now I can't edit it.  The query on the top is supposed to be the one I was recommending, and the one on the bottom is the one the SIEM page is currently using.
Sign In or Register to comment.