We had an issue with our installation where OSSIM database crashing with high CPU. After trouble shooting it was identified the following query takes extremely long to process, which causes the OSSIM database server to crash when the instance of the query increases.
SELECT incident.* FROM incident LEFT JOIN incident_ticket ON incident_ticket.incident_id = incident.id LEFT JOIN incident_subscrip ON incident_subscrip.incident_id=incident.id WHERE ( incident.in_charge in ('xxx') OR incident_ticket.users in ('xxx') OR incident_ticket.in_charge in ('xxx) OR incident_ticket.transferred in ('xxx') OR incident_subscrip.login in ('xxx') ) AND incident.status != 'Closed';
We then identified this is due to no index being available for "incident_subscrip" table. After adding the following index the query processed in 0.4 seconds. This may be an issue in your systems as well.
ALTER TABLE `alienvault`.`incident_subscrip` ADD INDEX `idx_id_login` (`incident_id` ASC, `login` ASC);
This came into our test labs when we started getting alarms from our testing and our SOC team was creating tickets on those alarms.