Wazuh | MySQL | No Decoder Match
117 views
Skip to first unread message
John Carry
Apr 12, 2023, 7:39:56 AM4/12/23
to Wazuh mailing list
Dear Wazuh Team,
We are in the process of integrating Database MySQL with our Wazuh SIEM where we are observing that there is no proper decoder for MYSQL Database 8.0 logs, when we tried to manually explore the decoder by searching for mysql under the section decoders we came with Decoder Named "0150-mysql_decoders.xml" as below where a single <prematch> is used which is not functional interms of identifying our mysql logs because we are not observing prematch in our logs..
Below are our mysql sample logs:
Error.logs:
2023-04-12T10:09:41.749994Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.32-0ubuntu0.22.04.2) initializing of server in progress as process 16320
2023-04-12T10:09:41.759101Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-04-12T10:09:43.432363Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-04-12T10:09:45.516204Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2023-04-12T10:09:47.022671Z 6 [System] [MY-013172] [Server] Received SHUTDOWN from user boot. Shutting down mysqld (Version: 8.0.32-0ubuntu0.22.04.2).
2023-04-12T10:09:51.560354Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32-0ubuntu0.22.04.2) starting as process 16364
2023-04-12T10:09:51.618531Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-04-12T10:09:52.103816Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-04-12T10:09:52.448378Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-04-12T10:09:52.448414Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-04-12T10:09:52.454513Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/tmp' in the path is accessible to all OS users. Consider choosing a different directory.
2023-04-12T10:09:52.475310Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: /var/run/mysqld/mysqlx.sock
2023-04-12T10:09:52.475475Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.32-0ubuntu0.22.04.2' socket: '/tmp/tmp.vM6p1R3bXk/mysqld.sock' port: 0 (Ubuntu).
2023-04-12T10:09:52.573946Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.32-0ubuntu0.22.04.2).
2023-04-12T10:09:54.037239Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.32-0ubuntu0.22.04.2) (Ubuntu).
2023-04-12T10:09:55.049691Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32-0ubuntu0.22.04.2) starting as process 16433
2023-04-12T10:09:55.061517Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-04-12T10:09:55.358759Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-04-12T10:09:55.593938Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-04-12T10:09:55.593971Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-04-12T10:09:55.598915Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/tmp' in the path is accessible to all OS users. Consider choosing a different directory.
2023-04-12T10:09:55.621387Z 7 [System] [MY-013172] [Server] Received SHUTDOWN from user boot. Shutting down mysqld (Version: 8.0.32-0ubuntu0.22.04.2).
2023-04-12T10:09:55.625219Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2023-04-12T10:09:57.187053Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.32-0ubuntu0.22.04.2) (Ubuntu).
2023-04-12T10:09:58.959065Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32-0ubuntu0.22.04.2) starting as process 16551
2023-04-12T10:09:58.967769Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-04-12T10:09:59.325986Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-04-12T10:09:59.548760Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-04-12T10:09:59.548793Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-04-12T10:09:59.575062Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2023-04-12T10:09:59.575201Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.32-0ubuntu0.22.04.2' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu).
2023-04-12T10:27:50.006461Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.32-0ubuntu0.22.04.2).
2023-04-12T10:27:51.621146Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.32-0ubuntu0.22.04.2) (Ubuntu).
2023-04-12T10:52:51.852536Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32-0ubuntu0.22.04.2) starting as process 19600
2023-04-12T10:52:51.910448Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-04-12T10:52:52.502480Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-04-12T10:52:52.824280Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-04-12T10:52:52.824323Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-04-12T10:52:52.859917Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2023-04-12T10:52:52.860065Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.32-0ubuntu0.22.04.2' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu).
2023-04-12T10:09:41.759101Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-04-12T10:09:43.432363Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-04-12T10:09:45.516204Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2023-04-12T10:09:47.022671Z 6 [System] [MY-013172] [Server] Received SHUTDOWN from user boot. Shutting down mysqld (Version: 8.0.32-0ubuntu0.22.04.2).
2023-04-12T10:09:51.560354Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32-0ubuntu0.22.04.2) starting as process 16364
2023-04-12T10:09:51.618531Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-04-12T10:09:52.103816Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-04-12T10:09:52.448378Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-04-12T10:09:52.448414Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-04-12T10:09:52.454513Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/tmp' in the path is accessible to all OS users. Consider choosing a different directory.
2023-04-12T10:09:52.475310Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: /var/run/mysqld/mysqlx.sock
2023-04-12T10:09:52.475475Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.32-0ubuntu0.22.04.2' socket: '/tmp/tmp.vM6p1R3bXk/mysqld.sock' port: 0 (Ubuntu).
2023-04-12T10:09:52.573946Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.32-0ubuntu0.22.04.2).
2023-04-12T10:09:54.037239Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.32-0ubuntu0.22.04.2) (Ubuntu).
2023-04-12T10:09:55.049691Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32-0ubuntu0.22.04.2) starting as process 16433
2023-04-12T10:09:55.061517Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-04-12T10:09:55.358759Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-04-12T10:09:55.593938Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-04-12T10:09:55.593971Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-04-12T10:09:55.598915Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/tmp' in the path is accessible to all OS users. Consider choosing a different directory.
2023-04-12T10:09:55.621387Z 7 [System] [MY-013172] [Server] Received SHUTDOWN from user boot. Shutting down mysqld (Version: 8.0.32-0ubuntu0.22.04.2).
2023-04-12T10:09:55.625219Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2023-04-12T10:09:57.187053Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.32-0ubuntu0.22.04.2) (Ubuntu).
2023-04-12T10:09:58.959065Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32-0ubuntu0.22.04.2) starting as process 16551
2023-04-12T10:09:58.967769Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-04-12T10:09:59.325986Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-04-12T10:09:59.548760Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-04-12T10:09:59.548793Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-04-12T10:09:59.575062Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2023-04-12T10:09:59.575201Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.32-0ubuntu0.22.04.2' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu).
2023-04-12T10:27:50.006461Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.32-0ubuntu0.22.04.2).
2023-04-12T10:27:51.621146Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.32-0ubuntu0.22.04.2) (Ubuntu).
2023-04-12T10:52:51.852536Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32-0ubuntu0.22.04.2) starting as process 19600
2023-04-12T10:52:51.910448Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-04-12T10:52:52.502480Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-04-12T10:52:52.824280Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-04-12T10:52:52.824323Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-04-12T10:52:52.859917Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2023-04-12T10:52:52.860065Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.32-0ubuntu0.22.04.2' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu).
General.log:
root@Linux-Test-Machine:/var/log/mysql# cat mysql.log
/usr/sbin/mysqld, Version: 8.0.32-0ubuntu0.22.04.2 ((Ubuntu)). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
2023-04-12T10:52:52.799554Z 0 Execute CREATE TABLE performance_schema.innodb_redo_log_files(
`FILE_ID` BIGINT NOT NULL COMMENT 'Id of the file.',
`FILE_NAME` VARCHAR(2000) NOT NULL COMMENT 'Path to the file.',
`START_LSN` BIGINT NOT NULL COMMENT 'LSN of the first block in the file.',
`END_LSN` BIGINT NOT NULL COMMENT 'LSN after the last block in the file.',
`SIZE_IN_BYTES` BIGINT NOT NULL COMMENT 'Size of the file (in bytes).',
`IS_FULL` TINYINT NOT NULL COMMENT '1 iff file has no free space inside.',
`CONSUMER_LEVEL` INT NOT NULL COMMENT 'All redo log consumers registered on smaller levels than this value, have already consumed this file.'
)engine = 'performance_schema'
2023-04-12T10:59:38.698846Z 8 Connect root@localhost on using Socket
2023-04-12T10:59:38.698929Z 8 Connect Access denied for user 'root'@'localhost' (using password: YES)
2023-04-12T11:10:26.675661Z 9 Connect root@localhost on using Socket
2023-04-12T11:10:26.675732Z 9 Connect Access denied for user 'root'@'localhost' (using password: YES)
2023-04-12T11:10:32.369906Z 10 Connect root@localhost on using Socket
2023-04-12T11:10:32.414517Z 10 Query select @@version_comment limit 1
2023-04-12T11:14:59.253132Z 10 Query CREATE DATABASE dbname
2023-04-12T11:15:19.458977Z 10 Query SHOW DATABASES
2023-04-12T11:18:51.824492Z 10 Query CREATE DATABASE XXXX_IS
2023-04-12T11:21:07.963596Z 10 Query SELECT DATABASE()
2023-04-12T11:21:07.977563Z 10 Init DB XXXX_IS
2023-04-12T11:21:07.979360Z 10 Query show databases
2023-04-12T11:21:07.981414Z 10 Query show tables
2023-04-12T11:21:07.999270Z 10 Query CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(30) NOT NULL,
email VARCHAR(50) NOT NULL,
password VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
As you have noticed that general.log have few lines that are multi-line output, I would request you to please help us out in successfully decoding and alerting on Wazuh as we are unable any find any help on it.
/usr/sbin/mysqld, Version: 8.0.32-0ubuntu0.22.04.2 ((Ubuntu)). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
2023-04-12T10:52:52.799554Z 0 Execute CREATE TABLE performance_schema.innodb_redo_log_files(
`FILE_ID` BIGINT NOT NULL COMMENT 'Id of the file.',
`FILE_NAME` VARCHAR(2000) NOT NULL COMMENT 'Path to the file.',
`START_LSN` BIGINT NOT NULL COMMENT 'LSN of the first block in the file.',
`END_LSN` BIGINT NOT NULL COMMENT 'LSN after the last block in the file.',
`SIZE_IN_BYTES` BIGINT NOT NULL COMMENT 'Size of the file (in bytes).',
`IS_FULL` TINYINT NOT NULL COMMENT '1 iff file has no free space inside.',
`CONSUMER_LEVEL` INT NOT NULL COMMENT 'All redo log consumers registered on smaller levels than this value, have already consumed this file.'
)engine = 'performance_schema'
2023-04-12T10:59:38.698846Z 8 Connect root@localhost on using Socket
2023-04-12T10:59:38.698929Z 8 Connect Access denied for user 'root'@'localhost' (using password: YES)
2023-04-12T11:10:26.675661Z 9 Connect root@localhost on using Socket
2023-04-12T11:10:26.675732Z 9 Connect Access denied for user 'root'@'localhost' (using password: YES)
2023-04-12T11:10:32.369906Z 10 Connect root@localhost on using Socket
2023-04-12T11:10:32.414517Z 10 Query select @@version_comment limit 1
2023-04-12T11:14:59.253132Z 10 Query CREATE DATABASE dbname
2023-04-12T11:15:19.458977Z 10 Query SHOW DATABASES
2023-04-12T11:18:51.824492Z 10 Query CREATE DATABASE XXXX_IS
2023-04-12T11:21:07.963596Z 10 Query SELECT DATABASE()
2023-04-12T11:21:07.977563Z 10 Init DB XXXX_IS
2023-04-12T11:21:07.979360Z 10 Query show databases
2023-04-12T11:21:07.981414Z 10 Query show tables
2023-04-12T11:21:07.999270Z 10 Query CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(30) NOT NULL,
email VARCHAR(50) NOT NULL,
password VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
As you have noticed that general.log have few lines that are multi-line output, I would request you to please help us out in successfully decoding and alerting on Wazuh as we are unable any find any help on it.
Andres Micalizzi
Apr 17, 2023, 3:29:54 PM4/17/23
to Wazuh mailing list
Hello John,
Thanks for Using Wazuh.
We recommend that you create custom decoders and rules that you can use with the logs. You can refer to the documentation here, to check the syntax and fields. After generating the decoders you should generate the rules that match with each log as necessary.
As an example, I have created a simple decoder and child decoder, and a rule that will decode the following log: 2023-04-12T10:59:38.698929Z 8 Connect Access denied for user 'root'@'localhost' (using password: YES)
<decoder name="mysql2">
<prematch>\.*Connect</prematch>
------------------------------------------------------------------------------------
Thanks for Using Wazuh.
We recommend that you create custom decoders and rules that you can use with the logs. You can refer to the documentation here, to check the syntax and fields. After generating the decoders you should generate the rules that match with each log as necessary.
As an example, I have created a simple decoder and child decoder, and a rule that will decode the following log: 2023-04-12T10:59:38.698929Z 8 Connect Access denied for user 'root'@'localhost' (using password: YES)
- DECODERS:
<prematch>\.*Connect</prematch>
</decoder>
<decoder name="mysql2-connect">
<parent>mysql2</parent>
<regex>Access denied for user ('\.*'@'\.*')\.*</regex>
<order>user</order>
</decoder>
<parent>mysql2</parent>
<regex>Access denied for user ('\.*'@'\.*')\.*</regex>
<order>user</order>
</decoder>
- RULE:
<group name="mysql2">
<rule id="100001" level="5">
<decoded_as>mysql2</decoded_as>
<regex>Access denied for user</regex>
<description>Access for user denied</description>
</rule>
</group>
<rule id="100001" level="5">
<decoded_as>mysql2</decoded_as>
<regex>Access denied for user</regex>
<description>Access for user denied</description>
</rule>
</group>
------------------------------------------------------------------------------------
You can check the output in the linked file.
I hope this helps to guide you in the process. In case you need further assistence, do not hesitate to write.
Cheers
Reply all
Reply to author
Forward
0 new messages