Splunk Search
Highlighted

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

New Member

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!

0 Karma
Highlighted

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

Influencer

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

0 Karma
Highlighted

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

New Member

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

0 Karma
Highlighted

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

Influencer

@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

View solution in original post

0 Karma
Highlighted

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

Influencer

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

0 Karma
Highlighted

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

New Member

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 (?-

0 Karma
Highlighted

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

Influencer

@charlesxavier Try this

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

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

New Member

That seemed to do the trick, thank you!

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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.

0 Karma
Highlighted

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

New Member

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.

0 Karma