Not capturing Mysql Log data

[DaveK]

Hi all,

I am new to Wazuh and have set up a virtual machine with the single host architecture.  I have successfully added agents and am receiving the standard log data.

The problem is adding additional log to monitor for mysql.  I have added the following to the ossec.conf file on the client machine

<localfile>

    <log_format>mysql_log</log_format>

    <location>/var/log/mysqld.log</location>

  </localfile>

In the manager when I view the log files to be analyzed I see that it is listed.  No data is being captured though. 

I verified there was log data being generated on the client but nothing from the mysql log is showing up when I review the data in Kibana.  I saw that multi-line log files are not supported, but I do not believe are logs are of this type. 

 Example log output:

2019-10-03T22:25:49.537480Z 20281929 [Note] Access denied for user 'root'@'localhost' (using password: YES)

2019-10-03T22:28:47.419171Z 20283740 [Note] Access denied for user 'root'@'localhost' (using password: YES)

Any help would be greatly appreciated.

Thanks,

Dave

[Eva Lopez]

Hello Dave K,

I have confirmed it doesn't work and I have opened a Github issue to report it. Here you can read the issue and provide more information in case you think it is necessary:  https://github.com/wazuh/wazuh/issues/4056

We fix it as soon as possible. Until then, to can give you a solution. Can you confirm for us if you are monitoring Apache logs?

Best regards,
Eva.

[DaveK]

Hi Eva,

Thank you so much for looking into this.

To answer your question about apache, we do monitor apache logs.  It looks as though I am only getting data from the /var/log/httpd/access_log but I am not receiving data from the /var/log/httpd/error_log and it looks like I should be (I see new entries of warnings and notices in the error_log, but am not 100% sure they would fire a Wazuh rule).  

I confirmed both are in the ossec.conf file:

  <!-- Log analysis --

  <localfile>

    <log_format>apache</log_format>

    <location>/var/log/httpd/error_log</location>

  </localfile>

  <localfile>

    <log_format>apache</log_format>

    <location>/var/log/httpd/access_log</location>

  </localfile>

FYI, this is not the same server as the mysql log we are monitoring.  

Here is some sample output from the apache error_log (I masked the urls and IPs):

