topic Re: Can I extract a field from a subsearch by comparing two fields in both the search and subsearch? in Splunk Search

Can I extract a field from a subsearch by comparing two fields in both the search and subsearch?

charlesxavier — Fri, 15 Feb 2019 18:52:36 GMT

Hello,

I'm trying to extract a customer number by having two searches pull web service calls and compare one field with the same values, then get the customer number from the subsearch. The reason for doing this with two web calls is because one is vital for determining if a user was created, but it does not contain the customer number, the second call carries the number.

Here's an example:

addCustomer call:
customer: {"Number":"","FirstName":"Foo","LastName":"Bar","phoneNumber":"1234567890"}

secondCall:
customer: {"Number":"12345676","FirstName":"Foo","LastName":"Bar","phoneNumber":"1234567890"}

I wanted to compare a field that is unique to this user, like the phone number or email (sometimes there isn't an email) and if they are matched, I wanted to get the Number in the second call (which is the subsearch).

Here's what I have to get both web calls for all users within a set time frame:

"addCustomer"
| rex field=_raw "\"phoneNumber\":\"(?<phoneNum>[^\"]+)"  
| append [search "secondCall:" | rex field=_raw "\"Number\":\"(?<CustomerNumber>^\"]+)" | rex field=_raw "\"phoneNumber\":\"(?<phoneNum>[^\"]+)" ]

I'm relatively new to Splunk, so any help would be appreciated!

Re: Can I extract a field from a subsearch by comparing two fields in both the search and subsearch?

Vijeta — Fri, 15 Feb 2019 19:18:03 GMT

are you searching from same index ? how does your event data look like?

Re: Can I extract a field from a subsearch by comparing two fields in both the search and subsearch?

charlesxavier — Fri, 15 Feb 2019 19:19:40 GMT

Hi, yes I am searching from the same index and the event data looks like those two example calls I provided.

Re: Can I extract a field from a subsearch by comparing two fields in both the search and subsearch?

Vijeta — Fri, 15 Feb 2019 19:32:33 GMT

@charlesxavier For example your index name is test, your query would be-

index=test| rex _raw "\"phoneNumber\":\"(?<phoneNumber>[^\"]+)"  | rex field=_raw "\"Number\":\"(?<CustomerNumber>[^\"]+)" | stats values(CustomerNumber) as CustomerNumber by phoneNumber

Re: Can I extract a field from a subsearch by comparing two fields in both the search and subsearch?

chrisyounger — Fri, 15 Feb 2019 19:37:49 GMT

so from the query you have posted, you have already done the hard work. Now you just need to do something like this to join the events together: |stats latest(*) as * by phoneNumber or |stats values(*) as * by phoneNumber

All the best.

Re: Can I extract a field from a subsearch by comparing two fields in both the search and subsearch?

charlesxavier — Mon, 18 Feb 2019 12:50:16 GMT

Hi Chrisyoungerjds,

I've tried adding in both of those lines, and I don't get any results found. Would coalesce() be alright to use here? Also, would I need to pass the second phone number from the subsearch to use to compare/join the events?

Thanks.

Re: Can I extract a field from a subsearch by comparing two fields in both the search and subsearch?

Vijeta — Mon, 18 Feb 2019 17:37:40 GMT

@charlesxavier Did you try the above search, what output do you get?

Re: Can I extract a field from a subsearch by comparing two fields in both the search and subsearch?

charlesxavier — Mon, 18 Feb 2019 17:51:54 GMT

Hello, I tried using this and received the following:

Error in 'rex' command: Encountered the following error while compiling the regex '"phoneNumber":"(?[^"]+)': Regex: unrecognized character after (? or (?-

Re: Can I extract a field from a subsearch by comparing two fields in both the search and subsearch?

Vijeta — Mon, 18 Feb 2019 17:56:40 GMT

@charlesxavier Try this

 index=test| rex _raw "\"phoneNumber\":\"(?<phoneNumber>\S+)}"  | rex field=_raw "\"Number\":\"(?<CustomerNumber>\S+)," | stats values(CustomerNumber) as CustomerNumber by phoneNumber

Re: Can I extract a field from a subsearch by comparing two fields in both the search and subsearch?

charlesxavier — Mon, 18 Feb 2019 18:58:13 GMT

That seemed to do the trick, thank you!