| Open-AudIT https://www.open-audit.org/phpBB3/ |
|
| Need help deleting garbage data https://www.open-audit.org/phpBB3/viewtopic.php?f=20&t=6467 |
Page 1 of 1 |
| Author: | nathanpeters [ Thu Oct 22, 2015 9:20 am ] |
| Post subject: | Need help deleting garbage data |
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? |
|
| Author: | jpa [ Fri Oct 23, 2015 6:31 am ] |
| Post subject: | Re: Need help deleting garbage data |
Do you even care about any machine alerts? No? [code]http://openauditserver/open-audit/index.php/admin_db/delete_all_alerts[/code] 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'[/code]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. |
|
| Author: | nathanpeters [ Sat Oct 24, 2015 4:10 am ] |
| Post subject: | Re: Need help deleting garbage data |
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. |
|
| Page 1 of 1 | All times are UTC + 10 hours |
| Powered by phpBB® Forum Software © phpBB Group https://www.phpbb.com/ |
|