Right join in Splunk

[asarolkar]

I have two sourcetypes that have a field that does not have the same name in both places (but has the same values)

i) sourcetype="alphalog" ModuleNum=* | dedup ModuleNum 

ii) sourcetype="betalog" MNumber=* | table MNumber

Please note that sourcetype="betalog" has another field called MName.

I need to write a Splunk query that basically does this ->

Select betalog.MName from both sourcetypes where alphalog.ModuleNum = betalog.MNumber

Is there a query that does a join like this in Splunk ?

Any help would be appreciated

[kristian_kolb · Accepted solution]

Are you sure that you need to join? That's a pretty expensive operation, performance wise.
From your original question, you want to get the betalog.MName from betalog, where the values for ModuleNum/MNumber are the same. What more do you need from alphalog? Nothing? Just a listing of beta.MNames by alpha.ModuleNum?

It's always good practice to give a few sample events and describe the output you desire.

sourcetype=betalog [search sourcetype=alphalog ModuleNum=* | dedup ModuleNum | rename ModuleNum as MNumber | fields + MNumber] | stats values(MName) by MNumber 

This could work...but it depends on what you actually want. The subsearch (within the square brackets) returns a 'list' of unique ModuleNums (renamed as MNumbers) to the outer search. Then it's just a question of how you want to use that.

Kristian

[asarolkar]

Very helpful. Thanks !

[rroberts]

sourcetype=alphalog | JOIN type=inner ModuleNum [ search sourcetype=betalog]

OR this search...

sourcetype=alphalog [ search sourcetype=betalog MNumber=* | FIELDS MNumber ]

I think at least one of the two examples above should work if Im following. Check out: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Join

You might find this link useful as well:

http://www.innovato.com/splunk/SQLSplunk.html

[asarolkar]

That did not really entirely help.

Here is a new query for review:

sourcetype="alphalog" | dedup ModuleNum | eval MNumber=ModuleNum| join MNumber[ search sourcetype="betalog" fields MName ]