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
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.
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
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.
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.
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.
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
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 !
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)
what is your search query?