Help Decoder for MS SQL eventchannel - Application

[chachab]

Hello Team,

Currently, I managed to integrate MSSQL to my Wazuh, and I can see events in my Wazuh dashboard, as attached screenshot, but I would like to have fields in my event details like event_time, client_ip, server_principal_name, database_principal_name, database_name, schema_name, object_nam,e and statement

The sample log I captured by tail -f /var/ossec/logs/archieve/archieve.json

{"timestamp":"2025-06-04T08:32:41.888+0000","rule":{"level":10,"description":"MS SQL UPDATE operation.","id":"160012","firedtimes":620,"mail":false,"groups":["windows","mssqlpci_dss_10.2.1"]},"agent":{"id":"001","name":"HQSQLTSDB01","ip":"171.10.1.17"},"manager":{"name":"wazuhdr"},"id":"1749025961.3194387817","full_log":"{\"win\":{\"system\":{\"providerName\":\"MSSQLSERVER\",\"eventID\":\"33205\",\"level\":\"0\",\"task\":\"5\",\"keywords\":\"0xa0000000000000\",\"systemTime\":\"2025-06-04T08:32:41.880580300Z\",\"eventRecordID\":\"30073398\",\"channel\":\"Application\",\"computer\":\"HQSQLTSDB01.domain.com\",\"severityValue\":\"AUDIT_SUCCESS\",\"message\":\"\\\"Audit event: audit_schema_version:1\\nevent_time:2025-06-04 08:32:41.5211949\\nsequence_number:1\\naction_id:SL  \\nsucceeded:true\\nis_column_permission:true\\nsession_id:70\\nserver_principal_id:286\\ndatabase_principal_id:1\\ntarget_server_principal_id:0\\ntarget_database_principal_id:0\\nobject_id:1577211748\\nuser_defined_event_id:0\\ntabsaction_id:631357873\\nclass_type:V \\nduration_milliseconds:0\\nresponse_rows:0\\naffected_rows:0\\nclient_ip:local machine\\npermission_bitmask:00000000000000000000000000000001\\nsequence_group_id:3B9FE7BD-C102-4EDE-9F7A-9D94027149FF\\nsession_server_principal_name:domain\\\\user.usery\\nserver_principal_name:domain\\\\user.usery\\nserver_principal_sid:0105000000000005150000003fe885e2b7a0d6465107ccd3dab80000\\ndatabase_principal_name:dbo\\ntarget_server_principal_name:\\ntarget_server_principal_sid:\\ntarget_database_principal_name:\\nserver_instance_name:HQSQLTSDB01\\ndatabase_name:E10TEST\\nschema_name:dbo\\nobject_name:Parttab\\nstatement:update Parttab\\r\\nset tabQty =222222\\r\\nwhere tabNum = 35\\nadditional_information:\\nuser_defined_information:\\napplication_name:Microsoft SQL Server Management Studio - Query\\n.\\\"\"},\"eventdata\":{\"data\":\"audit_schema_version:1 event_time:2025-06-04 08:32:41.5211949 sequence_number:1 action_id:SL   succeeded:true is_column_permission:true session_id:70 server_principal_id:286 database_principal_id:1 target_server_principal_id:0 target_database_principal_id:0 object_id:1577211748 user_defined_event_id:0 tabsaction_id:631357873 class_type:V  duration_milliseconds:0 response_rows:0 affected_rows:0 client_ip:local machine permission_bitmask:00000000000000000000000000000001 sequence_group_id:3B9FE7BD-C102-4EDE-9F7A-9D94027149FF session_server_principal_name:domain\\\\\\\\user.usery server_principal_name:domain\\\\\\\\user.usery server_principal_sid:0105000000000005150000003fe885e2b7a0d6465107ccd3dab80000 database_principal_name:dbo target_server_principal_name: target_server_principal_sid: target_database_principal_name: server_instance_name:HQSQLTSDB01 database_name:E10TEST schema_name:dbo object_name:Parttab statement:update Parttab  set tabQty =222222  where tabNum = 35 additional_information: user_defined_information: application_name:Microsoft SQL Server Management Studio - Query\"}}}","decoder":{"name":"windows_eventchannel"},"data":{"win":{"system":{"providerName":"MSSQLSERVER","eventID":"33205","level":"0","task":"5","keywords":"0xa0000000000000","systemTime":"2025-06-04T08:32:41.880580300Z","eventRecordID":"30073398","channel":"Application","computer":"HQSQLTSDB01.domain","severityValue":"AUDIT_SUCCESS","message":"\"Audit event: audit_schema_version:1\nevent_time:2025-06-04 08:32:41.5211949\nsequence_number:1\naction_id:SL  \nsucceeded:true\nis_column_permission:true\nsession_id:70\nserver_principal_id:286\ndatabase_principal_id:1\ntarget_server_principal_id:0\ntarget_database_principal_id:0\nobject_id:1577211748\nuser_defined_event_id:0\ntabsaction_id:631357873\nclass_type:V \nduration_milliseconds:0\nresponse_rows:0\naffected_rows:0\nclient_ip:local machine\npermission_bitmask:00000000000000000000000000000001\nsequence_group_id:3B9FE7BD-C102-4EDE-9F7A-9D94027149FF\nsession_server_principal_name:domain\\user.usery\nserver_principal_name:domain\\user.usery\nserver_principal_sid:0105000000000005150000003fe885e2b7a0d6465107ccd3dab80000\ndatabase_principal_name:dbo\ntarget_server_principal_name:\ntarget_server_principal_sid:\ntarget_database_principal_name:\nserver_instance_name:HQSQLTSDB01\ndatabase_name:E10TEST\nschema_name:dbo\nobject_name:Parttab\nstatement:update Parttab\r\nset tabQty =222222\r\nwhere tabNum = 35\nadditional_information:\nuser_defined_information:\napplication_name:Microsoft SQL Server Management Studio - Query\n.\""},"eventdata":{"data":"audit_schema_version:1 event_time:2025-06-04 08:32:41.5211949 sequence_number:1 action_id:SL   succeeded:true is_column_permission:true session_id:70 server_principal_id:286 database_principal_id:1 target_server_principal_id:0 target_database_principal_id:0 object_id:1577211748 user_defined_event_id:0 tabsaction_id:631357873 class_type:V  duration_milliseconds:0 response_rows:0 affected_rows:0 client_ip:local machine permission_bitmask:00000000000000000000000000000001 sequence_group_id:3B9FE7BD-C102-4EDE-9F7A-9D94027149FF session_server_principal_name:domain\\\\user.usery server_principal_name:domain\\\\user.usery server_principal_sid:0105000000000005150000003fe885e2b7a0d6465107ccd3dab80000 database_principal_name:dbo target_server_principal_name: target_server_principal_sid: target_database_principal_name: server_instance_name:HQSQLTSDB01 database_name:E10TEST schema_name:dbo object_name:Parttab statement:update Parttab  set tabQty =222222  where tabNum = 35 additional_information: user_defined_information: application_name:Microsoft SQL Server Management Studio - Query"}}},"location":"EventChannel"}


