Parsing additional data in a windows event log

Elastic Stack Logstash
1

I am using a customized SQL audit specification to forward MSSQL logs to the windows security event log. These events come in with the event id of 33205. I would like to take the message field from the logs and parse that field out. These logs are collected with winlogbeat on the original host and sent through Logstash. Here is an example of the raw log, using the file output in Logstash.

{"computer_name":"CH00SPS1.eu.mt.mtnet","event_id":33205,"beat":{"hostname":"CH00SPS1","name":"CH00SPS1","version":"6.2.4"},"@version":"1","record_number":"68676307","keywords":["Audit Success","Classic"],"@timestamp":"2018-06-07T17:12:17.534Z","event_test":"Audit event: event_time:2018-06-07 17:12:17.3200000\nsequence_number:1\naction_id:LGIS\nsucceeded:true\nis_column_permission:false\nsession_id:108\nserver_principal_id:313\ndatabase_principal_id:0\ntarget_server_principal_id:0\ntarget_database_principal_id:0\nobject_id:0\nuser_defined_event_id:0\nclass_type:LX\npermission_bitmask:00000000000000000000000000000000\nsession_server_principal_name:\nserver_principal_name:PS1_DB_SAPMonitor\nserver_principal_sid:9b02dc3510c1ed488ce98ef731273b5f\ndatabase_principal_name:\ntarget_server_principal_name:\ntarget_server_principal_sid:\ntarget_database_principal_name:\nserver_instance_name:CH00SPS1\ndatabase_name:\nschema_name:\nobject_name:\nstatement:-- network protocol: TCP/IP\nset quoted_identifier on\nset arithabort off\nset numeric_roundabort off\nset ansi_warnings on\nset ansi_padding on\nset ansi_nulls on\nset concat_null_yields_null on\nset cursor_close_on_commit off\nset implicit_transactions off\nset language us_english\nset dateformat mdy\nset datefirst 7\nset transaction isolation level read committed\n\nadditional_information:<action_info xmlns=\"http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data\"><pooled_connection>0</pooled_connection><client_options>0x28000020</client_options><client_options1>0x0001f438</client_options1><connect_options>0x00000001</connect_options><packet_data_size>4096</packet_data_size><address>172.21.7.69</address><is_dac>0</is_dac></action_info>\nuser_defined_information:\n.","event_data":{"param1":"event_time:2018-06-07 17:12:17.3200000\nsequence_number:1\naction_id:LGIS\nsucceeded:true\nis_column_permission:false\nsession_id:108\nserver_principal_id:313\ndatabase_principal_id:0\ntarget_server_principal_id:0\ntarget_database_principal_id:0\nobject_id:0\nuser_defined_event_id:0\nclass_type:LX\npermission_bitmask:00000000000000000000000000000000\nsession_server_principal_name:\nserver_principal_name:PS1_DB_SAPMonitor\nserver_principal_sid:9b02dc3510c1ed488ce98ef731273b5f\ndatabase_principal_name:\ntarget_server_principal_name:\ntarget_server_principal_sid:\ntarget_database_principal_name:\nserver_instance_name:CH00SPS1\ndatabase_name:\nschema_name:\nobject_name:\nstatement:-- network protocol: TCP/IP\nset quoted_identifier on\nset arithabort off\nset numeric_roundabort off\nset ansi_warnings on\nset ansi_padding on\nset ansi_nulls on\nset concat_null_yields_null on\nset cursor_close_on_commit off\nset implicit_transactions off\nset language us_english\nset dateformat mdy\nset datefirst 7\nset transaction isolation level read committed\n\nadditional_information:<action_info xmlns=\"http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data\"><pooled_connection>0</pooled_connection><client_options>0x28000020</client_options><client_options1>0x0001f438</client_options1><connect_options>0x00000001</connect_options><packet_data_size>4096</packet_data_size><address>172.21.7.69</address><is_dac>0</is_dac></action_info>\nuser_defined_information:"},"log_name":"Security","level":"Information","type":"wineventlog","source_name":"MSSQLSERVER$AUDIT","tags":["winsec","my00slr01","beats_input_codec_plain_applied","_grokparsefailure"],"host":"CH00SPS1","user":{"type":"User","identifier":"S-1-5-21-1085031214-1425521274-839522115-55157","domain":"EU","name":"SQLServicePS1"},"message":"Audit event: event_time:2018-06-07 17:12:17.3200000\nsequence_number:1\naction_id:LGIS\nsucceeded:true\nis_column_permission:false\nsession_id:108\nserver_principal_id:313\ndatabase_principal_id:0\ntarget_server_principal_id:0\ntarget_database_principal_id:0\nobject_id:0\nuser_defined_event_id:0\nclass_type:LX\npermission_bitmask:00000000000000000000000000000000\nsession_server_principal_name:\nserver_principal_name:PS1_DB_SAPMonitor\nserver_principal_sid:9b02dc3510c1ed488ce98ef731273b5f\ndatabase_principal_name:\ntarget_server_principal_name:\ntarget_server_principal_sid:\ntarget_database_principal_name:\nserver_instance_name:CH00SPS1\ndatabase_name:\nschema_name:\nobject_name:\nstatement:-- network protocol: TCP/IP\nset quoted_identifier on\nset arithabort off\nset numeric_roundabort off\nset ansi_warnings on\nset ansi_padding on\nset ansi_nulls on\nset concat_null_yields_null on\nset cursor_close_on_commit off\nset implicit_transactions off\nset language us_english\nset dateformat mdy\nset datefirst 7\nset transaction isolation level read committed\n\nadditional_information:<action_info xmlns=\"http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data\"><pooled_connection>0</pooled_connection><client_options>0x28000020</client_options><client_options1>0x0001f438</client_options1><connect_options>0x00000001</connect_options><packet_data_size>4096</packet_data_size><address>172.21.7.69</address><is_dac>0</is_dac></action_info>\nuser_defined_information:\n."}

