How to integrate MySQL logs in Wazuh

[Smriti Kaushal]

I need to push mysql logs to wazuh. I configured the ossec.conf file in the agent machine as following:

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

Also I configured the agent.conf file in Wazuh manager :

<agent_config>
    <localfile>
        <log_format>mysql_log</log_format>
        <location>/var/log/mysql/mysql_general.log</location>
    </localfile>

</agent_config>

But wazuh is not able to pull mysql logs.

[Jonathan Martín Valera]

Hi,

Currently, there seem to be incompatibility problems with the MySQL log format https://github.com/wazuh/wazuh/issues/12553.

The workaround for this would be to change <log_format>mysql_log</log_format> to <log_format>syslog</log_format> and see how the received data can be decoded later.

Can you share with me one or several log examples of your MySQL version, and I try to show you some examples?

Best regards.

​

[Smriti Kaushal]

yes sure,

here are some examples of MySQL logs that need to be pushed in Wazuh.

2022-09-16T11:49:27.368136Z        14 Query     select @@version_comment limit 1
2022-09-16T11:49:51.978151Z        14 Query     show databases

2022-09-16T11:51:22.266746Z        14 Query     show databases

2022-09-16T11:52:29.668365Z        14 Init DB   test_db
2022-09-16T11:52:29.670694Z        14 Query     show tables
2022-09-16T11:52:29.671149Z        14 Field List        table1

[Jonathan Martín Valera]

Ok,

I will show you an example to generate an alert when an action is logged as a query (containing Query).

First, we apply this configuration in the /var/ossec/etc/ossec.conf to monitor the mysql log:

<localfile>
    <log_format>syslog</log_format>
    <location>/var/log/mysql/mysql_general.log</location>
</localfile>

Restart the wazuh-agent or wazuh-manager (depending on where you are configuring) to apply the configuration:

systemctl restart wazuh-agent

Note: Please note, that in this case we are going to use syslog as log format.

Now, let’s add the following custom decoder that will allow us to decode the mysql log format you have shared. Add the following decoder in /var/ossec/etc/decoders/local_decoder.xml of wazuh-manager.

<decoder name="custom_mysql">
   <prematch>\d+\s\w+\s+</prematch>
   <regex>\s+(\d+)\s(\w+)\s+(\w\.*)</regex>
   <order>code, _action, command</order>
</decoder>

Note: The name action is reserved, therefore _action has been used.

Next, we are going to create a rule that allows us to generate an alert for the cases in which the log corresponds to a query (when the field decoded as _action corresponds to the Query value. For that, we add the following alert in the file /var/ossec/etc/rules/local_rules.xml.

<group name="mysql,">
    <rule id="100150" level="3">
        <decoded_as>custom_mysql</decoded_as>
        <field name="_action">Query</field>
        <description>The following query '$(command)' was run</description>
    </rule>
</group>

Now, we restart the wazuh-manager to apply the changes in the decoders and rules:

systemctl restart wazuh-manager

Finally, we tested the log 2022-09-16T11:49:51.978151Z 14 Query show databases in the /var/ossec/bin/wazuh-logtest tool:

2022-09-16T11:49:51.978151Z        14 Query     show databases

**Phase 1: Completed pre-decoding.
    full event: '2022-09-16T11:49:51.978151Z        14 Query     show databases'
    timestamp: '2022-09-16T11:49:51.978151Z    '

**Phase 2: Completed decoding.
    name: 'custom_mysql'
    _action: 'Query'
    code: '14'
    command: 'show databases'

**Phase 3: Completed filtering (rules).
    id: '100150'
    level: '5'
    description: 'The following query 'show databases' was run'
    groups: '['mysql']'
    firedtimes: '1'
    mail: 'False'
**Alert to be generated.

As you can see, from now on alerts will be generated for all queries that log into the file that wazuh is monitoring. You can edit or create the decoders and rules you need for your use case. I refer you useful information for this:

• Creating decoders and rules from scratch: https://wazuh.com/blog/creating-decoders-and-rules-from-scratch/
• Sibling decoders: flexible extraction of information: https://wazuh.com/blog/sibling-decoders-flexible-extraction-of-information/
• Custom rules and decoders: https://documentation.wazuh.com/current/user-manual/ruleset/custom.html
• Testing decoders and rules: https://documentation.wazuh.com/current/user-manual/ruleset/testing.html

Best regards.

​

[Smriti Kaushal]

Hey!

Thanks for your help.

[Rizwan Alam Azaad]

Hey Jonathan Martín Valera   , I am new to Wazuh, I was facing the same issue and tried the decoder and rules mentioned above by you, but it isn't working correctly for me, I have same logs to decode but no luck, 

Here is my log:

 2023-02-13T10:19:23.486395Z     19462 Query     SELECT @@session.transaction_read_only

The log that gets successful decoding is:

2022-09-16T11:49:51.978151Z        14 Query     show databases

The only difference between them is of spaces count.

Can you help me figure out as I have tried searching but no luck.

Thank you!

Regards,

Rizwan

On Monday, September 19, 2022 at 3:11:38 PM UTC+5 Smriti Kaushal wrote:

Hey!

Thanks for your help.