Splunk Search

How to search and display two sources in a table?

pmhelfrich
Explorer

I found this thread, but wasn't able to get it to work for me:
https://answers.splunk.com/answers/74245/joining-data-from-2-data-sources-in-splunk.html

I have 2 sources that I would like to display in a table. The two sources use 2 columns as the "join" to know that it's the same data.

In other words i have:
Source A, Column_A, Column_B (as well as other columns)
Source B, Column_A, Column_C (as well as other columns)
Column_A=Column_A and Column_B=Column_C (has to be both matching, not just one set of columns or the others)

Based on the link above, I have tried:

index=index* (sourcetype=A OR sourcetype=B)
| rename Column_C as Column_B
| table 

Maybe it doesn't matter, but I'm hung up on how Slunk knows what to join on if I don't tell it.

0 Karma
1 Solution

sundareshr
Legend

Try this

sourcetype=A | join column_A column_B [ search sourcetype=B | rename column_C AS column_B  | fields column_A column_B and other columns ] | table ...

View solution in original post

sundareshr
Legend

Try this

sourcetype=A | join column_A column_B [ search sourcetype=B | rename column_C AS column_B  | fields column_A column_B and other columns ] | table ...

pmhelfrich
Explorer

When you say "other columns," does that mean they have to be columns that match on each table, or just any column that I want from sourcetype B?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try like this

index=index* (sourcetype=A OR sourcetype=B)
| eval common_col=coalesce(Column_C,Column_B)
| table common_col list all other fields that you need here
| stats first(*) as * by common_col
0 Karma

pmhelfrich
Explorer

Unfortunately this didn't work. I'm still only getting columns from one of the tables. The other tables columns are just showing blank.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Can you share the query that you used? Also, could you confirm if there are matching data available in both the sourcetypes?

0 Karma

pmhelfrich
Explorer

The query that I gave above is the exact query with renamed values. I just checked and there is definitely data available in both sourcetypes and that would qualify for a "join." When I run what you gave and add |search common_col=(value), It pulls two rows, and each has the others data blank. In other words, the query is looking at both sourcetypes, just not combining anything.

My understanding is the columns you specify in the |table command won't effect the results and is just for display, correct?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Would you mind trying this

index=index* (sourcetype=A OR sourcetype=B)
 | eval common_col=coalesce(Column_C,Column_B)
 | table common_col list all other fields that you need here
 | stats values(*) as * by common_col
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...