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.
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.
No, both searches are independent of each other. Which approach is best depends on what you want to do after combining the two sources.
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!