topic Re: Field Extraction from complex events in Splunk Search

Field Extraction from complex events

SplunkDash — Thu, 02 Dec 2021 05:52:24 GMT

Hello,

I have some issues extracting fields from the following raw event. I should be getting following fileds from this event. Any help will be highly appreciated. Thank you!

Field Names:

TIMESTAMP, USERTYPE, USERID, SYSTEM, EVENTTYPE, EVENTID, SRCADDR, SESSIONID, TAXPERIOD, RETURNCODE, TAXFILERTIN, VARDATA

Sample Event:

{"log":"\u001b[0m\u001b[0m05:14:09,516 INFO [stdout] (default task-4193) 2021-12-02 05:14:09,516 INFO [tltest.logging.TltestEventWriter] \u003cMODTRANSAUDTRL\u003e\u003cEVENTID\u003e1210VIEW\u003c/EVENTID\u003e\u003cEVENTTYPE\u003eDATA_INTERACTION\u003c/EVENTTYPE\u003e\u003cSRCADDR\u003e192.131.8.1\u003c/SRCADDR\u003e\u003cRETURNCODE\u003e00\u003c/RETURNCODE\u003e\u003cSESSIONID\u003etfYU4-AEPnEzZg\u003c/SESSIONID\u003e\u003cSYSTEM\u003eTLCATS\u003c/SYSTEM\u003e\u003cTIMESTAMP\u003e20211202051409\u003c/TIMESTAMP\u003e\u003cUSERID\u003eAX3BLNB\u003c/USERID\u003e\u003cUSERTYPE\u003eAdmin\u003c/USERTYPE\u003e\u003cVARDATA\u003eCASE NUMBER, CASE NAME;052014011348000,BANTAM LLC\u003c/VARDATA\u003e\u003c/MODTRANSAUDTRL\u003e\n","stream":"stdout","time":"2021-12-02T05:14:09.517228451Z"}

Re: Field Extraction from complex events

yuanliu — Thu, 02 Dec 2021 07:24:05 GMT

I can see that your raw event is a valid JSON object; the "log" field in that object contains a valid XML element. Here is a strategy using spath command:

| makeresults | eval _raw = "{\"log\":\"\u001b[0m\u001b[0m05:14:09,516 INFO [stdout] (default task-4193) 2021-12-02 05:14:09,516 INFO [tltest.logging.TltestEventWriter] \u003cMODTRANSAUDTRL\u003e\u003cEVENTID\u003e1210VIEW\u003c/EVENTID\u003e\u003cEVENTTYPE\u003eDATA_INTERACTION\u003c/EVENTTYPE\u003e\u003cSRCADDR\u003e192.131.8.1\u003c/SRCADDR\u003e\u003cRETURNCODE\u003e00\u003c/RETURNCODE\u003e\u003cSESSIONID\u003etfYU4-AEPnEzZg\u003c/SESSIONID\u003e\u003cSYSTEM\u003eTLCATS\u003c/SYSTEM\u003e\u003cTIMESTAMP\u003e20211202051409\u003c/TIMESTAMP\u003e\u003cUSERID\u003eAX3BLNB\u003c/USERID\u003e\u003cUSERTYPE\u003eAdmin\u003c/USERTYPE\u003e\u003cVARDATA\u003eCASE NUMBER, CASE NAME;052014011348000,BANTAM LLC\u003c/VARDATA\u003e\u003c/MODTRANSAUDTRL\u003e\n\",\"stream\":\"stdout\",\"time\":\"2021-12-02T05:14:09.517228451Z\"}" ``` first, extract log from JSON ``` | spath | fields - _raw ``` this is just to clear table view, immaterial ``` ``` next, extract XML from log ``` | rex field=log mode=sed "s/.*tltest.logging.TltestEventWriter.\s//" ``` third, extract XML fields ``` | spath input=log

MODTRANSAUDTRL.EVENTID

MODTRANSAUDTRL.EVENTTYPE

MODTRANSAUDTRL.RETURNCODE

MODTRANSAUDTRL.SESSIONID

MODTRANSAUDTRL.SRCADDR

MODTRANSAUDTRL.SYSTEM

MODTRANSAUDTRL.TIMESTAMP

MODTRANSAUDTRL.USERID

MODTRANSAUDTRL.USERTYPE

MODTRANSAUDTRL.VARDATA

log

stream

time<

1210VIEW

DATA_INTERACTION

tfYU4-AEPnEzZg

192.131.8.1

TLCATS

20211202051409

AX3BLNB

Admin

CASE NUMBER, CASE NAME;052014011348000,BANTAM LLC

