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

[justinnaldzin]

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]

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

[fernanlee]

Probably a sub search will work for you.

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

[zomis]

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?

[zomis]

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

[lguinn2]

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...

[justinnaldzin]

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.

[lguinn2]

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 ]