I have events from one source that look like:
source=foo fieldA=300 fieldB=400
In another source, I have events that I want to join with only one field:
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?
(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
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
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...
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.
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 ]