Splunk Search

Joining 2 sourcetypes by property value


Hey all,

I have 2 source types with the following properties


  • id
  • value


  • name
  • description

So my events might look similar to:

  • source_1: id=abc-123, value="blah"
  • source_2: name=abc-123, description="some_description"

The values of source_1.id and source_2.name are equal.

Im trying to display the id/name, description and value in a table. I've came up with the following query to do so:


index=main sourcetype=source_2
| rename name AS id
| join id [search index=main sourcetype=source_1 id=*]
| table id, value, description


Is my query the best way to achieve this? Are there any alternatives?

Labels (1)
0 Karma


That left join can be implemented with append and stats, as bowesmana recently reminded me.  It is not necessarily more performant, as it all depends on data characteristics, and what other constraints you can place on each search.

Here is one generic idea


index=main sourcetype=source_2
| rename name AS id
| append [search index=main sourcetype=source_1 id=*]
| eventstats dc(sourcetype) as sourcecount by id
| where sourcecount == 2
| table asset_id, id, description


This is not likely to be performant.  If, on the other hand, asset_id and description are the only fields you care about, you can use stats instead of eventstats.  stats will perform better if your data contains lots of other fields


index=main sourcetype=source_2
| rename name AS id
| append [search index=main sourcetype=source_1 id=*]
| stats dc(sourcetype) as sourcecount values(asset_id) as asset_id values(description) as description by id
| where sourcecount == 2
| fields - sourcecount


Under the search window, you can open "Job -> Inspect Job" to compare performance.


Try to avoid joins when possible because they're not efficient commands.  See how this works for you.

index=main (sourcetype=source_2 OR sourcetype=source_1 id=*)
| eval id = coalesce(name, id)
| stats values(*) as * by id
| table asset_id, id, description
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

Using Machine Learning for Hunting Security Threats

WATCH NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more for ...

Observability Newsletter Highlights | March 2023

 March 2023 | Check out the latest and greatestSplunk APM's New Tag Filter ExperienceSplunk APM has updated ...

Security Newsletter Updates | March 2023

 March 2023 | Check out the latest and greatestUnify Your Security Operations with Splunk Mission Control The ...