<MODTRANSAUDTRL><EVENTID>1210VIEW</EVENTID><EVENTTYPE>DATA_INTERACTION</EVENTTYPE><SRCADDR>192.131.8.1</SRCADDR><RETURNCODE>00</RETURNCODE><SESSIONID>tfYU4-AEPnEzZg</SESSIONID><SYSTEM>TLCATS</SYSTEM><TIMESTAMP>20211202051409</TIMESTAMP><USERID>AX3BLNB</USERID><USERTYPE>Admin</USERTYPE><VARDATA>CASE NUMBER, CASE NAME;052014011348000,BANTAM LLC</VARDATA></MODTRANSAUDTRL>

stdout

2021-12-02T05:14:09.517228451Z

Re: Field Extraction from complex events

SplunkDash — Thu, 02 Dec 2021 10:24:32 GMT

Hello,

Thank you so much for sending me this field extraction code, truly appreciate it. But how I would implement this code here (please see screenshot below)? Any help will be highly appreciated, thank you again.

Re: Field Extraction from complex events

yuanliu — Thu, 02 Dec 2021 18:15:38 GMT

The built-in Field Extraction may not be the best approach to your problem. I'll leave possible ways to do that to later. Let me first propose using macro. Like Field Extraction, using macro also allows code reuse and improves maintainability.

Go to "Settings -> Advanced search -> Search macros -> New Search macro", put all what you tested into Definition. After you save it, say as "my-macro", you can invoke it in any place by inserting

`my-macro`

