Unable to analyse MySQL error logs in OSSEC

Question

I am trying to analyze MySQL error logs that are generated on my OSSEC agent and raise alerts using OSSEC server.

here is the code block added to /var/ossec/etc/ossec.conf on the agent side to read the error logs of MySQL from the agent:

  <localfile>
    <log_format>mysql_log</log_format>
    <location>/var/log/mysql/error.log</location>
  </localfile>

After doing so I have restarted the agent and server but unable test any error logs that are getting generated on the agent side like:

2020-09-15T04:09:24.164859Z 12 [Note] Access denied for user 'root'@'localhost' (using password: YES)

As per doc https://ossec-docs.readthedocs.io/en/latest/docs/programs/ossec-logtest.html under Caveats we need to add MySQL log: to the log generated for the ossec-logtest.

This will be added automatically when we send these logs to the OSSEC server for analysis from the agent.

ossec-logtest result for MySQL error log

ossec-logtest is working fine after adding MySQL log: to the beginning but they are not working in the realtime.

Can anyone please help me through this problem.

Answer 1

The fact that ossec-logtest trigger an alert means that mysql decoder and rules are working fine

Check on Agent

• MySql is running. systemctl status mysqld.service

• MySql configuration (loglevel and output file) allow to log that kind of event . See here

If the value is greater than 1, aborted connections are written to the error log, and access-denied errors for new connection attempts are written.

• MySql is effectively logging 'Access denied': grep "Access denied" /var/log/mysql/error.log
• Ossec and their processes is running ok: /var/ossec/bin/ossec-control status

Check on Manager

• log_alert_level field in /var/ossec/etc/ossec.conf is lower o equal than 9 (loglevel showed in your ossec-logtest)