mysql decoder & rule for my log

[ambuj pandey]

Dear Team,

As i am new to Wazuh. I need to set up the MySQL general, error logs into Wazuh for my MySQL Linux server (as an agent.)

I am receiving log details into archive.json, but alert is not reading the logs

Please create a decoder & rule format for the below logs.

Log1:   (general log)

2023-08-08T08:35:09.422429Z      1362 Connect   Access denied for user 'dbadmin'@'172.110.13.43' (using password: YES)

2023-08-08T08:35:09.419603Z      1362 Connect   dba...@172.110.13.43 on  using TCP/IP

log2: (Error log)

2023-08-08T04:13:23.478988Z 1298 [Warning] [MY-010057] [Server] IP address '45.295.73.18' has been resolved to the host name '18.73.295.45.bc.googleusercontent.com', which resembles IPv4-address itself.

This will be very helpful.

Thanks in advance.

[Benjamin Nworah]

Dear ambuj,

Thank you for choosing Wazuh.

Please give me some time to work on this and revert back.

Regards,

[Benjamin Nworah]

Dear ambuj,

Please can you send me more logs for these same events (log1 and log2).
I patiently await your feedback.

Regards,

[ambuj pandey]

Dear Sir, 

Thanks for the quick update. please find the below logs, which are storing into the file.

Log1:   (general log)

2023-08-08T05:45:54.138014Z      1357 Query     SELECT id, firstname, lastname FROM MyGuests LIMIT 0, 1000
2023-08-08T05:45:55.100217Z      1357 Query     SHOW TABLES
2023-08-08T05:45:55.138710Z      1357 Query     SELECT id, firstname, lastname FROM MyGuests LIMIT 0, 1000
2023-08-08T05:45:55.326927Z      1357 Query     SHOW TABLES
2023-08-08T05:45:55.362494Z      1357 Query     SELECT id, firstname, lastname FROM MyGuests LIMIT 0, 1000
2023-08-08T05:45:55.555008Z      1357 Query     SHOW TABLES
2023-08-08T05:45:55.590066Z      1357 Query     SELECT id, firstname, lastname FROM MyGuests LIMIT 0, 1000
2023-08-08T05:45:57.786897Z      1357 Query     SHOW TABLES
2023-08-08T05:45:57.824309Z      1357 Query     SELECT id, firstname, lastname FROM MyGuests LIMIT 0, 1000
2023-08-08T05:46:07.686088Z      1357 Query     SHOW TABLES
2023-08-08T05:46:07.723303Z      1357 Query     SELECT id, firstname, lastname FROM MyGuests LIMIT 0, 1000
2023-08-08T07:49:56.255479Z      1359 Connect   test...@172.110.13.43 on  using TCP/IP
2023-08-08T07:49:56.259070Z      1359 Connect   Access denied for user 'testuser'@'172.110.13.43' (using password: YES)
2023-08-08T07:50:05.802109Z      1360 Connect   test...@172.110.13.43 on  using TCP/IP
2023-08-08T07:50:05.802179Z      1360 Connect   Access denied for user 'testuser'@'172.110.13.43' (using password: YES)
2023-08-08T07:50:13.535006Z      1361 Connect   test...@172.110.13.43 on  using TCP/IP
2023-08-08T07:50:13.535077Z      1361 Connect   Access denied for user 'testuser'@'172.110.13.43' (using password: YES)
2023-08-08T08:35:09.419603Z      1362 Connect   test...@172.110.13.43 on  using TCP/IP
2023-08-08T08:35:09.422429Z      1362 Connect   Access denied for user 'testuser'@'172.110.13.43' (using password: YES)

log2: (Error log)

