Hello,
I'm using the following search string to monitor SQL Server DB Tables that are being audited by SQL Server Audit logs and then pushed to the Application Event Logs.
eventtype="wineventlog_application" sourcetype="*" TaskCategory="*" SourceName="MSSQLSERVER" EventCode="33205" | eval EventCodeDescription=if(isnull(EventCodeDescription), mvindex(split(Message, "."), 0), EventCodeDescription)| rex field=_raw "statement:(?<statement>.*)\s" | rex field=_raw "database_name:(?<database_name>.*)\s" | rex field=_raw "server_instance_name:(?<server_instance_name>.*)\s" | rex field=_raw "server_principal_name:(?<server_principal_name>.*)\s" | table _time, EventCode, EventCodeDescription, server_instance_name, server_principal_name, database_name, statement | rename server_instance_name as "Server Instance", server_principal_name as "Account Used", database_name as "Database", statement as "Table Modified"
This creates the following table:
I'm using the following to manually create fields (In Bold) in my search for my tables from the sample event log below.:
| rex field=_raw "statement:(?.*)\s"
| rex field=_raw "database_name:(?.*)\s"
| rex field=_raw "server_instance_name:(?.*)\s"
| rex field=_raw "server_principal_name:(?.*)\s"
7/17/17 12:03:44.000 PM 07/17/2017 12:03:44 PM LogName=Application SourceName=MSSQLSERVER EventCode=33205 EventType=0 Type=Information ComputerName=Batman.Justiceleague.dc TaskCategory=None OpCode=None RecordNumber=68853580 Keywords=Audit Success, Classic Message=Audit event: event_time:2017-07-17 18:03:44.2141420 sequence_number:1 action_id:IN succeeded:true is_column_permission:false session_id:76 server_principal_id:403 database_principal_id:1 target_server_principal_id:0 target_database_principal_id:0 object_id:7671075 user_defined_event_id:0 class_type:U permission_bitmask:00000000000000000000000000000008 session_server_principal_name:Batcave server_principal_name:Batman server_principal_sid:a1f03f0aga235sga232437d411 database_principal_name:dbo target_server_principal_name: target_server_principal_sid: target_database_principal_name: server_instance_name:DCUniverse database_name:Justice_league schema_name:dbo object_name:auditinfoadm statement:insert auditinfoadm (actor, actorname, dtime, event, ipaddr) select @l_memberid, name, getutcdate(), 'LOGOUT', @l_ipaddr from member where memberid = @l_memberid
additional_information: user_defined_information:
I want to create some field extractions so I don't have use these commands in my search string. I have tried doing so using both the tool and by manually creating my own regex. But I think because of how short the sample event log is (as per below), the information I want at the bottom of my event log is not being searched, thus it is not creating the fields for me in the "interesting fields" column in the events tab.
I have also tried using the "Filter" search bar to search for "server_principal_name" and this shows me the data, (while it will omit data it can't show) but it still does not create the extracted fields I want.
Anyone have ideas on how I can get around this?
... View more