topic Re: Issue using the field extractor in Splunk Search

Issue using the field extractor?

DeanDeleon0 — Fri, 05 May 2023 18:18:34 GMT

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?

Re: Issue using the field extractor

DalJeanis — Wed, 19 Jul 2017 00:01:43 GMT

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?

Re: Issue using the field extractor

woodcock — Wed, 19 Jul 2017 00:26:13 GMT

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

Re: Issue using the field extractor

DeanDeleon0 — Mon, 31 Jul 2017 15:27:27 GMT

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:

Re: Issue using the field extractor

DeanDeleon0 — Mon, 31 Jul 2017 15:41:04 GMT

/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

Re: Issue using the field extractor

woodcock — Mon, 31 Jul 2017 15:56:13 GMT

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+)

Re: Issue using the field extractor

DeanDeleon0 — Tue, 29 Sep 2020 15:10:09 GMT

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, @Anonymous, @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?

Re: Issue using the field extractor

woodcock — Mon, 31 Jul 2017 17:33:16 GMT

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

Re: Issue using the field extractor

woodcock — Mon, 31 Jul 2017 17:33:50 GMT

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

Re: Issue using the field extractor

DeanDeleon0 — Mon, 31 Jul 2017 18:12:55 GMT

Thanks! Your help was much appreciated!

Re: Issue using the field extractor

cdpascariu — Tue, 02 Oct 2018 10:29:12 GMT

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!

Re: Issue using the field extractor

splunkyj — Fri, 05 May 2023 16:32:36 GMT

Pretty close. This one worked for me.

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