topic Re: Rex Not Extracting All Data in Splunk Search

Rex Not Extracting All Data

IRHM73 — Wed, 21 Oct 2015 10:12:16 GMT

HI,

I wonder whether someone could help me please.

I'm trying to extract the first name from the data as shown below:

 [{"name":{"current":{"firstName":"M","lastName":"SMITH"}},"ids":{"nino":"AA111111A"},"dateOfBirth":"26121973"}]

So I've put together the following rex:

rex field="detail.output-cid-response" "\"firstName\":\"(?<cidFName>[^\"]+)"

The problem I have is that although there is data there, it is not extracting the "cidFName" for all the records and to be honest I'm at a loss why.

Could someone perhaps shed some light on where I'm going wrong please.

Many thanks and kind regards

Chris

Re: Rex Not Extracting All Data

krish3 — Wed, 21 Oct 2015 10:24:16 GMT

try this....

\"firstName\":\"(?<cidFName>[\w]+)"

Re: Rex Not Extracting All Data

IRHM73 — Wed, 21 Oct 2015 10:34:16 GMT

Hi @krish3, thank you for taking the time to reply to my post,

I've tried the query you kindly provided, but unfortunately this hasn't made any difference.

Many thanks and kind regards

Chris

Re: Rex Not Extracting All Data

krish3 — Wed, 21 Oct 2015 11:20:21 GMT

Can you please share what is the value of field detail.output-cid-response

Re: Rex Not Extracting All Data

IRHM73 — Wed, 21 Oct 2015 11:23:18 GMT

Hi @krish3, my apologies for not making this clear but detail,.output-cid-response is the raw data shown in my initial post i.e. [{"name":{"current":{"firstName":"CHRIS","lastName":"SMITH"}},"ids":{"nino":"AA111111A"},"dateOfBirth":"26121973"}]

Many thanks and kind regards

Chris

Re: Rex Not Extracting All Data

renatobamorim — Wed, 21 Oct 2015 11:55:05 GMT

If any event has two names on this field, better you use this:

firstName\":\"(?P<cidFname>.*?)\"

firstName\":\"(?P<cidFname>[\w\s]+)\"

Re: Rex Not Extracting All Data

IRHM73 — Wed, 21 Oct 2015 12:03:17 GMT

Hi @renatobamorim, thank you for taking the time to come back to me with this.

I must admit I wasn't quite sure what to do with the query you kindly sent but using the snippet as the following:

rex field="detail.output-cid-response" ""firstName":"(?.*?)""

I receive

