topic Re: Club two different searches into one in Splunk Search

Club two different searches into one

bashtekar — Thu, 18 Jan 2018 10:25:43 GMT

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

Re: Club two different searches into one

mayurr98 — Thu, 18 Jan 2018 12:42:39 GMT

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 !

Re: Club two different searches into one

493669 — Thu, 18 Jan 2018 12:43:43 GMT

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

Re: Club two different searches into one

richgalloway — Thu, 18 Jan 2018 13:03:03 GMT

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.

Re: Club two different searches into one

mayurr98 — Thu, 18 Jan 2018 13:08:47 GMT

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.

Re: Club two different searches into one

bashtekar — Thu, 18 Jan 2018 13:52:41 GMT

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)

Re: Club two different searches into one

mayurr98 — Thu, 18 Jan 2018 13:59:41 GMT

what is your search query?

Re: Club two different searches into one

bashtekar — Thu, 18 Jan 2018 15:16:32 GMT

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

Re: Club two different searches into one

elliotproebstel — Thu, 18 Jan 2018 15:26:54 GMT

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.

Re: Club two different searches into one

richgalloway — Thu, 18 Jan 2018 16:03:08 GMT

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.