I just need to have columns for event_time, client_ip, server_principal_name, database_principal_name, database_name, schema_name, so that I can create custom dashboards.

This is my rule

  <rule id="160001" level="3">
    <if_sid>61070</if_sid>
    <field name="win.system.providerName">^MSSQLSERVER$</field>
    <description>Unclassisised MS SQL server audit event.</description>
    <group>pci_dss_10.2.1,</group>
  </rule>

  <rule id="160010" level="3">
    <if_sid>160001</if_sid>
    <field name="win.system.providerName">^MSSQLSERVER$</field>
    <field name="win.system.eventID">33205</field>
    <description>MS SQL unclassisised operation</description>
    <group>pci_dss_10.2.1,</group>
  </rule>

  <rule id="160012" level="10">
    <if_sid>160010</if_sid>
    <field name="win.system.providerName">^MSSQLSERVER$</field>
    <field name="win.system.eventID">33205</field>
    <match type="pcre2">UPDATE|update</match>
    <description>MS SQL UPDATE operation.</description>
    <group>pci_dss_10.2.1,</group>
  </rule>

  <rule id="160013" level="12">
    <if_sid>160010</if_sid>
    <field name="win.system.providerName">^MSSQLSERVER$</field>
    <field name="win.system.eventID">33205</field>
    <match type="pcre2">DELETE|delete|Delete</match>
    <description>MS SQL DELETE operation.</description>
    <!--<description>MS SQL DELETE operation on $(database_name).$(schema_name).$(object_name) by $(server_principal_name)</description>-->
    <group>pci_dss_10.2.1,</group>
  </rule>

  <rule id="160014" level="4">
    <if_sid>160010</if_sid>
    <field name="win.system.providerName">^MSSQLSERVER$</field>
    <field name="win.system.eventID">33205</field>
    <field name="win.system.message" type="pcre2">(?s)^.*database_name:E10TEST.*$</field>
    <field name="win.system.message" type="pcre2">(?is)^.*statement:DROP.*$</field>
    <description>MS SQL DROP operation.</description>
    <group>pci_dss_10.2.1,</group>
  </rule>

