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

[charlesxavier]

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!

[chrisyounger]

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.

[charlesxavier]

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.

[Vijeta · Accepted solution]

@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

[charlesxavier]

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

[Vijeta]

@charlesxavier Try this

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

[charlesxavier]

That seemed to do the trick, thank you!

[Vijeta]

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

[Vijeta]

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

[charlesxavier]

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