How to create a table with fields from a combination of two sources, but only when the field appears in both.

[VABarn]

Hello gurus!

Would you please help with this problem? I have one index (main) and two sources (hostInfo and smRelationship). Within each source, there are a number of fields, but only one field is the same between them, a string value. Sometimes the hostInfo will have more entries than the smRelationship, sometimes the smRelationship will have more entries than hostInfo. I want to create a table with fields from a combination of the two sources, but only when the field appears in both.

Examples of each source, I have put the matching fields in bold (hostId == smSystemId)

source=hostInfo

hostId, hostName,

host-xxx,unique-fieldA

host-yyy,unique-fieldB

host-zzz,unique-fieldC

source=smRelationship

smSystemId, smRel, smLag, smDest, smSourc

host-xxx,unique-fieldD,unique-fieldE,unique-fieldF,unique-fieldG

host-zzz,unique-fieldH,unique-fieldI,unique-fieldJ,unique-fieldK

I wanted table output (I am actually leaving out the common field in the output):

unique-fieldA, unique-fieldD, unique-fieldE, unique-fieldF, unique-fieldG

unique-fieldC, unique-fieldH, unique-fieldI, unique-fieldJ, unique-fieldK

Here is a sample search string (that doesn't work).

index=main source=hostInfo OR source=smRelationship | where hostId = smSystemId | table hostName smRel smLag smDest smSourc

I was thinking of some sort of where comparison, that it only outputs into the table if both fields (hostId and smSystemId) were found, but that didn't work.

[martin_mueller]

The most obvious answer would be to join them:

source=hostinfo | join hostId [search source=smRelationship | rename smSystemId as hostId]

Depending on what you want to do after the join you may want to use stats like this:

source=hostInfo OR source=smRelationship | eval common = coalesce(hostId, smSystemId) | stats first(fieldA) as fieldA first(fieldB) as fieldB ... by common

...or maybe something entirely different. Do read http://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-joi... for some background.

[martin_mueller]

No, both searches are independent of each other. Which approach is best depends on what you want to do after combining the two sources.

[VABarn]

martin_mueller, thank you so much for the quick response!

I am sorry if I am missing something obvious, but when you state that ...after the join you may want to... do you mean that the first search is then appended to the second?

Again, thank you!