[ossec-list] How to purge/remove/delete data older than a specific date from within the database

[Dimitris Chontzopoulos]

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

[dan (ddp)]

I think everything in the database it timestamped. You should be able
to make a query to delete everything previous to a certain date.

[tanishk lakhaani]

Hi,

Just type in the following commands:

 

cd /var/ossec/logs/alerts/<year>/<month>/

 

Here you find the logs of all the days in each month. Just delete them using rm -rf command. I havent come across any oher automated tool for the same.

 

Regards

Tanishk

--
warm regards
Tanishk Lakhaani

[Dimitris Chontzopoulos]

Hey Tanishk,

I'm OK with the log files, it's the database I'm trying to remove older events from.



Dimitris

[Dimitris Chontzopoulos]

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: ossec...@googlegroups.com [mailto:ossec...@googlegroups.com] On Behalf Of dan (ddp)
Sent: Tuesday, February 22, 2011 03:52
To: ossec...@googlegroups.com
Subject: Re: [ossec-list] How to purge/remove/delete data older than a specific date from within the database

[Jamey B]

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/

[James Siegel]

this is also similar to a question I have about being able to modify the DB with known good changes to files so we don't get alerted on our hotfix process

[Ryan Schulze]

On a large deployment I wouldn't recommend doing the delete on the 'data' and 'alert' tables since that locks those tables during the operation (may take a while depending on the size of your tables). If this isn't an issue for you, then deleting is the easiest way to get rid of old data.

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, ...).

--

---
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 ossec-list+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.