[Sat Oct 05 11:27:59 2019] [warn] [client 192.168.########] PHP Warning:  Use of undefined constant __TIMEOUT__ - assumed '__TIMEOUT__' (this will throw an php on line 34

[Sat Oct 05 11:27:59 2019] [notice] [client 192.168.########] PHP Notice:  Undefined variable: StarSize in /var/www/html/XXXXXXXXXX.php on line

[Sat Oct 05 11:27:59 2019] [notice] [client 192.168.########] PHP Notice:  Undefined variable: obj in /var/www/html/XXXXXXXXXXXX.php on line 174

[Sat Oct 05 11:27:59 2019] [notice] [client 192.168.########] PHP Notice:  Trying to get property 'mImg' of non-object in /var/www/html/XXXXXXXXXXX

[Sat Oct 05 11:27:59 2019] [warn] [client 192.168.########] PHP Warning:  imagedestroy() expects parameter 1 to be resource, null given in /var/www/html/XXXXXXXX

[Sun Oct 06 03:36:01 2019] [notice] SIGHUP received.  Attempting to restart

Both servers are running centos 7

Apache version is : Apache/2.2.15 (Unix)

Mysql version is: 14.14 Distrib 5.7.23, for Linux (x86_64)

Thanks again for your response, I will monitor the github link you provided.  Let me know if you need anything else.

Dave

[Eva Lopez]

Hello Dave,

It’s possible that logs you want to see in Kibana don’t match any rule and don’t generate alerts. You can create new rules and decoders for it using our documentation.

To check that I’m right you can use ossec-logtest. Logtest is a tool for testing and verification of rules and decoders.
An example of ossec-logtest output is the following:

2019/10/08 16:38:45 ossec-testrule: INFO: Started (pid: 9807).
ossec-testrule: Type one log per line.

[Sat Oct 05 11:27:59 2019] [warn] [client 192.168.########] PHP Warning:  imagedestroy() expects parameter 1 to be resource, null given in /var/www/html/XXXXXXXX

**Phase 1: Completed pre-decoding.
       full event: '[Sat Oct 05 11:27:59 2019] [warn] [client 192.168.########] PHP Warning:  imagedestroy() expects parameter 1 to be resource, null given in /var/www/html/XXXXXXXX'
       timestamp: 'Sat Oct 05 11:27:59 2019'
       hostname: 'lopezziur'
       program_name: '(null)'
       log: '[warn] [client 192.168.########] PHP Warning:  imagedestroy() expects parameter 1 to be resource, null given in /var/www/html/XXXXXXXX'

**Phase 2: Completed decoding.
       decoder: 'apache-errorlog'
       srcip: '192.168.########'

**Phase 3: Completed filtering (rules).
       Rule id: '30102'
       Level: '0'
       Description: 'Apache warn messages grouped.'

I have input the log you sent and the output was this.

Note the log processing consist of three phases.

1. The first phase tries to divide the logs in timestamp, hotsname, program_name, and message (which is sent to the second phase). If it does not succeed, it sends the complete log to the second phase.
2. The second phase is decoding. The decoders extract information from the received events.
3. The third phase consists of rules matching. In this phase, Wazuh decide if alert is generated.

Respect MySQL alerts you can test these rules. To do it:

1. Remove ruleset/decoders/0025-apache_decoders.xml and ruleset/decoders/0150-mysql_decoders.xml files.
   1. Add decoders found in custom-decoders.xml in etc/decoders/local_decoders.xml
   2. Move the rules files to ruleset/rules to replacing to default rules.

These rules seem works:

root@lopezziur:/var/ossec# bin/ossec-logtest 
2019/10/08 16:59:31 ossec-testrule: INFO: Started (pid: 10113).
ossec-testrule: Type one log per line.

2019-10-03T22:25:49.537480Z 20281929 [Note] Access denied for user 'root'@'localhost' (using password: YES)

**Phase 1: Completed pre-decoding.
       full event: '2019-10-03T22:25:49.537480Z 20281929 [Note] Access denied for user 'root'@'localhost' (using password: YES)'
       timestamp: '2019-10-03T22:25:49.537480Z 202'
       hostname: '1929'
       program_name: '(null)'
       log: '[Note] Access denied for user 'root'@'localhost' (using password: YES)'

**Phase 2: Completed decoding.
       decoder: 'custom-decoder'

**Phase 3: Completed filtering (rules).
       Rule id: '50106'
       Level: '9'
       Description: 'MySQL: authentication failure.'
**Alert to be generated.

To monitor MySQL file you can use Syslog log format:

<localfile>
    <log_format>syslog</log_format>
    <location>/var/log/mysqld.log</location>
</localfile>

I hope it helps you. If you have further questions let us know.

Best regards,
Eva

[DaveK]

Hi Eva,

Thank you so very much for your help.  We have followed your directions and are successfully capturing the mysql and apache log data.  It also helped us with understanding the custom rule / decoder creation process.  We were struggling with that a little bit.

I have one follow-up question though, since we modified the files in the /ruleset directory instead of the /etc/rules/local_rules.xml , can we still periodically run the update_ruleset command or is will that overwrite the changes?

Thanks again for the help!

Dave

[Eva Lopez]

Hi Dave,

Sorry, I forgot to mention if you update ruleset you lose the custom rules and decoders. After you update it, you should replace again the custom files.

The fact you save these files in ruleset and not in etc/rule is due we are modifying exists rules. Save it in etc/rules may cause problems.

New rules can be added etc/rules. If you need help to create a rule or decoder you send us the log you match it and we will help you.

Best regards,
Eva

​

​