We just rotate the data and alert tables monthly. That allows you to clean up old data and eventually drop the old tables altogether when they are no longer needed, without impacting the access to the current alert and data tables.
An example SQL for rotating the table looks like this: create table data_new like data; rename table data to data_07, data_new to data;Since the rename is atomic, no data get's lost. You can rotate them how ever often you like (e.g. weekly, bi monthly, ...). The downside of course is that queries, that require a time frame that is larger than that contained in one table, get slightly more complicated (i.e. union selects), and you have additional work dealing with these tables (naming scheme, making sure they are eventually deleted, ...).
On 8/19/2015 11:04 AM, Jamey B wrote:
Hi,Was there any update on this [old] thread? The URL below digs into MySQL cleaning, is this recommended for large deployments? I have numerous OSSEC servers running, I think this would benefit each one (performance wise).https://jsosic.wordpress.com/2012/11/21/cleaning-ossec-mysql-database/On Tuesday, February 22, 2011 at 5:15:44 AM UTC-5, Dimitris Chontzopoulos wrote:Hey Dan, It seems to me that data regarding events are stored/referenced in multiple tables. This stops me from just deleting data from a single table as it could affect the rest of the data inside the other tables and end up with an out-of-sync database. Unless I'm horribly mistaken that is. Can any of you guys comment on "how" data should be removed from the Database? Is what Dan suggests sufficient or should we come up with a 'join' and/or 'view' and delete data from there and not directly from a single table? Dimitris -----Original Message----- From: [email protected] <javascript:> [mailto:[email protected] <javascript:>] On Behalf Of dan (ddp) Sent: Tuesday, February 22, 2011 03:52 To: [email protected] <javascript:> Subject: Re: [ossec-list] How to purge/remove/delete data older than a specific date from within the database I think everything in the database it timestamped. You should be able to make a query to delete everything previous to a certain date. On Mon, Feb 21, 2011 at 7:01 AM, Dimitris Chontzopoulos <[email protected] <javascript:>> wrote: > Hello everyone, > > We're trying to remove data from within the OSSEC Database that are older than a specific date, but we can't find a tool that would > remove that data easily without harming the rest of the database. > > Is there a query of some sort we could run, so as to gather the data we're interested in and remove them afterwards? > > This might be a stupid question but I'm no MySQL Administrator or 'that' experienced at all. > > > > > Kind regards, > > > > Dimitris > > > -- ---You received this message because you are subscribed to the Google Groups "ossec-list" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <mailto:[email protected]>.For more options, visit https://groups.google.com/d/optout.
----- You received this message because you are subscribed to the Google Groups "ossec-list" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
smime.p7s
Description: S/MIME Cryptographic Signature
