Splunk Search

How to join two fields in a source with one field in another source?

justinnaldzin
Engager

I have events from one source that look like:

source=foo fieldA=100
source=foo fieldB=200
source=foo fieldA=300 fieldB=400

In another source, I have events that I want to join with only one field:

source=bar fieldC=100
source=bar fieldC=200
source=bar fieldC=300
source=bar fieldC=400

How would I join these two sources when the first source has two fields. And sometimes one of those fields is null and other times they both have values?

woodcock
Esteemed Legend

Like this:

(index=foo AND source=foo) OR (index=bar AND source=bar)
| eval joiner = mvappend(mvappend(fieldA, fieldB), fieldC)
| fields - fieldA fieldB fieldC
| stats values(*) AS * BY joiner
0 Karma

fernanlee
Path Finder

Probably a sub search will work for you.

source=mytable1
[SEARCH source=mytable2
mycolumn2=myvalue
| FIELDS mycolumn2]

0 Karma

zomis
Explorer

This doesn't help very much as you haven't adapted the search query to the actual data. Can you give an example of how a subsearch would help in this particular case?

0 Karma

zomis
Explorer

You can accomplish this by combining the values of fieldA and fieldB to a multifield, and then make the multifield an actual multivalue field and use mvexpand on that.

Here's a run-anywhere search you can try:

| gentimes start=-1
| eval f="100,0;0,200;300,400;700,800"
| table f
| makemv f delim=";"
| mvexpand f
| rex field=f "(?<fieldA>\d+),(?<fieldB>\d+)"
| fields - f
| eval fieldA = if(fieldA==0, null, fieldA)
| eval fieldB = if(fieldB==0, null, fieldB)
| eval source="foo"
| eval multifield = fieldA + ";" + fieldB
| eval multifield = coalesce(multifield, fieldA, fieldB)
| makemv multifield delim=";"
| mvexpand multifield
| table source fieldA fieldB multifield
| join left=L right=R where L.multifield = R.fieldC 
    [ gentimes start=-1 
    | eval fieldC="100,200,300,400,500,600" 
    | table fieldC
    | makemv fieldC delim="," 
    | mvexpand fieldC
    | eval source="bar" ]
| table L.source R.source L.fieldA L.fieldB L.multifield R.fieldC

In your case it can probably be done like this:

source="foo"
| eval multifield = fieldA + ";" + fieldB
| eval multifield = coalesce(multifield, fieldA, fieldB)
| makemv multifield delim=";"
| mvexpand multifield
| table source fieldA fieldB multifield
| join left=L right=R where L.multifield = R.fieldC [ search source="bar" ]
| table L.source R.source L.fieldA L.fieldB L.multifield R.fieldC
0 Karma

lguinn2
Legend

I don't know what you mean by "join". But you can search both sourcetypes this way:

(sourcetype=foo fieldA=* OR fieldB=*) OR (sourcetype=bar fieldC=*)

Of cousre, if you are trying to MATCH fieldC with: fieldA if available, otherwise fieldB...

(sourcetype=foo fieldA=* OR fieldB=*) OR (sourcetype=bar fieldC=*)
| eval  matchField = if(sourcetype=="bar",fieldC,coalesce(fieldA,fieldB))
# do something with matchField #

This is a complete guess; the community could be much more helpful if we understood what you are trying to accomplish...

0 Karma

justinnaldzin
Engager

Sorry I should be more specific. By "JOIN" I mean using the actual join command:

source=foo | eval matchField=fieldA
| join matchField [ search 
source=bar | eval matchField=fieldC ]

| append [ search 
source=foo | eval matchField=fieldB
| join matchField [ search 
source=bar | eval matchField=fieldC]

So the 1st source has both a fieldA and fieldB that could potentially match with a fieldC in the 2nd source in the join statement. While the above search works, I'm trying to figure out a better, cleaner way to join, instead of running all of these sub searches.

0 Karma

lguinn2
Legend

And once you "join" these things, then what do you want to do with them?

Depending on the output that you need, you can usually avoid using the join command in Splunk. And you should avoid using subsearches where they are not needed, as they are slower and have limitations. So, +1 to you for "better, cleaner" - but I think you might be able to go above and beyond - if the community understood your goal better...

Although the simplistic answer is

sourcetype=foo fieldA=* OR fieldB=*
 | eval  matchField = coalesce(fieldA,fieldB)
 | join matchField [ search sourcetype=bar fieldC=* | rename matchField=fieldC ]
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 ...