topic Re: Join only returns 1 value in Splunk Search

Join only returns 1 value

arrowecssupport — Fri, 28 Feb 2020 14:25:06 GMT

The search below looks up a serial number in another index, there will be multiple values to "x", but currently it only returns 1.
How do I get it to return all of the values?
Also, 2nd question, as it's only returning 1 value, how does it choose which value to return?

index = email
serialnumber=123456789
| join serialnumber type=left  [ search index=db  | dedup Y | rename serial AS serialnumber ]
| table serialnumber  X

Re: Join only returns 1 value

nickhills — Fri, 28 Feb 2020 14:33:05 GMT

Hi @arrowecssupport

I am making some assumptions about where the "single value" is coming from, but lets talk about dedup first.

dedup x means 'deduplicate x'
This has the effect of only returning unique values of x.

given values like:

serial x
----------
123  foo
456  bar
789  bar
012  baz

you would see one entry for each value of foo, bar, baz.

serial x
----------
123  foo
456  bar
012  baz

Your second question "how does it choose" - it will pick the most recent (last) entry in the set of results.

That does not always mean the latest (or last by time), which is why the sort order is important.
If you want it to be the latest by _time you should |sort - _time before you |dedup x

The last part of the puzzle is the join

join by default only matches 1 entry. It also chooses the 'last in the result set'
For the join to return more than one value, you need to specify how many.
|join max=<int> or use 0 to return all values.

Re: Join only returns 1 value

arrowecssupport — Fri, 28 Feb 2020 14:36:43 GMT

Ah shoot sorry, i've edited the post look again.

Re: Join only returns 1 value

nickhills — Fri, 28 Feb 2020 14:40:25 GMT

Ok, thats thrown me a bit, but if I understand and my last edit on the join command follows the same logic as your question, try this:

index = email
 serialnumber=123456789
 | join serialnumber max=0 type=left  [ search index=db  | dedup Y | rename serial AS serialnumber ]
 | table serialnumber  X

Re: Join only returns 1 value

nickhills — Fri, 28 Feb 2020 15:12:38 GMT

FYI i just updated your question title - Its not a subsearch, its the join that is causing it. Hopefully my suggestion above works!