Splunk Search

Join two datasets (main search and subsearch), keep all keys in both, and update non-key fields with data from the subsearch

cdhippen
Path Finder

If I have two searches, one generates fields "key A" and "Column A" and the second search generates fields "key B" "Column B" and I want to join them together, keep all keys in "key A" and update the values that exist in key A AND key B with the values in Column B, leaving column A values as a fallback for keys that don't appear in column B, how would I do that?
|key| data| |key|data |
|---|---|---|---|---|
| A | 123 | | A | 456 |
| B | 123 | | B | 456 |
| C | 123 | | C | NULL |
| D | 123 | | G | 456 |
| E | 123 | | H | 456 |
| F | 123 | | I | 123 |
||
V
|key | data|
|---|---|
| A | 456 |
| B | 456 |
| C | 123 |
| D | 123 |
| E | 123 |
| F | 123 |
| G | 456 |
| H | 456 |
| I | 123 |

I tried main search | join type=outer max=0 [| subsearch] but it doesn't appear to be working because when I tried
main search NOT key=A [| subsearch] I would expect A to be removed from the A search but replaced with B search, but that didn't happen, so I think I'm losing values from the B search.

0 Karma
1 Solution

DMohn
Motivator

Assuming both searches have the field "key" and "data" you could go like this...

<your_main_search> | rename data as data1 | join type=outer max=0 key [<your_second_search> | rename data as data2] | eval data=coalesce(data2, data1) | table key data

View solution in original post

DMohn
Motivator

Assuming both searches have the field "key" and "data" you could go like this...

<your_main_search> | rename data as data1 | join type=outer max=0 key [<your_second_search> | rename data as data2] | eval data=coalesce(data2, data1) | table key data

cdhippen
Path Finder

This isn't working for me. If I limit the data of the main search (for testing) by saying | inputlookup x-x WHERE key=A and the subsearch results in key=A, key=B, key=C etc, the end result still only returns key=A. I need a way to keep all the results from both searches.

0 Karma

DMohn
Motivator

Ah, right ... You can't join a key where there is none on the left side of the join. Try it that way:

| inputlookup first_lookup | remane data as data1 | append [| inputlookup second_lookup | rename data as data2] | stats values(data1) as data1, values(data2) as data2 by key | eval data=coalesce(data2,data1) | table key data
0 Karma

cdhippen
Path Finder

I forgot to reply that I figured it out, but your solution here is more or less what I did in my search. Thanks for the ideas! The coalesce idea was very valuable.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...