Joining 2 sourcetypes by property value

[chrisdev]

Hey all,

I have 2 source types with the following properties

source_1

• id
• value

source_2

• 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?

[yuanliu]

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.

[richgalloway]

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