2023-08-07T20:46:28.629106Z 1175 [Warning] [MY-010055] [Server] IP address '71.6.232.20' could not be resolved: Name or service not known
2023-08-07T22:55:03.665019Z 1176 [Warning] [MY-010058] [Server] Hostname 'apzg-0729a-058.stretchoid.com' does not resolve to '198.199.96.58'.
2023-08-08T02:38:59.838588Z 1177 [Warning] [MY-010055] [Server] IP address '110.52.217.17' could not be resolved: Temporary failure in name resolution
2023-08-08T03:15:54.308722Z 1178 [Warning] [MY-010055] [Server] IP address '74.235.140.187' could not be resolved: Name or service not known
2023-08-08T04:11:09.369300Z 1179 [Warning] [MY-010055] [Server] IP address '190.83.8.9' could not be resolved: Name or service not known
2023-08-08T04:11:32.659416Z 1199 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-08-08T04:11:58.713963Z 1221 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-08-08T04:12:30.779247Z 1249 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-08-08T04:13:23.005253Z 1296 [Warning] [MY-010057] [Server] IP address '35.187.98.121' has been resolved to the host name '121.98.187.35.bc.googleusercontent.com', which resembles IPv4-address itself.
2023-08-08T04:13:23.478988Z 1298 [Warning] [MY-010057] [Server] IP address '35.195.93.98' has been resolved to the host name '98.93.195.35.bc.googleusercontent.com', which resembles IPv4-address itself.
2023-08-08T04:13:56.843377Z 1326 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-08-08T04:14:20.487629Z 1347 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-08-08T04:30:45.326908Z 1355 [Warning] [MY-010055] [Server] IP address '20.64.155.174' could not be resolved: Name or service not known


Also, this will be helpful, if you share the rule to trigger the event.

[Benjamin Nworah]

Dear ambuj,

I have created the following decoders and rules for these logs:

2023-08-08T08:35:09.419603Z      1362 Connect   dba...@172.110.13.43 on  using TCP/IP

2023-08-08T04:13:23.478988Z 1298 [Warning] [MY-010057] [Server] IP address '45.295.73.18' has been resolved to the host name '18.73.295.45.bc.googleusercontent.com', which resembles IPv4-address itself

Decoders

============

<decoder name="test">
<prematch>Connect|\pWarning\p\s+\pMY-\d+\p</prematch>
</decoder>

<decoder name="test">
<parent>test</parent>
<regex offset="after_parent" type="pcre2">(?i)\s+(\S+)\.\.\.@(\d+.\d+.\d+.\d+)</regex>
<order>username, remote_ip</order>
</decoder>

<decoder name="test">
<parent>test</parent>
<regex offset="after_parent" type="pcre2">(?i)'(\d+.\d+.\d+.\d+)' has been resolved to the host name '(\S+)'</regex>
<order>ip_address,host_name</order>
</decoder>

Rules

===========

<group name="test,">
<rule id="100064" level="5">
<decoded_as>test</decoded_as>
<match type="pcre2">(?i)on\s+using TCP/IP</match>
<description>User $(username) connected from $(remote_ip) using TCP/IP</description>
</rule>

<rule id="100065" level="5">
<decoded_as>test</decoded_as>
<match type="pcre2">(?i)has been resolved to the host name</match>
<description>$(ip_address) has been resolved to $(host_name).</description>
</rule>
</group>

You can refer to the below links to create or modify the decoders to parse the other logs.
https://documentation.wazuh.com/current/user-manual/ruleset/custom.html
https://documentation.wazuh.com/current/user-manual/ruleset/ruleset-xml-syntax/index.html

Please let me know if this helps.

Regards,

[ambuj pandey]

Dear Sir, Thanks for the quick update.

I make the change in my decoder & rule.

For log 1, alerts are working... Thank u so much Sir.

But I have below point about the shared detail.

For log 2, "2023-08-08T04:13:23.478988Z 1298 [Warning] [MY-010057] [Server] IP address '45.295.73.18' has been resolved to the host name '18.73.295.45.bc.googleusercontent.com', which resembles IPv4-address itself"

I am still unable to receive any alert

.

.

Also please create decoder & rule for below queries logs.