Do some experiment with it. (You can parameterize a macro with arguments. But if you are new to macros, don't worry about that at the beginning.)

Now, to possible use of built-in Field Extraction. This is undesirable for several reasons. First, your actual data have well-known structures. It is advantageous to use SPL's builtin spath command to deal with them. Second, your data contains escaped non-ASCII Unicode, which makes use of regex messy, whereas builtin SPL functions take care of them painlessly. (Once multi-byte Unicode is "flattened" into escape code, it is not easy to turn them back explicitly in SPL.)

Still, I want to give one example using rex. The example is for EVENTID:

\\\u003cEVENTID\\\u003e(?<EVENTID>[^\\\]+)

Note this example is constructed to merely be able to extract that value from the exact sample data you posted. It may not work for all your data.

Re: Field Extraction from complex events

SplunkDash — Fri, 03 Dec 2021 02:50:37 GMT

Hello,

Thank you so much and I also thought about micro. but client doesn't like to go that way. Good thing is that your \\u003cEVENTID\\u003e(?<EVENTID>[^\\]+) is working as expected. Only problem with that when I have any missing values or fields in the events, see the following 2 sample events, Event 1 doesn't have the TRANSACTIONCODE field, but Event 2 does. These types of missing fields/field values coursing issues doing field extraction using \\u003cEVENTID\\u003e(?<EVENTID>[^\\]+) . Would it be possible to address this issue? Thank you so much again, appreciate your support in these efforts.

Complete codes (working as expected for Event 1 but not working for Event 2 due to TRANSACTIONCODE field )

\\u003cEVENTID\\u003e(?<EVENTID>[^\\]+)\\u003c\/EVENTID\\u003e\\u003cEVENTTYPE\\u003e(?<EVENTYPE>[^\\]+)\\u003c\/EVENTTYPE\\u003e\\u003cSRCADDR\\u003e(?<SRCADDR>[^\\]+)\\u003c\/SRCADDR\\u003e\\u003cRETURNCODE\\u003e(?<RETURNCODE>[^\\]+)\\u003c\/RETURNCODE\\u003e\\u003cSESSIONID\\u003e(?<SESSIONID>[^\\]+)\\u003c\/SESSIONID\\u003e\\u003cSYSTEM\\u003e(?<SYSTEM>[^\\]+)\\u003c\/SYSTEM\\u003e\\u003cTIMESTAMP\\u003e(?<TIMESTAMP>[^\\]+)\\u003c\/TIMESTAMP\\u003e\\u003cUSERID\\u003e(?<USERID>[^\\]+)\\u003c\/USERID\\u003e\\u003cUSERTYPE\\u003e(?<USERTYPE>[^\\]+)\\u003c\/USERTYPE\\u003e\\u003cVARDATA\\u003e(?<VARDATA>[^\\]+)

Event 1

Event 2

{"log":"\u001b[0m\u001b[0m05:14:09,516 INFO [stdout] (default task-4193) 2021-12-02 06:14:09,516 INFO [tltest.logging.TltestEventWriter] \u003cMODTRANSAUDTRL\u003e\u003cEVENTID\u003e1210VIEW\u003c/EVENTID\u003e\u003cEVENTTYPE\u003eDATA_INTERACTION\u003c/EVENTTYPE\u003e\u003cSRCADDR\u003e192.131.8.1\u003c/SRCADDR\u003e\u003cTRANSACTIONCODE\u003e192.131.8.1\u003c/TRANSACTIONCODE\003xy\u003cRETURNCODE\u003e00\u003c/RETURNCODE\u003e\u003cSESSIONID\u003etfYU4-AEPnEzZg\u003c/SESSIONID\u003e\u003cSYSTEM\u003eTLCATS\u003c/SYSTEM\u003e\u003cTIMESTAMP\u003e20211202051409\u003c/TIMESTAMP\u003e\u003cUSERID\u003eAX3BLNB\u003c/USERID\u003e\u003cUSERTYPE\u003eAdmin\u003c/USERTYPE\u003e\u003cVARDATA\u003eCASE NUMBER, CASE NAME;052014011348000,BANTAM LLC\u003c/VARDATA\u003e\u003c/MODTRANSAUDTRL\u003e\n","stream":"stdout","time":"2021-12-02T05:14:09.517228451Z"}

Re: Field Extraction from complex events

yuanliu — Fri, 03 Dec 2021 07:37:47 GMT

Event 1 doesn't have the TRANSACTIONCODE field, but Event 2 does. These types of missing fields/field values coursing issues doing field extraction

As noted in my previous message, ad hoc rex often suffer from inflexibility. This is one big reason to leverage builtin functions that complies with structured data types. I hope that the client will double your pay the next time they have some data that don't fit the existing code.

Yes, you can work around these conditions by crafting PCRE more carefully. For example, if the order of fields in the XML is absolutely certain, i.e., TRANSACTIONCODE always appear in between SRCADDR and RETURNCODE, you can use

(\\\u003cTRANSACTIONCODE\\\u003e(?<TRANSACTIONCODE>[^\\\]+)\\\u003c/TRANSACTIONCODE\\\u003e){0,1}

to signify that <TRANSACTIONCODE>***</TRANSACTIONCODE> may appear 0 times or 1 time in between those two fields. NOTE here I surmise that you made a typo in the second sample event by closing TRANSACTIONCODE tag with \003xy instead of expected \u003e (>).

However, XML does not require fields to appear in any given order. So, there is no guarantee. If you must use rex, most people would do multiple extractions, one for each tag. This is also a better way to avoid the problem caused by fields appearing in some events but not others. For example, use

\\\u003cEVENTID\\\u003e(?<EVENTID>[^\\\]+)

to extract EVENTID, then use

\\\u003cEVENTTYPE\\\u003e(?<EVENTTYPE>[^\\\]+)

to extract EVENTTYPE, and so on. No need to use (expr){0,1} because if the simple expression doesn't match, that field simply will not be extracted. (Even these singular field extractions may not work in all conditions. For one, there is no requirement for XML tags to have brackets immediately bound field name. For example, there can be any number of elements, blanks, line breaks, optional declarations, etc., between EVENTID and "<" or ">".)

This said, if you want to use fixed order, here is a construct that can extract both sample events.

| makeresults count=2 | streamstats count | eval _raw = if(count==1,"{\"log\":\"\u001b[0m\u001b[0m05:14:09,516 INFO [stdout] (default task-4193) 2021-12-02 05:14:09,516 INFO [tltest.logging.TltestEventWriter] \u003cMODTRANSAUDTRL\u003e\u003cEVENTID\u003e1210VIEW\u003c/EVENTID\u003e\u003cEVENTTYPE\u003eDATA_INTERACTION\u003c/EVENTTYPE\u003e\u003cSRCADDR\u003e192.131.8.1\u003c/SRCADDR\u003e\u003cRETURNCODE\u003e00\u003c/RETURNCODE\u003e\u003cSESSIONID\u003etfYU4-AEPnEzZg\u003c/SESSIONID\u003e\u003cSYSTEM\u003eTLCATS\u003c/SYSTEM\u003e\u003cTIMESTAMP\u003e20211202051409\u003c/TIMESTAMP\u003e\u003cUSERID\u003eAX3BLNB\u003c/USERID\u003e\u003cUSERTYPE\u003eAdmin\u003c/USERTYPE\u003e\u003cVARDATA\u003eCASE NUMBER, CASE NAME;052014011348000,BANTAM LLC\u003c/VARDATA\u003e\u003c/MODTRANSAUDTRL\u003e\n\",\"stream\":\"stdout\",\"time\":\"2021-12-02T05:14:09.517228451Z\"}", "{\"log\":\"\u001b[0m\u001b[0m05:14:09,516 INFO [stdout] (default task-4193) 2021-12-02 06:14:09,516 INFO [tltest.logging.TltestEventWriter] \u003cMODTRANSAUDTRL\u003e\u003cEVENTID\u003e1210VIEW\u003c/EVENTID\u003e\u003cEVENTTYPE\u003eDATA_INTERACTION\u003c/EVENTTYPE\u003e\u003cSRCADDR\u003e192.131.8.1\u003c/SRCADDR\u003e\u003cTRANSACTIONCODE\u003e192.131.8.1\u003c/TRANSACTIONCODE\u003e\u003cRETURNCODE\u003e00\u003c/RETURNCODE\u003e\u003cSESSIONID\u003etfYU4-AEPnEzZg\u003c/SESSIONID\u003e\u003cSYSTEM\u003eTLCATS\u003c/SYSTEM\u003e\u003cTIMESTAMP\u003e20211202051409\u003c/TIMESTAMP\u003e\u003cUSERID\u003eAX3BLNB\u003c/USERID\u003e\u003cUSERTYPE\u003eAdmin\u003c/USERTYPE\u003e\u003cVARDATA\u003eCASE NUMBER, CASE NAME;052014011348000,BANTAM LLC\u003c/VARDATA\u003e\u003c/MODTRANSAUDTRL\u003e\n\",\"stream\":\"stdout\",\"time\":\"2021-12-02T05:14:09.517228451Z\"}") | rex "\\\u003cEVENTID\\\u003e(?<EVENTID>[^\\\]+)\\\u003c/EVENTID\\\u003e\\\u003cEVENTTYPE\\\u003e(?<EVENTTYPE>[^\\\]+)\\\u003c/EVENTTYPE\\\u003e\\\u003cSRCADDR\\\u003e(?<SRCADDR>[^\\\]+)\\\u003c/SRCADDR\\\u003e(\\\u003cTRANSACTIONCODE\\\u003e(?<TRANSACTIONCODE>[^\\\]+)\\\u003c/TRANSACTIONCODE\\\u003e){0,1}\\\u003cRETURNCODE\\\u003e(?<RETURNCODE>[^\\\]+)\\\u003c/RETURNCODE\\\u003e\\\u003cSESSIONID\\\u003e(?<SESSIONID>[^\\\]+)\\\u003c/SESSIONID\\\u003e\\\u003cSYSTEM\\\u003e(?<SYSTEM>[^\\\]+)\\\u003c/SYSTEM\\\u003e\\\u003cTIMESTAMP\\\u003e(?<TIMESTAMP>[^\\\]+)\\\u003c/TIMESTAMP\\\u003e\\\u003cUSERID\\\u003e(?<USERID>[^\\\]+)\\\u003c/USERID\\\u003e\\\u003cUSERTYPE\\\u003e(?<USERTYPE>[^\\\]+)\\\u003c/USERTYPE\\\u003e\\\u003cVARDATA\\\u003e(?<VARDATA>[^\\\]+)"

EVENTID

EVENTTYPE

RETURNCODE

SESSIONID

SRCADDR

SYSTEM

TIMESTAMP

TRANSACTIONCODE

USERID

USERTYPE

VARDATA

_raw

_time

count

1210VIEW

DATA_INTERACTION

tfYU4-AEPnEzZg

192.131.8.1

TLCATS

20211202051409

AX3BLNB

Admin

CASE NUMBER, CASE NAME;052014011348000,BANTAM LLC

2021-12-02 23:20:29

1210VIEW

DATA_INTERACTION

tfYU4-AEPnEzZg

192.131.8.1

TLCATS

20211202051409

192.131.8.1

AX3BLNB

Admin

CASE NUMBER, CASE NAME;052014011348000,BANTAM LLC

{"log":"\u001b[0m\u001b[0m05:14:09,516 INFO [stdout] (default task-4193) 2021-12-02 06:14:09,516 INFO [tltest.logging.TltestEventWriter] \u003cMODTRANSAUDTRL\u003e\u003cEVENTID\u003e1210VIEW\u003c/EVENTID\u003e\u003cEVENTTYPE\u003eDATA_INTERACTION\u003c/EVENTTYPE\u003e\u003cSRCADDR\u003e192.131.8.1\u003c/SRCADDR\u003e\u003cTRANSACTIONCODE\u003e192.131.8.1\u003c/TRANSACTIONCODE\u003e\u003cRETURNCODE\u003e00\u003c/RETURNCODE\u003e\u003cSESSIONID\u003etfYU4-AEPnEzZg\u003c/SESSIONID\u003e\u003cSYSTEM\u003eTLCATS\u003c/SYSTEM\u003e\u003cTIMESTAMP\u003e20211202051409\u003c/TIMESTAMP\u003e\u003cUSERID\u003eAX3BLNB\u003c/USERID\u003e\u003cUSERTYPE\u003eAdmin\u003c/USERTYPE\u003e\u003cVARDATA\u003eCASE NUMBER, CASE NAME;052014011348000,BANTAM LLC\u003c/VARDATA\u003e\u003c/MODTRANSAUDTRL\u003e\n","stream":"stdout","time":"2021-12-02T05:14:09.517228451Z"}

2021-12-02 23:20:29

Again, note that I use \u003e to close all tags.