Rules not triggered though log test successfully

[duc do]

Hello Wazuh Team.

I create a custom rule and a custom decoder in mysql like your instruction here in https://groups.google.com/g/wazuh/c/jYNtj1hLLJc/m/h6UXKdOBAQAJ.

Here is my sample log:

2022-11-16T09:15:54.892964Z        29 Connect   Access denied for user 'sontran'@'localhost' (using password: YES)
2022-11-16T09:17:24.043672Z        30 Connect   duc@localhost on  using Socket
2022-11-16T09:17:24.047775Z        30 Query     select @@version_comment limit 1
2022-11-16T09:17:31.447953Z        30 Query     show databases
2022-11-16T09:17:48.951929Z        30 Query     create database tranmai
2022-11-16T09:18:30.984183Z        30 Query     show databases

The result of ruleset test:

**Phase 1: Completed pre-decoding.    full event: '2022-11-16T09:17:24.047775Z 30 Query select @@version_comment limit 1'    timestamp: '2022-11-16T09:17:24.047775Z ' **Phase 2: Completed decoding.    name: 'custom_mysql'    _action: 'Query'    code: '30'    command: 'select @@version_comment limit 1' **Phase 3: Completed filtering (rules).    id: '100150'    level: '9'    description: 'The following query 'select @@version_comment limit 1' was run'    groups: '["mysql"]'    firedtimes: '3'    mail: 'false' **Alert to be generated.


It can test successfully in wazuh-logtest. But log can not be triggered and displayed in dashboard.  It only be displayed as raw data event.

Can you show me how to solve this problem?
Thanks

[Nicolas Agustin Guevara Pihen]

Hello, thank you for using Wazuh! 

I understand that you created a decoder and rules for those logs, there are working in logtest, but when you send actual logs from a localfile, you are not able to trigger an actual rule with that. 
I will try to replicate your issue to find a solution as soon as possible. 

Kind regards,

[Nicolas Agustin Guevara Pihen]

Hi, I tested this configuration and it works as expected. Could you confirm if something is different? 

1- Configure the localfile. It must be in syslog format as a workaround of this issue:  https://github.com/wazuh/wazuh/issues/12553. The file must have read permissions

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

2- Created a custom decoder and a custom rule: 
Decoder:
<decoder name="custom_mysql">
  <prematch>\d+\s\w+\s+</prematch>
  <regex>\s+(\d+)\s(\w+)\s+(\w\.*)</regex>
  <order>code, _action, command</order>
</decoder>

Rule:
<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>

3- Restart the manager (or the agent)
systemctl restart wazuh-manager

4- After the restart, when a log is added to the file, it should be shown as an alert


Regards,

On Wednesday, November 16, 2022 at 9:12:15 AM UTC-3 ducdo1998da...@gmail.com wrote:

[duc do]

Hi nicolas, thanks for your reply
1. I use agent.conf for centralized configuration and use config like this because my log is in folder  /var/lib/mysql/sontm1.log  :

                              <agent_config name="sontm1">
                                            <!-- Shared agent configuration here -->
                                          <localfile>
                                                <log_format>syslog</log_format>
                                                <location>/var/lib/mysql/sontm1.log</location>
                                                </localfile>
                                         </agent_config>

We used exactly same the decoder and rule you suggest for local_decoder and local_rule respectively, I also restarted wazuh manager and wazuh decoders after update  rule and decoder.

However, The event only display as raw data event when I changed index pattern to wazuh-archives-* , and NOT showing as alerts with wazuh-alerts-*

With full_log field in raw data event, I used field full_log to decode, but it shows No decoder match.  But if I access file log /var/lib/mysql/sontm1.log  directly and paste to log-test, It works well.

How can I solve this problem? 

Thanks

Vào lúc 20:06:39 UTC+7 ngày Thứ Tư, 16 tháng 11, 2022, nicolas...@wazuh.com đã viết:

[Nicolas Agustin Guevara Pihen]

Hi. 
I understand the issue now. Looking at the pictures, it seems that it may be related to a spacing issue that may be interfering with the decoder. 
To be able to do more testing on this issue, I will kindly request the following: 

• The log from the full_log field (the one that didn't work on the logtest)
  
• If possible, a copy of the log file of the agent with some logs (I don't need much of them, but want the file in case pasting the logs here could slightly modify them and alter the results) 
  
• The version of Wazuh that you are currently using

I will be looking forward to your answer.
Kind regards,

[duc do]

Hi Nicolas, Thanks for your reply
Here is info that you need:
1.The log from full_log field in raw data event:

2022-11-17T07:49:43.789887Z       12 Query    select @@version_comment limit 1
2022-11-17T07:49:49.854619Z       12 Query    show databases
2022-11-17T07:50:12.292728Z       12 Query    create database user
2022-11-17T07:50:19.763305Z       12 Query    show databases

2. 

Here is the log I copy DIRECTLY from my file log on ubuntu.

2022-11-17T07:49:43.789887Z        12 Query     select @@version_comment limit 1
2022-11-17T07:49:49.854619Z        12 Query     show databases
2022-11-17T07:50:12.292728Z        12 Query     create database user
2022-11-17T07:50:19.763305Z        12 Query     show databases

I also send the copy of file log with the file attached.

3. The version of wazuh i used: v4.3.8.
   I installed wazuh-agent on Ubuntu 20.04.5.

I'm really hope your reply soon.
Thanks,

Vào lúc 00:31:46 UTC+7 ngày Thứ Sáu, 18 tháng 11, 2022, nicolas...@wazuh.com đã viết:

[Nicolas Agustin Guevara Pihen]

Hi, I finished analyzing this issue, here are my findings and a possible solution: 

Issue
Apparently, the logs in the file contained some tabs instead of spaces. That caused the regex not to match and therefore the rule not to trigger. Additionally, the full_log field didn't show any tabs, but instead contained less spaces than the original log. That caused some issues with Wazuh's predecoder, that was taking spaces as part of the timestamp, and interfering with the way in which the custom decoder was supposed to work. 

Here you can see the different logs, the dots are spaces and the arrows are tabs:


Possible solution

• To solve the problem with the tabs, we can switch the decoder regex to PCRE2. In that format, the /s expression will match spaces and tabs.
  
• To solve the predecoder issue, we have to do a workaround: in the localfile configuration, we can set an out_format. That will add some information at the beginning of the log, and that will prevent the predecoder to interfere with the log. Additionally, with this approach we can set text that will be decoded as program_name, and will make it easier to match with the decoder. 
  

Here are the final configuration with those changes: 

Agent.conf:

<agent_config name="sontm1">
<!-- Shared agent configuration here -->
      <localfile>
           <log_format>syslog</log_format>
           <location>/var/lib/mysql/sontm1.log</location>

           <out_format>$(timestamp) $(hostname) customsql: $(log)</out_format>
     </localfile>
 </agent_config>

That will transform a log like 2022-11-17T07:49:43.789887Z        12 Query     select @@version_comment limit 1 into Nov 11  07:49:43  Agent01 customsql: 2022-11-17T07:49:43.789887Z        12 Query     select @@version_comment limit 1, adding a timestamp, the name of the agent and "customsql:" at the beginning. 

Decoder:
<decoder name="custom_mysql">
    <program_name>customsql</program_name>
    <regex type="pcre2">(\d+)\s(\w+)\s+(\w.*)</regex>

    <order>code, _action, command</order>
</decoder>

 
Note that the program_name is the same text that we added in the out_format configuration. You can change both to any text that you prefer, but take into account that some texts may cause the log to be decoded with another decoder. 

I hope you find this information helpful, please let me know if the solution works or if you have any other questions. 

Kind regards,