wazuh and MSSQL audit logs

[Dimitar Penkov]

Hello 

This is my first post so plese be gentle ;)

im trying to send audit logs from mssql to wazuh to get alerts when someone is executing query  

problem is that there is no rules for that matches on message comes from mssql

what i see is that eventID is 33205 and message comes with severity value "severityValue\":\"AUDIT_SUCCESS\" i check that there is a such rule but nothing match

i try to create rule but looks like is not working

<rule id="100103" level="0">
    <if_sid>60001</if_sid>
    <field name="win.system.severityValue">^AUDIT_SUCCESS$</field>
    <description>Windows audit success event</description>
  </rule>

Here is example of message

{"timestamp":"2019-09-13T09:41:29.47+0300","agent":{"id":"041","name":"DB01","ip":"10.22.3.61"},"manager":{"name":"IS3.domain.office"},"id":"1568356889.700828450","full_log":"{\"win\":{\"system\":{\"providerName\":\"MSSQL$DB01\",\"eventID\":\"33205\",\"level\":\"0\",\"task\":\"5\",\"keywords\":\"0xa0000000000000\",\"systemTime\":\"2019-09-13T06:41:24.593240800Z\",\"eventRecordID\":\"3009319\",\"channel\":\"Application\",\"computer\":\"DB01.domain.prod\",\"severityValue\":\"AUDIT_SUCCESS\",\"message\":\"Audit event: audit_schema_version:1 event_time:2019-09-13 06:41:23.7965120 sequence_number:1 action_id:SL   succeeded:true is_column_permission:true session_id:89 server_principal_id:288 database_principal_id:77 target_server_principal_id:0 target_database_principal_id:0 object_id:2046630334 user_defined_event_id:0 class_type:U  permission_bitmask:00000000000000000000000000000001 sequence_group_id:4E88E82C-C3FF-4A6F-9CD0-D92D9FD16359 session_server_principal_name:OFFICE\\\\sys.test server_principal_name:OFFICE\\\\sys.test server_principal_sid:010500000000000515000000f4c6826aa3951380da98d5dd0b120000 database_principal_name:OFFICE\\\\sys.test target_server_principal_name: target_server_principal_sid: target_database_principal_name: server_instance_name:DB01\\\\DB01 database_name:test schema_name:dbo object_name:message statement:SELECT id, medium, gate_message_id, charge_operation_id, datediff(hour, dt, getdate()) hours_old FROM message WHERE status=5 and dt>dateadd(hour, -30, getdate()) and medium = 'P' ORDER BY id additional_information: user_defined_information: .\"},\"eventdata\":{\"data\":\"audit_schema_version:1 event_time:2019-09-13 06:41:23.7965120 sequence_number:1 action_id:SL   succeeded:true is_column_permission:true session_id:89 server_principal_id:288 database_principal_id:77 target_server_principal_id:0 target_database_principal_id:0 object_id:2046630334 user_defined_event_id:0 class_type:U  permission_bitmask:00000000000000000000000000000001 sequence_group_id:4E88E82C-C3FF-4A6F-9CD0-D92D9FD16359 session_server_principal_name:OFFICE\\\\sys.test server_principal_name:OFFICE\\\\sys.test server_principal_sid:010500000000000515000000f4c6826aa3951380da98d5dd0b120000 database_principal_name:OFFICE\\\\sys.test target_server_principal_name: target_server_principal_sid: target_database_principal_name: server_instance_name:DB01\\\\DB01 database_name:test schema_name:dbo object_name:message statement:SELECT id, medium, gate_message_id, charge_operation_id, datediff(hour, dt, getdate()) hours_old FROM message WHERE status=5 and dt&gt;dateadd(hour, -30, getdate()) and medium = 'P' ORDER BY id additional_information: user_defined_information:\"}}}","decoder":{"name":"windows_eventchannel"},"data":{"win":{"system":{"providerName":"MSSQL$DB01","eventID":"33205","level":"0","task":"5","keywords":"0xa0000000000000","systemTime":"2019-09-13T06:41:24.593240800Z","eventRecordID":"3009319","channel":"Application","computer":"DB.domain.prod","severityValue":"AUDIT_SUCCESS","message":"Audit event: audit_schema_version:1 event_time:2019-09-13 06:41:23.7965120 sequence_number:1 action_id:SL   succeeded:true is_column_permission:true session_id:89 server_principal_id:288 database_principal_id:77 target_server_principal_id:0 target_database_principal_id:0 object_id:2046630334 user_defined_event_id:0 class_type:U  permission_bitmask:00000000000000000000000000000001 sequence_group_id:4E88E82C-C3FF-4A6F-9CD0-D92D9FD16359 session_server_principal_name:OFFICE\\sys.test server_principal_name:OFFICE\\sys.test server_principal_sid:010500000000000515000000f4c6826aa3951380da98d5dd0b120000 database_principal_name:OFFICE\\sys.test target_server_principal_name: target_server_principal_sid: target_database_principal_name: server_instance_name:DB-SICP00001\\DBSICP01 database_name:test schema_name:dbo object_name:message statement:SELECT id, medium, gate_message_id, charge_operation_id, datediff(hour, dt, getdate()) hours_old FROM message WHERE status=5 and dt>dateadd(hour, -30, getdate()) and medium = 'P' ORDER BY id additional_information: user_defined_information: ."},"eventdata":{"data":"audit_schema_version:1 event_time:2019-09-13 06:41:23.7965120 sequence_number:1 action_id:SL   succeeded:true is_column_permission:true session_id:89 server_principal_id:288 database_principal_id:77 target_server_principal_id:0 target_database_principal_id:0 object_id:2046630334 user_defined_event_id:0 class_type:U  permission_bitmask:00000000000000000000000000000001 sequence_group_id:4E88E82C-C3FF-4A6F-9CD0-D92D9FD16359 session_server_principal_name:OFFICE\\sys.test server_principal_name:OFFICE\\sys.test server_principal_sid:010500000000000515000000f4c6826aa3951380da98d5dd0b120000 database_principal_name:OFFICE\\sys.test target_server_principal_name: target_server_principal_sid: target_database_principal_name: server_instance_name:DB-SICP00001\\DBSICP01 database_name:test schema_name:dbo object_name:message statement:SELECT id, medium, gate_message_id, charge_operation_id, datediff(hour, dt, getdate()) hours_old FROM message WHERE status=5 and dt&gt;dateadd(hour, -30, getdate()) and medium = 'P' ORDER BY id additional_information: user_defined_information:"}}},"location":"EventChannel"}