"2023-08-08T05:45:55.138710Z      1357 Query     SELECT id, firstname, lastname FROM MyGuests LIMIT 0, 1000

2023-08-08T05:45:55.326927Z      1357 Query     SHOW TABLES
2023-08-08T05:45:55.362494Z      1357 Query     SELECT id, firstname, lastname FROM MyGuests LIMIT 0, 1000
2023-08-08T05:45:55.555008Z      1357 Query     SHOW TABLES
2023-08-08T05:45:55.590066Z      1357 Query     SELECT id, firstname, lastname FROM MyGuests LIMIT 0, 1000
2023-08-08T05:45:57.786897Z      1357 Query     SHOW TABLES
2023-08-08T05:45:57.824309Z      1357 Query     SELECT id, firstname, lastname FROM MyGuests LIMIT 0, 1000
2023-08-08T05:46:07.686088Z      1357 Query     SHOW TABLES

2023-08-08T05:46:07.723303Z      1357 Query     SELECT id, firstname, lastname FROM MyGuests LIMIT 0, 1000"

..

.

.

This will be very helpful.

Thanks in advance.

[Benjamin Nworah]

Dear ambuj,

The below log works. please you will need to restart the Wazuh manager after making this change:
systemclt restart wazuh-manager

Type one log per line

2023-08-08T04:13:23.478988Z 1298 [Warning] [MY-010057] [Server] IP address '45.295.73.18' has been resolved to the host name '18.73.295.45.bc.googleusercontent.com', which resembles IPv4-address itself

**Phase 1: Completed pre-decoding.
        full event: '2023-08-08T04:13:23.478988Z 1298 [Warning] [MY-010057] [Server] IP address '45.295.73.18' has been resolved to the host name '18.73.295.45.bc.googleusercontent.com', which resembles IPv4-address itself'
        timestamp: '2023-08-08T04:13:23.478988Z 129'

**Phase 2: Completed decoding.
        name: 'test'
        host_name: '18.73.295.45.bc.googleusercontent.com'
        ip_address: '45.295.73.18'

**Phase 3: Completed filtering (rules).
        id: '100065'
        level: '5'
        description: '45.295.73.18 has been resolved to 18.73.295.45.bc.googleusercontent.com.'
        groups: '['test']'
        firedtimes: '1'
        mail: 'False'
**Alert to be generated.


For the other logs, please give me some time to work on this and revert back.

Regards,

[Benjamin Nworah]

To restart the Wazuh manager, use the command:
systemctl restart wazuh-manager.

The command in my previous email is incorrect. kindly ignore.

Regards,

[ambuj pandey]

Dear Sir, Thanks for the update.

I restarted the service properly after changes in the rules and decoder.
1
For log2:

"2023-08-08T04:13:23.478988Z 1298 [Warning] [MY-010057] [Server] IP address '45.295.73.18' has been resolved to the host name '18.73.295.45.bc.googleusercontent.com', which resembles IPv4-address itself"

The test is working. Getting the same output while testing, but I am not receiving any alert logs.

2:

When I generate the log1.
Why this is used by MySql pre-build rule is triggered?

{"timestamp":"2023-08-09T06:02:41.009+0000","rule":{"level":9,"description":"MySQL: authentication failure.","id":"50106","firedtimes":1,"mail":false,"groups":["mysql_log","authentication_failed"],"pci_dss":["10.2.4","10.2.5","8.7"],"gpg13":["7.1"],"gdpr":["IV_35.7.d","IV_32.2"],"hipaa":["164.312.b","164.312.d","164.312.e.1","164.312.e.2.I","164.312.e.2.II"],"nist_800_53":["AU.14","AC.7","SC.2"],"tsc":["CC6.1","CC6.8","CC7.2","CC7.3","PI1.4","PI1.5"]},"agent":{"id":"001","name":"MNP-mail","ip":"22.222.1.5"},"manager":{"name":"paras-Test"},"id":"1691560961.2973778","full_log":"MySQL log: 2023-08-09T05:26:07.896564Z 1831 Connect   Access denied for user 'root'@'203.110.83.42' (using password: YES)","decoder":{"name":"mysql_log"},"location":"/var/log/mysql/mysql.log"}

