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.
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 [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 [search index=X | ... do something ... | rename ... | stats count by b | table b | rename b as search ] | table d
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
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?
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.