Maybe im missing something or didnt undestand properly how to create rule

Thank you 

[Juan Pablo Saez]

Hi Dimitar,

and welcome to Wazuh community, we will be happy to assist you. Let's take a look at your question:

im trying to send audit logs from mssql to wazuh to get alerts when someone is executing query  
problem is that there is no rules for that matches on message comes from mssql
what i see is that eventID is 33205 and message comes with severity value "severityValue\":\"AUDIT_SUCCESS\" i check that there is a such rule but nothing match

Wazuh will not produce alert for rules with level 0. Also, there are many events with "severityValue":"AUDIT_SUCCESS" so i think a more specific filter "providerName":"MSSQL$DB01" is needed. On the other hand, the event you pasted has "channel":"Application" so your rule has to point to 60003 rule(sink rule for  "channel":"Application" events) and not to 60001 rule(sink rule for "channel":"Application" events)

Therefore, i have made these tunings on your rule:

<rule id="100103" level="6">

    <if_sid>60003</if_sid>

    <field name="win.system.providerName">^MSSQL$DB01$</field>

    <description>MSSQL event</description>

 </rule>

Our binary /var/ossec/bin/ossec-logtest allows you to enter events and see if they trigger rules but for eventchannel events you have to make a small modification to make it work.

I hope it helps. Please, let me know if you need more information on how to create or debug your custom rules. 

Best regards, Juan Pablo Sáez

[Dimitar Penkov]

Hello Juan

Thanks for information

since we have more than one instance i think i can make rule for EventID because all of that security audit comes with 33205 

so my rule if im correct must be 

<rule id="100103" level="6">

    <if_sid>60003</if_sid>

    <field name="win.system.eventid">^33205</field>

    <description>MSSQL event</description>

 </rule>

Regards 

[Juan Pablo Saez]

Hi again Dimitar,

I just tested your modification and it triggers correctly:

**Phase 2: Completed decoding.

       decoder: 'json'

       win.system.providerName: 'MSSQL$DB01'

       win.system.eventID: '33205'

       win.system.level: '0'

       win.system.task: '5'

       win.system.keywords: '0xa0000000000000'

       win.system.systemTime: '2019-09-13T06:41:24.593240800Z'

       win.system.eventRecordID: '3009319'

       win.system.channel: 'Application'

       win.system.computer: 'DB01.domain.prod'

       win.system.severityValue: 'AUDIT_SUCCESS'

       win.system.message: 'Audit event: audit_schema_version:1 event_time:2019-09-13 06:41:23.7965120 sequence_number:1 action_id:SL   succeeded:true is_column_permission:true session_id:89 server_principal_id:288 database_principal_id:77 target_server_principal_id:0 target_database_principal_id:0 object_id:2046630334 user_defined_event_id:0 class_type:U  permission_bitmask:00000000000000000000000000000001 sequence_group_id:4E88E82C-C3FF-4A6F-9CD0-D92D9FD16359 session_server_principal_name:OFFICE\sys.test server_principal_name:OFFICE\sys.test server_principal_sid:010500000000000515000000f4c6826aa3951380da98d5dd0b120000 database_principal_name:OFFICE\sys.test target_server_principal_name: target_server_principal_sid: target_database_principal_name: server_instance_name:DB01\DB01 database_name:test schema_name:dbo object_name:message statement:SELECT id, medium, gate_message_id, charge_operation_id, datediff(hour, dt, getdate()) hours_old FROM message WHERE status=5 and dt>dateadd(hour, -30, getdate()) and medium = 'P' ORDER BY id additional_information: user_defined_information: .'

       win.eventdata.data: 'audit_schema_version:1 event_time:2019-09-13 06:41:23.7965120 sequence_number:1 action_id:SL   succeeded:true is_column_permission:true session_id:89 server_principal_id:288 database_principal_id:77 target_server_principal_id:0 target_database_principal_id:0 object_id:2046630334 user_defined_event_id:0 class_type:U  permission_bitmask:00000000000000000000000000000001 sequence_group_id:4E88E82C-C3FF-4A6F-9CD0-D92D9FD16359 session_server_principal_name:OFFICE\\sys.test server_principal_name:OFFICE\\sys.test server_principal_sid:010500000000000515000000f4c6826aa3951380da98d5dd0b120000 database_principal_name:OFFICE\\sys.test target_server_principal_name: target_server_principal_sid: target_database_principal_name: server_instance_name:DB01\\DB01 database_name:test schema_name:dbo object_name:message statement:SELECT id, medium, gate_message_id, charge_operation_id, datediff(hour, dt, getdate()) hours_old FROM message WHERE status=5 and dt&gt;dateadd(hour, -30, getdate()) and medium = 'P' ORDER BY id additional_information: user_defined_information:'

**Phase 3: Completed filtering (rules).

       Rule id: '100103'

       Level: '6'

       Description: 'MSSQL event'

**Alert to be generated.

Count on us for any questions you may have. Greetings, 

Juan Pablo Sáez

[Dimitar Penkov]

well that was strange because is not working on mine 

i put rule in /var/ossec/etc/rules/local_rules.xml

and i restart wazuh-manager

**Phase 2: Completed decoding.

       decoder: 'json'

and thats it :)

Regards

On Friday, September 13, 2019 at 10:27:38 AM UTC+3, Dimitar Penkov wrote:

[Juan Pablo Saez]

Hi again Dimitar,

In my first message i wrote 

Our binary /var/ossec/bin/ossec-logtest allows you to enter events and see if they trigger rules but for eventchannel events you have to make a small modification to make it work.

So, when testing eventchannel events in /var/ossec/bin/ossec-logtest, as the one that you are working now, you should make the further modification on your ruleset:

On  /var/ossec/ruleset/rules/0575-win-base_rules.xml please, find the first rule with the 60000 ID

  <rule id="60000" level="0">

    <category>ossec</category>

    <decoded_as>windows_eventchannel</decoded_as>

    <field name="win.system.providerName">\.+</field>

    <options>no_full_log</options>

    <description>Group of windows rules</description>

  </rule>

Remove the <category> field and modify the <decoded_as> field as in the further example(from windows_eventchannel to json):

  <rule id="60000" level="0">

    <decoded_as>json</decoded_as>

    <field name="win.system.providerName">\.+</field>

    <options>no_full_log</options>

    <description>Group of windows rules</description>

  </rule>

After this modification, you should be able to see how your example event triggers your custom rule:

Your example event:

