Splunk Search
Highlighted

How to combine results of two table searches?

New Member

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.

0 Karma
Highlighted

Re: How to combine results of two table searches?

SplunkTrust
SplunkTrust

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
Highlighted

Re: How to combine results of two table searches?

New Member

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?

0 Karma
Highlighted

Re: How to combine results of two table searches?

New Member

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.

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.