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

@Code the issue is not with the mysql password. The issue is with ossec which is unable to analyze the MySQL logs which are generated on the ossec agent. — rana bhagath chand, Sep 15 '20 at 04:45
I think this may help https://stackoverflow.com/questions/2995054/access-denied-for-user-rootlocalhost-using-passwordno — Ericgit, Sep 15 '20 at 04:52

score 0 · Answer 1 · answered Sep 15 '20 at 16:41

0

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)

answered Sep 15 '20 at 16:41

Hi Juan, I got your point but the main issue is the logs are not reaching on the server-side while using the mysql_log format in the ossec.conf file. Therefore it is unable to analyze in the real time. Do you have any idea regarding it? – rana bhagath chand Sep 15 '20 at 19:03
It's a long journey from generating logs to seeing the alert in the manager, where each step can be a filter that blocks the flow of data. What behavior indicates the is a server-side issue? I suggest you start the analysis of the problem starting from the agent. – Juan Nicolas Asselle Sep 15 '20 at 19:21

1 Answers1