MSSQL | Multiline Output

[John Carry]

Dear Wazuh Team,

I have been successful for bringing the Multi-line MSSQL Query based via (Multiline Regex )logs to Wazuh (archive.log), but unfortunately there is no decoder or rule to analyze it.

So to solve that problem I have created Custom decoder and rule, but the problem is the rules are working fine with Query based logs when the Log entry is of single line but not working fine with Multiline log entry.

Actual logs at the Server End ( Wazuh-Agent, Monitored  Device):

Here you can see first highlighted log is single line and the other is multiline:

Below is the output of the triggered rule for multi-line Query based log, the problem is that it is only showing first line of it and discarding the following lines.

Below are the configured rule and decoder:

Requirement:

Please map a way that can help us to gather multi-line logs in alerts.

[John Carry]

Dear Wazuh Team,

Any update please ?

[John Ebuka Onyejegbu]

Hello John,

Looking at the decoder you wrote, i think there has to be some regex commands withing the decoder to make sure all the matching strings are in scope.

for the custom_mysql we can have something like this.

<decoder name="custom_mysql">
   <prematch>Query|Connect</prematch>
   <log_format>multi-line-regex</log_format>
   <multiline_regex replace="no-replace" match="all">(?m)(?:^#.*\n)+\w.*\n\w.*</multiline_regex>
</decoder>

the above will try to bring ALL the logs in scope.

we can also look at the below.

<decoder name="custom_mysql">
   <prematch>Query|Connect</prematch>
   <regex>^(?<timestamp>\S+)\s+(?<pid>\S+)\s+.*\s+--\s+(?<query>.*)</regex>
   <order>timestamp, pid, query</order>
</decoder>

the above follows a different regex pattern, by bringing in scope explicitly the timestamp, pid and query.

you can add more regex patterns to streamline to your requirement.

[John Carry]

Hello Ebuka,

Thanks for your response, further I have tried your below set of commands but there is some error been involved.

Your Provided Commands;

<decoder name="custom_mysql">
   <prematch>Query|Connect</prematch>
   <log_format>multi-line-regex</log_format>
   <multiline_regex replace="no-replace" match="all">(?m)(?:^#.*\n)+\w.*\n\w.*</multiline_regex>
</decoder>

Work At my end:

Note: Further be noted that I have intentionally removed your   <multiline_regex replac>......., in-rode to dignose the issue but unfortunately the issues lies under  <log_format>multi-line-regex</log_format>.

[John Ebuka Onyejegbu]

Hello John,

you can try the below.

<decoder name="custom_mysql">
   <prematch>Query[\s\S]*?|Connect[\s\S]*?</prematch>
</decoder>
<decoder name="error_mysql">
   <prematch>System[\s\S]*?|Warning[\s\S]*?</prematch>
</decoder>

<group name="mysql,">
    <rule id="115022" level="3">
        <decoded_as>custom_mysql</decoded_as>
        <location>/var/log/mysql/mysql.log</location>
        <match>Query</match>
        <description>The Query Based Alerts</description>
    </rule>
</group>

Let me know how it worked out.

[John Carry]

Dear Ebuka,

It not worked , please be noted that here our main concern is to capture and trigger alert for Multiline Query based logs where Analyst can view multiline Query logs on Wazuh.

There are different forms of Query based alerts that  are logged inside mysql.log, some of them are ending with ")" while some are ending without ")".

Example:

Please device a way that can capture all forms of Query based logs.

[John Ebuka Onyejegbu]

Hello John,

Sorry for the delay i had to reproduce the problem.

to get it to work i added a custom decoder in /var/ossec/etc/decoders/local_decoder.xml in 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>

i also created a rule in /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>

that is all, multi-line queries will be logged in wazuh.

Please note that you cannot test multi-line queries with the log test binary /var/ossec/bin/wazuh-logtest as each line will be treated as a separate terminal command, resulting in broken, missing or incomplete commands as you are experiencing. to properly test the multi-line logging you should use a proper SQL terminal or interface.

Regards.