{"win":{"system":{"providerName":"MSSQL$DB01","eventID":"33205","level":"0","task":"5","keywords":"0xa0000000000000","systemTime":"2019-09-13T06:41:24.593240800Z","eventRecordID":"3009319","channel":"Application","computer":"DB01.domain.prod","severityValue":"AUDIT_SUCCESS","message":"Audit event: audit_schema_version:1 event_time:2019-09-13 06:41:23.7965120 sequence_number:1 action_id:SL   succeeded:true is_column_permission:true session_id:89 server_principal_id:288 database_principal_id:77 target_server_principal_id:0 target_database_principal_id:0 object_id:2046630334 user_defined_event_id:0 class_type:U  permission_bitmask:00000000000000000000000000000001 sequence_group_id:4E88E82C-C3FF-4A6F-9CD0-D92D9FD16359 session_server_principal_name:OFFICE\\sys.test server_principal_name:OFFICE\\sys.test server_principal_sid:010500000000000515000000f4c6826aa3951380da98d5dd0b120000 database_principal_name:OFFICE\\sys.test target_server_principal_name: target_server_principal_sid: target_database_principal_name: server_instance_name:DB01\\DB01 database_name:test schema_name:dbo object_name:message statement:SELECT id, medium, gate_message_id, charge_operation_id, datediff(hour, dt, getdate()) hours_old FROM message WHERE status=5 and dt>dateadd(hour, -30, getdate()) and medium = 'P' ORDER BY id additional_information: user_defined_information: ."},"eventdata":{"data":"audit_schema_version:1 event_time:2019-09-13 06:41:23.7965120 sequence_number:1 action_id:SL   succeeded:true is_column_permission:true session_id:89 server_principal_id:288 database_principal_id:77 target_server_principal_id:0 target_database_principal_id:0 object_id:2046630334 user_defined_event_id:0 class_type:U  permission_bitmask:00000000000000000000000000000001 sequence_group_id:4E88E82C-C3FF-4A6F-9CD0-D92D9FD16359 session_server_principal_name:OFFICE\\\\sys.test server_principal_name:OFFICE\\\\sys.test server_principal_sid:010500000000000515000000f4c6826aa3951380da98d5dd0b120000 database_principal_name:OFFICE\\\\sys.test target_server_principal_name: target_server_principal_sid: target_database_principal_name: server_instance_name:DB01\\\\DB01 database_name:test schema_name:dbo object_name:message statement:SELECT id, medium, gate_message_id, charge_operation_id, datediff(hour, dt, getdate()) hours_old FROM message WHERE status=5 and dt&gt;dateadd(hour, -30, getdate()) and medium = 'P' ORDER BY id additional_information: user_defined_information:"}}}

The output after this modification:

**Phase 2: Completed decoding.

       decoder: 'json'

       win.system.providerName: 'MSSQL$DB01'

       win.system.eventID: '33205'

       win.system.level: '0'

       win.system.task: '5'

       win.system.keywords: '0xa0000000000000'

       win.system.systemTime: '2019-09-13T06:41:24.593240800Z'

       win.system.eventRecordID: '3009319'

       win.system.channel: 'Application'

       win.system.computer: 'DB01.domain.prod'

       win.system.severityValue: 'AUDIT_SUCCESS'

       win.system.message: 'Audit event: audit_schema_version:1 event_time:2019-09-13 06:41:23.7965120 sequence_number:1 action_id:SL   succeeded:true is_column_permission:true session_id:89 server_principal_id:288 database_principal_id:77 target_server_principal_id:0 target_database_principal_id:0 object_id:2046630334 user_defined_event_id:0 class_type:U  permission_bitmask:00000000000000000000000000000001 sequence_group_id:4E88E82C-C3FF-4A6F-9CD0-D92D9FD16359 session_server_principal_name:OFFICE\sys.test server_principal_name:OFFICE\sys.test server_principal_sid:010500000000000515000000f4c6826aa3951380da98d5dd0b120000 database_principal_name:OFFICE\sys.test target_server_principal_name: target_server_principal_sid: target_database_principal_name: server_instance_name:DB01\DB01 database_name:test schema_name:dbo object_name:message statement:SELECT id, medium, gate_message_id, charge_operation_id, datediff(hour, dt, getdate()) hours_old FROM message WHERE status=5 and dt>dateadd(hour, -30, getdate()) and medium = 'P' ORDER BY id additional_information: user_defined_information: .'

       win.eventdata.data: 'audit_schema_version:1 event_time:2019-09-13 06:41:23.7965120 sequence_number:1 action_id:SL   succeeded:true is_column_permission:true session_id:89 server_principal_id:288 database_principal_id:77 target_server_principal_id:0 target_database_principal_id:0 object_id:2046630334 user_defined_event_id:0 class_type:U  permission_bitmask:00000000000000000000000000000001 sequence_group_id:4E88E82C-C3FF-4A6F-9CD0-D92D9FD16359 session_server_principal_name:OFFICE\\sys.test server_principal_name:OFFICE\\sys.test server_principal_sid:010500000000000515000000f4c6826aa3951380da98d5dd0b120000 database_principal_name:OFFICE\\sys.test target_server_principal_name: target_server_principal_sid: target_database_principal_name: server_instance_name:DB01\\DB01 database_name:test schema_name:dbo object_name:message statement:SELECT id, medium, gate_message_id, charge_operation_id, datediff(hour, dt, getdate()) hours_old FROM message WHERE status=5 and dt&gt;dateadd(hour, -30, getdate()) and medium = 'P' ORDER BY id additional_information: user_defined_information:'

