topic Re: Extract Data From Event in Splunk Search

Extract Data From Event

IRHM73 — Mon, 24 Aug 2015 06:40:57 GMT

Hi, I wonder whether someone can help me please.

I have multiple events which include the following piece of information "empRef\":\"012/A12345\" in the middle of the event.

Could someone perhaps tell me please how it's possible to extract this piece of information from the event data.

Many thanks and kind regards

Chris

Re: Extract Data From Event

liorfink — Mon, 24 Aug 2015 06:48:57 GMT

I can't really tell what comes before and after that piece of information you want to extract, but I think what you're looking for is Regex.
If your data looks something like that:
...abcde"empRef":"012/A12345"abcde...
You should be able to make a Regular Expression that will extract that exect piece from the rest.

Re: Extract Data From Event

pwmcity — Mon, 24 Aug 2015 07:39:11 GMT

You need to use the rex command
http://docs.splunk.com/Documentation/Splunk/6.2.5/SearchReference/rex

... | rex field=_raw "\"empRef\":\"(?<empRef>[^\"]*)\"

(disclaimer: regex untested!)

Re: Extract Data From Event

IRHM73 — Mon, 24 Aug 2015 07:44:36 GMT

Hi @pwmcity, thank you very much for this. I'll be able to work with this.

Many thanks and kind regards

Chris

Re: Extract Data From Event

IRHM73 — Mon, 24 Aug 2015 07:45:33 GMT

HI @liorfink, thank you very much for taking the time to come back to me with this.

Kind regards

Chris

Re: Extract Data From Event

pwmcity — Tue, 25 Aug 2015 08:12:24 GMT

Did it work? What's the error?

regex appears to work: https://regex101.com/r/uP9iB9/1

Re: Extract Data From Event

IRHM73 — Tue, 25 Aug 2015 08:22:45 GMT

Hi intially, I received an unbalanced quotes error message, so I inserted a " at the end of the regex which removed the error.

I then add | stats count by empRef to the end of the search because I am just wanting to create a list of the empRef field values, and although the search runs and the events are shown, no list is being created.

My apologies for the novice type problem.

Many thanks and kind regards

Chris

Re: Extract Data From Event

IRHM73 — Tue, 25 Aug 2015 09:18:47 GMT

Hi I'm not sure whether this is of help, but I've managed to get the 'Field Extractor' working and have come up with the following:

 (?=[^e]*(?:empRef|e.*empRef))^(?:[^\\\n]*\\){7}"(?P<REF>[^\\]+)

The problem I have is that I'm not sure how to incorporate this into my search.

I've tried rex " (?=[^e]*(?:empRef|e.*empRef))^(?:[^\\\n]*\\){7}"(?P[^\\]+)" but I receive a parser error.

Many thanks and kind regards

Chris

Re: Extract Data From Event

pwmcity — Wed, 26 Aug 2015 00:06:29 GMT

Not sure what's going on there, but your regex has gotten overly complicated!

Is it that you're trying to capture multiple empRef in a single event? The Rex command defaults to 1, so you can set that with max_match=0 for unlimited.

I put your example string into splunk, to see if it was the way splunk handles double-quotes... but that's not the case and it worked fine, it produces a field 'var' with your example data, and a field 'empRef' with the expected 2 values

index=_internal
| eval var="[{\"friendlyName\":\"\",\"empRef\":\"012/AB00000\"},{\"friendlyName\":\"\",\"empRef\":\"023/AB11111\"}]"
| rex field=var "\"empRef\":\"(?<empRef>[^\"]*)\"" max_match=0

Re: Extract Data From Event

IRHM73 — Wed, 26 Aug 2015 06:10:52 GMT

Hi thank you for coming back to me with this I really appreciate it.

Your assumption is correct in that I'm trying to extract multiple empRef's from a single event, so I ran your script and it works perfectly.

But when I tried to run this using my full script i.e.

index="main" detail.responseMessage="*empRef*" | rex field=var "\"empRef\":\"(?<empRef>[^\"]*)\"" max_match=0
  | stats count by empRef

the list of empRef's are not shown.

Many thanks for all your help and kind regards

Chris

Re: Extract Data From Event

IRHM73 — Fri, 28 Aug 2015 06:53:54 GMT

Hi @pwmcity, I'm really very sorry to trouble you with this again, but I just wondered whether you may have had a chance to look at my last comment and the problems I'm still having with extracting the data.

Many thanks and kind regards

Chris

Re: Extract Data From Event

pwmcity — Tue, 01 Sep 2015 00:16:42 GMT

It could be an issue with mv .... the problem is that since there are multiple empRef values per event, it means now you have a 'multivalue' field. If you look up the available mv commands (http://docs.splunk.com/Documentation/Splunk/6.2.3/SearchReference/Commandsbycategory) and search for mv ... you'll see a few commands there.

You could try | mvexpand empRef | stats count by empRef

Aside from that, have you gone into verbose mode and checked to see if your empRef field is actually being populated?

Re: Extract Data From Event

IRHM73 — Wed, 23 Sep 2015 06:19:57 GMT

Hi @pwmcity, thank you very much for coming back to me with this and my apologies for not replying sooner. The empRef is definitely being populated so I'll have a look at the documentation you highlighted.