a Error in 'rex' command: Encountered
the following error while compiling
the regex 'firstName:(?.*?)': Regex:
unrecognized character after (? or (?-

I've had to include the double " otherwise I receive an unbalanced quotes error message.

Many thanks and kind regards

Chris

Re: Rex Not Extracting All Data

krish3 — Wed, 21 Oct 2015 12:22:46 GMT

Can you post few more lines of your logs I do not see any issues with the regex pattern....

Check your regex here

Re: Rex Not Extracting All Data

IRHM73 — Wed, 21 Oct 2015 12:28:27 GMT

Hi please find a little more of my log:

{"output-cid-response":"[{\"name\":{\"current\":{\"firstName\":\"ESTELLE\",\"lastName\":\"CRICHTON\"}},\"ids\":{\"sautr\":\"2354290204\",\"nino\":\"ZA631419C\"},\"dateOfBirth\":\"04111923\"}]"

I also don't see a problem with Regex, because I've been using Regex101 to check this.

I hope this helps.

Many thanks and kind regards

Chris

Re: Rex Not Extracting All Data

muebel — Wed, 21 Oct 2015 12:30:34 GMT

Hi IRHM73, That either means that the regex isn't valid for all values of the "detail.output-cid-response" field, or that the "detail.output-cid-response" field doesn't exist for all events.

I would run the regex over _raw, which is the default value for the rex command.

So, in that way, try running

rex "\"firstName\":\"(?<cidFName>[^\"]+)"

If that doesn't pull all the cidFName fields as you would expect, post the _raw for the events where the field isn't extracting properly.

Please let me know how this works! 😄

Re: Rex Not Extracting All Data

IRHM73 — Wed, 21 Oct 2015 12:49:26 GMT

Hi @muebel, thank you for taking the time to reply to my post.

I tried the query you kindly sent but found I had to put 'rex field....' in front.

But unfortunately the details on some of the records are missing inc the one shown as the raw data log below:

{"auditSource":"matching","auditType":"TxSucceeded","eventId":"cc642788","tags":{"X-Request-ID":"uke83d","transactionName":"Search"},"detail":{"output-cid-response":"[{\"name\":{\"current\":{\"firstName\":\"JOHN\",\"lastName\":\"SMITH\"}},\"ids\":{\"nino\":\"AA111111A\"},\"dateOfBirth\":\"26121973\"}]","output-cycle":"CYCLE3","output-matching-time-in-millis":"120","input-searchRequest":"IncomingSearchRequest(Some(AA111111A),Some(John),Some(Smith),Some(1973-12-26))","output-errors":"[]","output-result":"match found","input-nino":"AA111111A"},"generatedAt":"2015-10-20T20:04:14.728Z"}

I can confirm that the detail.output-cid-response is present in all records and as far as I can see they are exactly the same with differeing usernames, nino's etc.

Many thanks and kind regards

Chris

Re: Rex Not Extracting All Data

renatobamorim — Wed, 21 Oct 2015 12:50:22 GMT

try use rex field=_raw

Re: Rex Not Extracting All Data

muebel — Wed, 21 Oct 2015 13:08:30 GMT

the double quotes are escaped within the _raw of all the events? In that case try escaping the slashes as well:

 rex field=_raw "\\\"firstName\\\":\\\"(?<cidFName>[^\"]+)"

Re: Rex Not Extracting All Data

IRHM73 — Wed, 21 Oct 2015 13:08:48 GMT

Hi thank you for clarifying on how to use the querys.

Unfortunately there was no change using firstName\":\"(?P.*?)\"and firstName\":\"(?P[\w\s]+)\"" didn't extract any information.

I then tried:

firstName\":\"(?P<cidFname>.*?)\"
firstName\":\"(?P<cidFname>[\w\s]+)\"
\"firstName\":\"(?<cidFName>[^\"]+)"

All with the rex=field raw, and unfortunately these did not extract any of the information.

Many thanks and kind regards

Chris

Re: Rex Not Extracting All Data

IRHM73 — Wed, 21 Oct 2015 13:19:12 GMT

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

In answer to your question, all the raw events the double quotes are escaped.

I tried the query you provided, but unfortunately I receive the following error:

Error in 'SearchParser': Missing a
search command before '^'. Error at
position '470' of search query 'search
index=main auditSource="matching"
auditType...{snipped} {errorcontext =
Name":"(?[^"]+)" | e}'.

Many thanks and kind regards

Chris

Re: Rex Not Extracting All Data

wpreston — Wed, 21 Oct 2015 13:38:02 GMT

Can you try this one?

rex field="detail.output-cid-response" "firstName\\\":\\\"(?<cidFName>[^\\]+)\\"

Re: Rex Not Extracting All Data

IRHM73 — Wed, 21 Oct 2015 13:40:21 GMT

Hi @wpreston, thank you for this.

Unfortunately when I run this I recieve this error:

Error in 'rex' command: Encountered
the following error while compiling
the regex
'firstName\":\"(?[^]+)\':
Regex: \ at end of pattern

Many thanks and kind regards

Chris

Re: Rex Not Extracting All Data

wpreston — Wed, 21 Oct 2015 14:28:04 GMT

Hmm, ok how about this one?

rex field=detail.output-cid-response "firstName.\":.\"(?<NewField>.+)[\\\]\","

Re: Rex Not Extracting All Data

wpreston — Wed, 21 Oct 2015 15:10:08 GMT

I indexed your sample data and was able to use the following regex to extract "JOHN" as the "firstName" field. One rex extracted from the _raw field as a source, and the other extracted from the detail.output-cid-response field as a source. Please see if either fits your needs:

| rex field=_raw "firstName[\\\]\":[\\\]\"(?<firstNameRaw>[^\\\]+)[\\\]"

| rex field="detail.output-cid-response" "firstName\":\"(?<firstName>[^\"]+)\""

Re: Rex Not Extracting All Data

IRHM73 — Thu, 22 Oct 2015 06:12:30 GMT

Eureka!!!!

@Wpreston, thank you for coming back to me with this it is greatly appreciated. I've tried the queries you kindly provided and this one worked: | rex field=_raw "firstName[\\\]\":[\\\]\"(?[^\\\]+)[\\\]"

So that I can learn from this, could I ask please what the '[' and ']' do?

Many thanks and kind regards

Chris