Splunk Search

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

VABarn
New Member

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.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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

0 Karma

VABarn
New Member

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!

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk Observability Cloud – June 2025

What’s New in Splunk Observability Cloud – June 2025 We are excited to announce the latest enhancements to ...

Almost Too Eventful Assurance: Part 2

Work While You SleepBefore you can rely on any autonomous remediation measures, you need to close the loop ...

Leveraging Detections from the Splunk Threat Research Team & Cisco Talos

 Stay ahead of today’s evolving threats with the combined power of the Splunk Threat Research Team (STRT) and ...