Splunk Search

Club two different searches into one

bashtekar
New Member

I have one search which gives results like below:
PlanNumber PlanType
123456 C
879879 R
567891 C

2nd search gives results like this:
VendorId PlanNumber

ABCD 123456

AEFG 879879

I want to combine the results and would like to display results as :
VendorId PlanNumber PlanType
ABCD 123456 C
AEFG 879879 R

Thanks
Bhargav

0 Karma

elliotproebstel
Champion

I agree with @richgalloway here - you should avoid using join if you can help it. Without seeing your source searches, the general structure of what I'd recommend is this:

<your first search> OR <your second search>
| stats values(VendorId) AS VendorId values(PlanType) AS PlanType BY PlanNumber 
| fields VendorId PlanNumber PlanType

In that first line, you will literally be joining the text of your first search with the second search. However, if the searches that are currently producing the first and second tables are complex, this won't work directly out of the box. So if you'd like help figuring out how to implement this structure, I'd be happy to help you analyze your queries. Feel free to obfuscate any sensitive data and post the search queries here.

0 Karma

bashtekar
New Member

Got solution used join host instead of join PlanNumber and gave separate names to both PlanNumbers
and then by using eval removed plannumbers which were not matching and then deleted the rows

0 Karma

richgalloway
SplunkTrust
SplunkTrust

If your problem is resolved, please accept an answer to help future users.
It would help if you would share the query that solved your problem (mask private information). SPL can be easier to understand than English.

---
If this reply helps you, Karma would be appreciated.
0 Karma

richgalloway
SplunkTrust
SplunkTrust

The join command will work, but is inefficient. If you have a large data set you could get better performance using stats. We'd need to see your current queries to offer a new query that does not use join.

---
If this reply helps you, Karma would be appreciated.
0 Karma

mayurr98
Super Champion

yes, but we need to know what is the index and data source of both the queries. @bashtekar if you give us search queries for both the searches it would be better for us to give you efficient solutions.

0 Karma

493669
Super Champion

Use JOIN command Try below :

<first search giving result PlanNumber PlanType>|JOIN PlanNumber max=0 [second search giving result VendorId PlanNumber]

Hope this helps you

0 Karma

mayurr98
Super Champion

hey try this

<first search output in table> | join PlanNumber [search <2nd search output in table>]

Try this run anywhere search

| makeresults 
| eval PlanNumber="123456C 879879R 567891C" 
| makemv PlanNumber 
| mvexpand PlanNumber 
| rex field=PlanNumber "(?P<PlanNumber>\d{6})(?P<PlanType>\w{1})" 
| fields - _time 
| join PlanNumber 
    [| makeresults 
    | eval VendorId="ABCD123456C AEFG879879R" 
    | makemv VendorId 
    | mvexpand VendorId 
    | rex field=VendorId "(?P<VendorId>\w{4})(?P<PlanNumber>\d{6})(?P<PlanType>\w{1})" 
    | fields - _time]

let me know if this helps !

0 Karma

bashtekar
New Member

join is not working for me.. tried this. I am also trying something like join host instead of PlanNumber which is giving me partially incorrect result (its looping thorugh both searches and giving all possible results)

0 Karma

mayurr98
Super Champion

what is your search query?

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...