[Kevin Ledesma]

Hello! 

Well you are on the right path, for your requirement, what you want is probably to generate a custom decoder, you can check the official documentation it will give you all you need to know in order to create your decoder:

• User manual - Ruleset - Custom decoder
  
• Blogpost - Creating decoders and rules
  

Also, note that you can test your decoders and rules using the wazuh logtest tool (to test windows events follow the indications from this other thread)

If you need some more assistance on the decoder/rules, just share your current decoder and the full log you are using to test it, I tried to extract the actual event from the JSON you shared previously, it is the following:

{"win":{"system":{"providerName":"MSSQLSERVER","eventID":"33205","level":"0","task":"5","keywords":"0xa0000000000000","systemTime":"2025-06-04T08:32:41.880580300Z","eventRecordID":"30073398","channel":"Application","computer":"HQSQLTSDB01.domain.com","severityValue":"AUDIT_SUCCESS","message":"\"Audit event: audit_schema_version:1\nevent_time:2025-06-04 08:32:41.5211949\nsequence_number:1\naction_id:SL  \nsucceeded:true\nis_column_permission:true\nsession_id:70\nserver_principal_id:286\ndatabase_principal_id:1\ntarget_server_principal_id:0\ntarget_database_principal_id:0\nobject_id:1577211748\nuser_defined_event_id:0\ntabsaction_id:631357873\nclass_type:V \nduration_milliseconds:0\nresponse_rows:0\naffected_rows:0\nclient_ip:local machine\npermission_bitmask:00000000000000000000000000000001\nsequence_group_id:3B9FE7BD-C102-4EDE-9F7A-9D94027149FF\nsession_server_principal_name:domain\\user.usery\nserver_principal_name:domain\\user.usery\nserver_principal_sid:0105000000000005150000003fe885e2b7a0d6465107ccd3dab80000\ndatabase_principal_name:dbo\ntarget_server_principal_name:\ntarget_server_principal_sid:\ntarget_database_principal_name:\nserver_instance_name:HQSQLTSDB01\ndatabase_name:E10TEST\nschema_name:dbo\nobject_name:Parttab\nstatement:update Parttab\r\nset tabQty =222222\r\nwhere tabNum = 35\nadditional_information:\nuser_defined_information:\napplication_name:Microsoft SQL Server Management Studio - Query\n.\""},"eventdata":{"data":"audit_schema_version:1 event_time:2025-06-04 08:32:41.5211949 sequence_number:1 action_id:SL   succeeded:true is_column_permission:true session_id:70 server_principal_id:286 database_principal_id:1 target_server_principal_id:0 target_database_principal_id:0 object_id:1577211748 user_defined_event_id:0 tabsaction_id:631357873 class_type:V  duration_milliseconds:0 response_rows:0 affected_rows:0 client_ip:local machine permission_bitmask:00000000000000000000000000000001 sequence_group_id:3B9FE7BD-C102-4EDE-9F7A-9D94027149FF session_server_principal_name:domain\\\\user.usery server_principal_name:domain\\\\user.usery server_principal_sid:0105000000000005150000003fe885e2b7a0d6465107ccd3dab80000 database_principal_name:dbo target_server_principal_name: target_server_principal_sid: target_database_principal_name: server_instance_name:HQSQLTSDB01 database_name:E10TEST schema_name:dbo object_name:Parttab statement:update Parttab  set tabQty =222222  where tabNum = 35 additional_information: user_defined_information: application_name:Microsoft SQL Server Management Studio - Query"}}}

Another thing, remember that your new decoder needs to be a child of windows_eventchannel, but for testing purposes you may want it to inherit from json.

[chachab]

Hello Kevin,

Is that possible to just show me the way? Just a few fields, because I tried on my end with no success. 

Thank you!

[chachab]

