Splunk Search

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

charlesxavier
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
1 Solution

Vijeta
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

chrisyounger
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

charlesxavier
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

Vijeta
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
0 Karma

charlesxavier
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

Vijeta
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

charlesxavier
New Member

That seemed to do the trick, thank you!

0 Karma

Vijeta
Influencer

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

0 Karma

Vijeta
Influencer

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

0 Karma

charlesxavier
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...