Splunk Search
Highlighted

What's the best way to extract key value pairs from the following log?

Motivator

Hello Experts,

I am trying to extract key-value pairs from the following. Here's the sample log. I have tried using xmlkv, spath and no luck.

"2014-11-11 04:46:13|xyz|INFO|#ae5760fa|Sweepstakes|RESPONSE=REDEEMEDON11-11-2014,04:46:06BATCHTYPE2CUSTFIRSTNAMEjacobBATCHEXPIRATION11-12-2014,04:43:16CUSTEMAILCONFIRMEDNVOUCHERIDZBEB2RLKQZOBCUSTINTERNETPROVIDEROtherPURCHASECOMPLETEYVOUCHERCOUNT1CUSTLAStNAMEGomathamDEVICELIMIT0POLICYTYPE1CUSTMACADDRESSabc1.ab12.a123.1234CREATEDBYcustCREATEDON11-11-2014,04:43:16BATCHIDCUSTREGISTEREDON11-11-2014,04:43:16CUSTEMAILxyz@yahoo.comBATCH_STATUSA","2014-11-11T04:46:13.000-0500"

What's the easiest way to extract first and second as $field1::$field2 or a on the fly search time extraction?

Appreciate your help.

Thanks,
Rgv

0 Karma
Highlighted

Re: What's the best way to extract key value pairs from the following log?

Builder

Hey Raghav, you definitely need to use regular expressions... Before I suggest anything, is the text you paste correct? looks like is missing "whitespaces" and the line breaking is wrong... Try to paste the text and use the "code sample" icon on the top of the form to format it correctly, like this:

test 123

As regular expression suggestion, I always recommend to use the site http://regex101.com/ to play with.

0 Karma
Highlighted

Re: What's the best way to extract key value pairs from the following log?

Motivator

Unfortunately, that's the format they are in. Multilines with no spaces 😞

0 Karma
Highlighted

Re: What's the best way to extract key value pairs from the following log?

Builder

Ok, so just clarify, what you mean by extract "first and second as $field1::$field2"? What are the parts of the text you wish to extract as fields, pls give me an example, like:

fieldA=jacob
fieldB=Gomatham
fieldC=???
etc

Cheers

0 Karma
Highlighted

Re: What's the best way to extract key value pairs from the following log?

Motivator

Yup...you got it

0 Karma
Highlighted

Re: What's the best way to extract key value pairs from the following log?

Builder

So, based on that here how you could start with:

index=bla "your search" | rex field=_raw "FIRST_NAME(?P<fieldA>\w+)BATCH_EXPIRATION.+LASt_NAME(?P<fieldB>\w+)DEVICE_LIMIT" | table fieldA, fieldB

This is just an example assuming all your events have a similar format, you should be able to extract other fields as your requirement. Again, just "past" your log into http://regex101.com and start with the Regex on my example.

View solution in original post

Highlighted

Re: What's the best way to extract key value pairs from the following log?

Builder

Just two more things... you probably notice you could choose any name to the "fieldA" (B, etc). Also looking to your data, should be straight forward to expand this extraction method for almost all fields.

Once you have it done, you also could configure it inside "Settings -> Fields -> Field Extraction" so you don't need to do it every search using the rex command.

Cheers

0 Karma