the below is the sample log

 <rule id="61070" level="0">
    <if_sid>60003</if_sid>
    <field name="win.system.severityValue">^AUDIT_SUCCESS$</field>
    <options>no_full_log</options>
    <description>Windows application audit success event.</description>
    <group>gpg13_4.12,</group>
  </rule>

  {"win":{"system":{"providerName":"MSSQL$SQLEXPRESS","eventID":"33205","level":"0","task":"5","keywords":"0xa0000000000000","systemTime":"2025-06-05T02:57:29.712618900Z","eventRecordID":"15295","channel":"Application","computer":"domainvms01.domain.com","severityValue":"AUDIT_SUCCESS","message":"\"Audit event: audit_schema_version:1\nevent_time:2025-06-05 02:57:29.3688595\nsequence_number:1\naction_id:UP  \nsucceeded:true\nis_column_permission:true\nsession_id:74\nserver_principal_id:266\ndatabase_principal_id:5\ntarget_server_principal_id:0\ntarget_database_principal_id:0\nobject_id:1349579846\nuser_defined_event_id:0\ntransaction_id:13465043\nclass_type:U \nduration_milliseconds:0\nresponse_rows:0\naffected_rows:0\nclient_tls_version:771\ndatabase_transaction_id:85448\nledger_start_sequence_number:0\nclient_ip:local machine\npermission_bitmask:00000000000000000000000000000002\nsequence_group_id:02AFA37F-706B-42C2-A50C-D31EBD16C496\nsession_server_principal_name:chachab\nserver_principal_name:chachab\nserver_principal_sid:24c2756017c49546b68946a4461a1049\ndatabase_principal_name:chachab\ntarget_server_principal_name:\ntarget_server_principal_sid:\ntarget_database_principal_name:\nserver_instance_name:domainvms01\\SQLEXPRESS\ndatabase_name:domain_VMSDB\nschema_name:dbo\nobject_name:tbl_user\nstatement:update dbo.tbl_user\r\nSET EmailAddress = 'emai...@gmail.com'\r\nWHERE UserId = 9\nadditional_information:\nuser_defined_information:\napplication_name:Microsoft SQL Server Management Studio - Query\nconnection_id:C3B56A20-DED6-4D20-AFA1-F06DE4984268\ndata_sensitivity_information:\nhost_name:domainVMS01\nsession_context:\nclient_tls_version_name:1.2\nexternal_policy_permissions_checked:\n.\""},"eventdata":{"data":"audit_schema_version:1 event_time:2025-06-05 02:57:29.3688595 sequence_number:1 action_id:UP   succeeded:true is_column_permission:true session_id:74 server_principal_id:266 database_principal_id:5 target_server_principal_id:0 target_database_principal_id:0 object_id:1349579846 user_defined_event_id:0 transaction_id:13465043 class_type:U  duration_milliseconds:0 response_rows:0 affected_rows:0 client_tls_version:771 database_transaction_id:85448 ledger_start_sequence_number:0 client_ip:local machine permission_bitmask:00000000000000000000000000000002 sequence_group_id:02AFA37F-706B-42C2-A50C-D31EBD16C496 session_server_principal_name:chachab server_principal_name:chachab server_principal_sid:24c2756017c49546b68946a4461a1049 database_principal_name:chachab target_server_principal_name: target_server_principal_sid: target_database_principal_name: server_instance_name:domainvms01\\\\SQLEXPRESS database_name:domain_VMSDB schema_name:dbo object_name:tbl_user statement:update dbo.tbl_user  SET EmailAddress = 'emai...@gmail.com'  WHERE UserId = 9 additional_information: user_defined_information: application_name:Microsoft SQL Server Management Studio - Query connection_id:C3B56A20-DED6-4D20-AFA1-F06DE4984268 data_sensitivity_information: host_name:domainVMS01 session_context: client_tls_version_name:1.2 external_policy_permissions_checked:"}}}

[chachab]

This is my decoder I have right now

<decoder name="mssql_33205_audit">
        <parent>json</parent>
  <prematch>"providerName":"MSSQLSERVER"</prematch>
  <regex type="pcre2">message.*?event_time:(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d+).*?client_ip:(.*?)(?:\\n|\s).*?server_principal_name:(.*?)(?:\\n|\s).*?database_principal_name:(.*?)(?:\\n|\s).*?database_name:(.*?)(?:\\n|\s).*?schema_name:(.*?)(?:\\n|\s).*?object_name:(.*?)(?:\\n|\s).*?statement:\s*(?i)(delete|update)</regex>
  <order>event_time, client_ip, server_principal_name, database_principal_name, database_name, schema_name, object_name, statement</order>

</decoder>


When I run a test, I get the details, but the rule is not triggered. 

This is my rule

<group name="mssql_audit">
  <rule id="100202" level="10">
          <decoded_as>mssql_33205_audit</decoded_as>
          <description>MS SQL database activity detected: $(statement) operation executed by user $(server_principal_name)</description>
  </rule>

[chachab]

Hello,

I managed to do it

Thank you!

[Singh Satish]

Hi,

Can you please share what exact you did to resolve above issue.