Open-AudIT

What's on your network?
It is currently Sat Jan 20, 2018 6:59 pm

All times are UTC + 10 hours




Post new topic Reply to topic  [ 3 posts ] 
Author Message
PostPosted: Thu Oct 22, 2015 9:20 am 
Offline
Newbie

Joined: Fri Aug 08, 2014 3:48 am
Posts: 8
Due to some issues with a collection script we had sending some xml files in an invalid format, we have ended up with our oa_alert_log table having over 1.3 million entries for one of our servers! Basically, we would get a good audit followed by a basically empty one so we have hundreds of thousands of entries like this "removed partition /", "added partition/", "remove software package bash", "added software package bash" etc.

This means:
1)I cannot load the server to display through the web ui because open-audit quickly exceeds the 500mb php memory limit.

We are trying to figure out how to easily get rid of the invalid audits without deleting our entire database. We know the date that we started sending valid data, but short of deleting all oa_alert_log entries before that date, I can't seem to find a way to do this using open-audit and not directly editing the sql database.

Also, we could just set the server status to "deleted" but that doesn't actually remove anything from the database. We are also not sure if the audit script runs on a server with the status "deleted" will it just update the deleted record with the data rendering it virtually invisible, or would it ignore the audit because of the deleted status, or would it create a new server record?

I know that depending if foreign key constraints were added or not, the oa_alert_log links to 11 other log tables, so any real cleanup would have to remove all entries tied to the system key of the system before deleting the oa_alert_log entries themselves.

Is there any easier way?


Top
 Profile  
Reply with quote  
PostPosted: Fri Oct 23, 2015 6:31 am 
Offline
Moderator

Joined: Fri Jul 20, 2007 8:27 am
Posts: 1226
Do you even care about any machine alerts? No?
Code:
http://openauditserver/open-audit/index.php/admin_db/delete_all_alerts


If you only want to clear the alerts for the suspect machine then I think direct SQL is the best. Something like:
Code:
DELETE From oa_alert_log Inner Join system On oa_alert_log.system_id = system.system_id Where system.hostname = 'BadServerHostName'
You can make the delete query as complex as you want to save more data. I didn't test the above but it's fairly simple so it should work. You'll need to get a SQL tool or some sort or use the mysql command line.


Top
 Profile  
Reply with quote  
PostPosted: Sat Oct 24, 2015 4:10 am 
Offline
Newbie

Joined: Fri Aug 08, 2014 3:48 am
Posts: 8
Oh wow, I somehow completely missed that database maintenance page in the ui. That did exactly what I needed.

Since you can specify a date , we just purged all alerts older than 30 days (the garbage data came in 2 months ago) and the record now loads fine.


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 posts ] 

All times are UTC + 10 hours


Who is online

Users browsing this forum: No registered users and 9 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB® Forum Software © phpBB Group