**Phase 3: Completed filtering (rules).

       Rule id: '100103'

       Level: '6'

       Description: 'MSSQL event'

**Alert to be generated.

Please remember to return the 60000 rule to its previous configuration after completing the testing process of your custom rules. I want to emphasize that this is only necessary for eventchannel events, the rest of events can be tested without making any modification. As soon as our workflow allows it we will fix the testing binary so that this modification is not necessary.


PD: note that the log that I use as input on /var/ossec/bin/ossec-logtest, is the "full_log" portion of the event you pasted on your first message. This portion is the one that Wazuh manager receives from the agent.

The one you pasted is the enriched version after the manager's processing.

I hope it helps. Greetings, Juan Pablo Sáez

[Dimitar Penkov]

Hello Juan

after i edit rule 6000 and paste json is working

this is debug of ossec-logtest

 

   Trying rule: 60000 - Group of windows rules

      *Rule 60000 matched.

      *Trying child rules.

   Trying rule: 60001 - Group of Windows rules for the Security channel

   Trying rule: 60002 - Group of Windows rules for the System channel

   Trying rule: 60003 - Group of Windows rules for the Application channel

      *Rule 60003 matched.

      *Trying child rules.

   Trying rule: 60006 - Group of Windows rules for the McAfee channel

   Trying rule: 60600 - Windows Application informational event

   Trying rule: 60601 - Windows Application warning event

   Trying rule: 60602 - Windows Application error event

   Trying rule: 100099 - MSSQL event

      *Rule 100099 matched.

**Phase 3: Completed filtering (rules).

      Rule id: '100099'

      Level: '6'

      Description: 'MSSQL event'

**Alert to be generated.

question now is how to make it work permanently 

Regards 

[Juan Pablo Saez]

Hi again Dimitar, 

First of all, sorry for the late reply.

question now is how to make it work permanently

• About the 60000 rule on /var/ossec/ruleset/rules/0575-win-base_rules.xml:

•  (TESTING)When using ossec-logtest with Eventchannel example events,  you should use the further version of 60000 rule:

•   <rule id="60000" level="0">

     <decoded_as>json</decoded_as>

     <field name="win.system.providerName">\.+</field>

     <options>no_full_log</options>

     <description>Group of windows rules</description>

   </rule>

• (AVERAGE USE)During normal use of the Wazuh manager, you should use the version below of 60000 rule:

•   <rule id="60000" level="0">

    <category>ossec</category>

    <decoded_as>windows_eventchannel</decoded_as>

    <field name="win.system.providerName">\.+</field>

    <options>no_full_log</options>

    <description>Group of windows rules</description>

  </rule>

• There is no fix to make this work with testing purposes and normal use at the same time.  It only happens with Eventchannel events so you can use ossec-logtest without modifying any rule with all the other kinds of events. We are fixing it when our workflow allows it.

• On the other hand, you should place your custom rule 100099 and the new ones you design on /var/ossec/etc/rules/local_rules.xml as they will survive updates there.

Please, let me know if it helps. Best regards, 

Juan Pablo Sáez

Best regards, Juan Pablo Sáez

[chachab]

Hello, Juan and Dimitar,

Did this work fine now? I come into the same problem like this.. 

I will really appreciate your help on this. Dimitar, do you have audit logs in your Wazuh dashboard?