MariaDB Audit Plugin not creating Wazuh Alert

[Kevin]

Hi all,

Looking for some help with this as I'm not making any ground trying to resolve it.

I'm running Debian 12.4 using MariaDB 10.11 with the audit plugin. I'm forwarding the audit log to rsyslog, rsyslog is configured to send the log over to my Wazuh server (which it does receive).

I would like to create an alert for failed connect attempts, drop tables, create tables and alter tables etc. (but for now, I just want failed connect to work)

I've changed the my actual names to placeholders in the examples below, such as UserA would be the actual user, IE Bob, DatabaseName would be whatever the actual database name is, etc.

The audit log is formatted as the following:

For Failed connect attempts:

2024-02-12T21:02:00.208840-06:00 DatabaseServer1 mysql-server_auditing: <DatabaseServer1> DatabaseServer1,UserA,WorkstationA.company.com,68,0,FAILED_CONNECT,,,1045

Alter table:

2024-02-13T22:27:05.494217-06:00 DatabaseServer1 mysql-server_auditing: <DatabaseServer1> DatabaseServer1,UserA,WorkstationA.company.com,71,149,QUERY,DatabaseName,'ALTER TABLE `MyTable`\r\n\tCHANGE COLUMN `address1` `address` VARCHAR(100)',1142

I've tried adding a decoder to the local_decoder.xml and rules to local_rules.xml but it doesn't use them for the filtering at all. 

Here are the results from logtest for the failed connect:

**Phase 1: Completed pre-decoding.
        full event: '2024-02-12T21:02:00.208840-06:00 DatabaseServer1 mysql-server_auditing: <DatabaseServer1> DatabaseServer1,UserA,WorkstationA.company.com,68,0,FAILED_CONNECT,,,1045'
        timestamp: '2024-02-12T21:02:00.208840-06:00'
        program_name: 'mysql-server_auditing'

**Phase 2: Completed decoding.
        name: 'mariadb-syslog'
        mariadb.connectionid: '68'
        mariadb.host: 'WorkstationA.company.com'
        mariadb.info: 'DatabaseServer1'
        mariadb.operation: 'FAILED_CONNECT'
        mariadb.queryid: '0'
        mariadb.retcode: '1045'
        mariadb.username: 'UserA'

**Phase 3: Completed filtering (rules).
        id: '88100'
        level: '0'
        description: 'MariaDB group messages.'
        groups: '['mariadb']'
        firedtimes: '1'
        mail: 'False'

The same rule is used for the ALTER table as well. Below is my added decoder and rules, which I don't even know if I'm even close to doing it correctly lol it's just something I've found online, which I've tried many versions/combos and they never are used, it always goes to rule 88100.

<decoder name="mysql-server_auditing">
    <prematch>^mysql-server_auditing: &lt;DatabaseServer1&lt; DatabaseServer1</prematch>
    <regex>^.*,\s*([\w\.]+),\s*(\d+),\s*(\w+),.*$</regex>
    <order>srcip, id, status</order>
</decoder>

------- Rule

<rule id="100100" level="10">
    <decoded_as>mysql-server_auditing</decoded_as>
    <match>FAILED_CONNECT</match>
    <description>MariaDB: Failed connection attempt.</description>
</rule>

thank you for any help!

(I hope the formatting is good lol)

[Francisco Tuduri]

Hello Kevin!

Looking at your wazuh-logtest output we see, in Phase 2, that the default decoder for MariaDB is correctly decoding the events fields (operation, username).
However, the default rules for these events are only generating an alert based on the message type (error, warning, note). You can see these rules here: https://github.com/wazuh/wazuh/blob/master/ruleset/rules/0535-mariadb_rules.xml. The 'failed connect' event does not match any of these rules, except for the base one (88100).

To address the issue of generating an alert for the failed connect event, you can create a custom rule that acts as a 'child' of rule 88100. Here's a suggested approach:

<group name="mariadb,">

  <rule id="100100" level="10">

    <if_sid>88100</if_sid>
    <field name="mariadb.operation">FAILED_CONNECT</field>

    <description>MariaDB: Failed connection attempt.</description>
  </rule>

</group>

This rule uses the if_sid tag to create a 'child' rule of rule 88100. It will trigger an alert when the rule 88100 is triggered AND the field mariadb.operation matches FAILED_CONNECT.

You can follow a similar approach to create rules for other operations as well.

This is now the output of logtest with that new rule:

**Phase 1: Completed pre-decoding.
        full event: '2024-02-12T21:02:00.208840-06:00 DatabaseServer1 mysql-server_auditing: <DatabaseServer1> DatabaseServer1,UserA,WorkstationA.company.com,68,0,FAILED_CONNECT,,,1045'
        timestamp: '2024-02-12T21:02:00.208840-06:00'
        program_name: 'mysql-server_auditing'

**Phase 2: Completed decoding.
        name: 'mariadb-syslog'
        mariadb.connectionid: '68'
        mariadb.host: 'WorkstationA.company.com'
        mariadb.info: 'DatabaseServer1'
        mariadb.operation: 'FAILED_CONNECT'
        mariadb.queryid: '0'
        mariadb.retcode: '1045'
        mariadb.username: 'UserA'

**Phase 3: Completed filtering (rules).

        id: '100100'
        level: '10'
        description: 'MariaDB: Failed connection attempt.'

        groups: '['mariadb']'
        firedtimes: '1'
        mail: 'False'

**Alert to be generated.


Let me know if this solution works for you or if you need further assistance.
Regards!

[Kevin]

Francisco, you ARE the man! works like a champ.

Thank you so much for your help!