Splunk Search

Join only returns 1 value

arrowecssupport
Communicator

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
Tags (1)
0 Karma

nickhills
Ultra Champion

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.

If my comment helps, please give it a thumbs up!
0 Karma

arrowecssupport
Communicator

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

0 Karma

nickhills
Ultra Champion

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
If my comment helps, please give it a thumbs up!
0 Karma

nickhills
Ultra Champion

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

If my comment helps, please give it a thumbs up!
0 Karma
Get Updates on the Splunk Community!

Index This | Divide 100 by half. What do you get?

November 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...

Splunk and Fraud

Watch Now!Watch an insightful webinar where we delve into the innovative approaches to solving fraud using the ...