Issue using the field extractor?

[DeanDeleon0]

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?

[cdpascariu]

I had the same issue with the "statement" extraction. After hours of trial and error, I found the magic regex:

...| rex field=_raw "statement:(?[^^$]+)additional" | ...

Regex: [^^$]+
[^^$] match anything that is not an empty line
+ match multiple times

Give it a try!

[splunkyj]

Pretty close. This one worked for me. 

| rex field=_raw "statement:(?<statement>[^^$]+)"

[woodcock · Accepted solution]

You are misunderstanding the way that EXTRACT- works, assuming that it is like EVAL- in that the portion following the hyphen is the name of the field: it is not! You need to specify the field name inside the RegEx string so try something like this instead:

[WinEventLog:Application]
EXTRACT-server_instance_name = server_instance_name:(?<server_instance_name>\S+)
EXTRACT-statement = statement:(?<statement>\S+)
EXTRACT-server_principal_name = server_principal_name:(?<server_principal_name>\S+)
EXTRACT-database_name = database_name:(?<database_name>\S+)

[DeanDeleon0]

YES! Thank you for this clarification. This resolved the issue I was having. Another question if I may?

The table now shows:

INSERT

This is fine for the other three fields. But the "statement" shows the following, which I would also like to include:

statement:INSERT INTO dbo.Ap_Invoice_Split (Ap_Invoice_Id, Inter_Company_Skey, Branch_Skey, Gl_Account_Skey, Product_Skey, Category_Skey, Description, Purchase_Order, Amount, GST_Amount) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9);

Could you also clarify for me how to include the rest of this information as per the statement above?

EXTRACT-statement = statement:(?\S+)

As per my understanding of regex \S+ is for selecting "not whitespace" and matching 1 or more of the preceeding token? Which is why it stops after seeing a blank space. How do extend this to cover the rest of this?

[woodcock]

Don't forget to upvote all the helpful answers and click Accept on the best one.

[DeanDeleon0]

Thanks! Your help was much appreciated!

[woodcock]

You need some kind of delimiter to use as an anchor on the end, maybe (?<statement>.*?);($|[\r\n]+)

[woodcock]

If you "have tried" then do show us your props.conf and transforms.conf entries.

[DeanDeleon0]

/opt/splunk/etc/apps/search/local/props.conf:

[WinEventLog:Application]
EXTRACT-server_instance_name = server_instance_name:(?.*)\s
EXTRACT-statement = statement:(?.*)\s
EXTRACT-server_principal_name = server_principal_name:(?.*)\s
EXTRACT-database_name = database_name:(?.*)\s
[*]

I haven't put anything in the transforms.conf

[DalJeanis]

Probably not the length of the event, unless it's thousands of characters long.

When you try the search_time extraction -
1) Did you leave off the quotes from the regex?

2) Did you restart splunk?

[DeanDeleon0]

Hi Thanks for the reply. I found that I had an extra \ at the end and this was causing it to not show anything. However the problem I have now is, it is displaying everything after the line specified:

<redacted> database_name:<redacted> 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:<tsql_stack><frame nest_level = '3' database_name = <redacted>' schema_name = 'dbo' object_name = 'usrsession_bdr'/></tsql_stack> user_defined_information: