Re: preludedb-admin delete alert

Anybody have the source for preludedb-admin to see what it is exactly doing?  My guess is that it is doing a mysql delete command which is why it's taking so long. With a database that grows 4 gig a day the delete command cannot keep up.

I truncated the tables before an lost everything and had to go from scratch to get back up.  If you truncate the table drops and gets rebuilt from the .frm file right?

Out of these tables which one(s) hold the sensor information that I would not want to truncate (lose), Prelude_Node, Prelude_Analzer maybe?  What is Prelude_User and Prelude_UserId, doesn't the Prewikka database hold the users for login information?  I want to truncate tables but I want to be able to not lose my sensors or ability to login when I clean up the database.

+-------------------------------+
| Tables_in_prelude             |
+-------------------------------+
| Prelude_Action                |
| Prelude_AdditionalData        |
| Prelude_Address               |
| Prelude_Alert                 |
| Prelude_Alertident            |
| Prelude_Analyzer              |
| Prelude_AnalyzerTime          |
| Prelude_Assessment            |
| Prelude_Checksum              |
| Prelude_Classification        |
| Prelude_Confidence            |
| Prelude_CorrelationAlert      |
| Prelude_CreateTime            |
| Prelude_DetectTime            |
| Prelude_File                  |
| Prelude_FileAccess            |
| Prelude_FileAccess_Permission |
| Prelude_Heartbeat             |
| Prelude_Impact                |
| Prelude_Inode                 |
| Prelude_Linkage               |
| Prelude_Node                  |
| Prelude_OverflowAlert         |
| Prelude_Process               |
| Prelude_ProcessArg            |
| Prelude_ProcessEnv            |
| Prelude_Reference             |
| Prelude_Service               |
| Prelude_SnmpService           |
| Prelude_Source                |
| Prelude_Target                |
| Prelude_ToolAlert             |
| Prelude_User                  |
| Prelude_UserId                |
| Prelude_WebService            |
| Prelude_WebServiceArg         |
| _format                       |
+-------------------------------+

-----Original Message-----
From: LC Bruzenak [mailto:[email protected]] 
Sent: Friday, January 28, 2011 4:13 PM
To: Schubert, Aaron
Cc: [email protected]
Subject: Re: [prelude-user] preludedb-admin delete alert

On Wed, 2011-01-19 at 09:28 -0600, Schubert, Aaron wrote:
> I issue the command, preludedb-admin delete alert "type=mysql name=xxxxx user=xxxxx pass=xxxxx" --criteria "alert.create_time < 2011-01-19", and it just sits there for days deleting events.  In the meantime the database is still growing.  By completion of the prelude implementation I will be taking in over 100 million events per day between snort and reading firewall logs.

Wow. And I thought I had issues.
:)
On my HP DL380 4-processor machine with internal RAIDs, I get around
1500 event deletions/second (if I restrict the event count to 100000).
I only do this after moving them out of the way for storage.

Over a day that is 129M events if it worked nonstop; which of course
assumes the input is constant and the rate consistent with the
deletions. That many events and I guess it probably is.

> 
> Is there a way to just truncate the tables?  If so, what tables do I truncate and which do I leave alone without messing up the system.  Can I truncate events up to a certain time?
> 
> Basically is there a quicker way to trim the DB?

Although I do not know the answer to this question, in theory the
prelude internal commands should be calling the same mysql routines that
you could directly. 

Or maybe you could store a trigger which would delete the oldest events
automatically in the DB? Depends on your requirements I guess.

Are you able to limit the number of events in the first place to a more
manageable number?

LCB

-- 
LC (Lenny) Bruzenak
[email protected]

_______________________________________________
Prelude-user site list
[email protected]
http://lists.prelude-technologies.com/mailman/listinfo/prelude-user