How to combine results of two table searches?

[jwertheim]

I've combed through a plethora of the posts here with regards to using subsearches and other various "solutions" to what must be a very common issue: combining results from two searches.

Let me start out by apologizing since this may very well be an easily solved question. I hope you'll be able to take a moment to assist me whether or not that's the case. I would appreciate it.

My issue is the following:

I have two searches. They mostly resemble

index=X | ... do something ... | rename ... | table a, b, c

index=Y some_value_found_in_indexX_fieldb | table d

Let's say I do a regular boring search on index X and pick out one of field b's values. I then perform the second search. When I do it manually I can find matches for that value from index Y.

What I want to do is use each value from index X field b as the search "keyword" (for lack of a better term) rather than a static string that I pick out by hand.

Now I already tried several different subsearches with one inside the other knowing that the subsearch is performed first, which means that any value I want to use from it is for outer search (and can't be done in the opposite order).

So I expected that I'd be using the first search as my subsearch. Something like:

index=Y [search index=X | ... do something ... | rename ... | table a, b, c] | table d

But I don't think that's quite right since I never get any results.

If this were to work, I'd probably have a table with a, b, c, and d.

I don't think a join would be used in this case. Am I wrong?

If any of you could help me I would be super thankful.

[somesoni2]

In the search that you tried, the subsearch is returning 3 fields a, b, and c, so when the subsearch results are applied as filter, it will be in this form

index=Y ((a=foo1 AND b=bar1 AND c=chao1) OR (a=foo2 AND b=bar2 AND c=chao2) ..AND SO ON ) | table d

Based on your description, you just want to use field b, so I would suggest to use the subsearch like this (just return field b). Again assuming that index=Y has a field defined/extracted, with same name as b. (when you search manually you do index=Y b=some_value_of_b_You_find_in_X)

 index=Y [search index=X | ... do something ... | rename ... | stats count by b | table b] | table d

If you're doing text based search, try like this (when you search manually you do index=Y "just_the_value_of_b_You_find_in_X")

index=Y [search index=X | ... do something ... | rename ... | stats count by b | table b | rename b as search ] | table d

Update
The subsearch here is just acting as filter, so none of field values are retained as such (it's basically a giant OR condition as the first query in my answer, but with just field b). If you're looking for merging those two queries and getting all fields (a,b,c,d) for matching /commond rows, try like this

search index=X | ... do something ... | rename ... | table a,b,c | append [search index=Y [search index=X | ... do something ... | rename ... | stats count by b | table b] | table b, d] | stats values(a) as values(c) as values(d) as d by b

[jwertheim]

Sweet! On the right track, I think!

So by changing it so that I only return the 'b' field's values, that means I lose the values for 'a' and 'c'.

Is there a way to do it where I can keep them too or would I need to use another subsearch?

[jwertheim]

Hmm.

By changing my search query to that last format you described, I'm only getting the very first value (by using 'head 1'). If I don't do that then I get 4 duplicates and nothing else when there are many more other possible values.