3: 

please take your time to generate decoder for log 3:

"2023-08-08T05:45:55.138710Z      1357 Query     SELECT id, firstname, lastname FROM MyGuests LIMIT 0, 1000

2023-08-08T05:45:55.326927Z      1357 Query     SHOW TABLES
2023-08-08T05:45:55.362494Z      1357 Query     SELECT id, firstname, lastname FROM MyGuests LIMIT 0, 1000
2023-08-08T05:45:55.555008Z      1357 Query     SHOW TABLES

2023-08-08T05:46:07.723303Z      1357 Query     SELECT id, firstname, lastname FROM MyGuests LIMIT 0, 1000"

[Benjamin Nworah]

Dear ambuj,

Please let me test and revert back.

Regards,

[Benjamin Nworah]

Hello ambuj,

The decoder and rule works for me for the log  2. I have attached a screen shoot. I will work on the other logs you shared.

Regards,

[Benjamin Nworah]

Dear ambuj,

I have modified the decoders and rules to include the below logs:

2023-08-08T05:45:55.362494Z      1357 Query     SELECT id, firstname, lastname FROM MyGuests LIMIT 0, 1000
2023-08-08T05:45:55.555008Z      1357 Query     SHOW TABLES

Kindly copy the below decoder into the Wazuh server file /var/ossec/etc/decoders/local_decoder.xml.   Please note that I have changed the decoder name from "test" to "mysql_decoder".

<decoder name="mysql_decoder">
<prematch>Query|Connect|\pWarning\p\s+\pMY-\d+\p</prematch>
</decoder>

<decoder name="mysql_decoder">
<parent>mysql_decoder</parent>

<regex offset="after_parent" type="pcre2">(?i)\s+(\S+)\.\.\.@(\d+.\d+.\d+.\d+)</regex>
<order>username, remote_ip</order>
</decoder>

<decoder name="mysql_decoder">
<parent>mysql_decoder</parent>

<regex offset="after_parent" type="pcre2">(?i)'(\d+.\d+.\d+.\d+)' has been resolved to the host name '(\S+)'</regex>
<order>ip_address,host_name</order>
</decoder>

<decoder name="mysql_decoder">
<parent>mysql_decoder</parent>
<regex offset="after_parent" type="pcre2">(?i)select (.+)\s+from\s+(\S+)\s+limit\s+(\d+),\s+(\d+)</regex>
<order>query_parameters,table,offset,count</order>
</decoder>

For the rule, kindly copy the below rules into the Wazuh server file /var/ossec/etc/rules/local_rules.xml:

<group name="test,">
<rule id="100064" level="5">

<decoded_as>mysql_decoder</decoded_as>

<match type="pcre2">(?i)on\s+using TCP/IP</match>
<description>User $(username) connected from $(remote_ip) using TCP/IP</description>
</rule>

<rule id="100065" level="5">

<decoded_as>mysql_decoder</decoded_as>

<match type="pcre2">(?i)has been resolved to the host name</match>
<description>$(ip_address) has been resolved to $(host_name).</description>
</rule>

<rule id="100066" level="8">
<decoded_as>mysql_decoder</decoded_as>
<match type="pcre2">(?i)select.+limit</match>
<description>$(query_parameters) queried from $(table) table with a limit of $(offset) offset and $(count) count</description>
</rule>

<rule id="100067" level="5">
 <decoded_as>mysql_decoder</decoded_as>
 <match type="pcre2">(?i)show tables</match>
 <description>Query ran to list tables</description>
</rule>
</group>

Finally, restart the Wazuh manager service using the below command:

systemctl restart wazuh-manager

Please let me know if this helps.

Regards,