I am using the following filter in Logstash to attempt to parse the "message" field, which is pasted in the next message below.. I hit the character limit.

2

Continued.

"message":"Audit event: event_time:2018-06-07 17:12:17.3200000\nsequence_number:1\naction_id:LGIS\nsucceeded:true\nis_column_permission:false\nsession_id:108\nserver_principal_id:313\ndatabase_principal_id:0\ntarget_server_principal_id:0\ntarget_database_principal_id:0\nobject_id:0\nuser_defined_event_id:0\nclass_type:LX\npermission_bitmask:00000000000000000000000000000000\nsession_server_principal_name:\nserver_principal_name:PS1_DB_SAPMonitor\nserver_principal_sid:9b02dc3510c1ed488ce98ef731273b5f\ndatabase_principal_name:\ntarget_server_principal_name:\ntarget_server_principal_sid:\ntarget_database_principal_name:\nserver_instance_name:CH00SPS1\ndatabase_name:\nschema_name:\nobject_name:\nstatement:-- network protocol: TCP/IP\nset quoted_identifier on\nset arithabort off\nset numeric_roundabort off\nset ansi_warnings on\nset ansi_padding on\nset ansi_nulls on\nset concat_null_yields_null on\nset cursor_close_on_commit off\nset implicit_transactions off\nset language us_english\nset dateformat mdy\nset datefirst 7\nset transaction isolation level read committed\n\nadditional_information:<action_info xmlns=\"http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data\"><pooled_connection>0</pooled_connection><client_options>0x28000020</client_options><client_options1>0x0001f438</client_options1><connect_options>0x00000001</connect_options><packet_data_size>4096</packet_data_size><address>172.21.7.69</address><is_dac>0</is_dac></action_info>\nuser_defined_information:\n."}

My filter configuration in Logstash is here.

if "winsec" in [tags] {
    if "MSSQLSERVER$AUDIT" in [source_name] {
    grok {
        break_on_match => false
        keep_empty_captures => false
        match => {"message" => "Audit event: event_time:%{DATA:local_event_time}\\nsequence_number:%{NUMBER:sequence_number}\\naction_id:%{NOTSPACE:action_id}\\nsucceeded:%{WORD:succeeded}\\nis_column_permission:%{WORD:is_column_permission}\\nsession_id:%{NUMBER:session_id}\\nserver_principal_id:%{NUMBER:server_principal_id}\\ndatabase_principal_id:%{NUMBER:database_principle_id}\\ntarget_server_principal_id:%{NUMBER:target_server_principal_id}\\ntarget_database_principal_id:%{NUMBER:target_database_principal_id}\\nobject_id:%{NUMBER:object_id}\\nuser_defined_event_id:%{NUMBER:user_defined_event_id}\\nclass_type:%{DATA:class_type}\\npermission_bitmask:%{DATA:permission_bitmask}\\nsession_server_principal_name:%{DATA:session_server_principle_name}\\nserver_principal_name:%{DATA:server_principal_name}\\nserver_principal_sid:%{DATA:server_principal_sid}\\ndatabase_principal_name:%{DATA:database_principal_name}\\ntarget_server_principal_name:%{DATA:target_server_principal_name}\\ntarget_server_principal_sid:%{DATA:target_server_principal}\\ntarget_database_principal_name:%{DATA:target_database_principal_name}\\nserver_instance_name:%{DATA:server_instance_name}\\ndatabase_name:%{DATA:database_name}\\nschema_name:%{DATA:schema_name}\\nobject_name:%{DATA:object_name}\\nstatement:%{GREEDYDATA:statement}\\nadditional_information:%{DATA:additional_information}\\nuser_defined_information:%{DATA:user_defined_information}"}
       remove_field => [ "message" ]
    }
}
    }

While this code works in grok debugger it does not work in Logstash and I just end up with a _grokparsefailure in Elasticsearch. Not sure what I am doing wrong, it is almost like Logstash cannot access this field?

Thanks for looking!

3

One additional tidbit. In Logstash I am able to duplicate the message field into a new field but unable to parse that new field either.

4

Solved. used mutate with gsub to change the \n to a different delimiter and the message field parsed fine. Hope this helps someone.

5

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.