topic Re: Right join in Splunk in Getting Data In

Right join in Splunk

asarolkar — Wed, 02 Jan 2013 23:43:04 GMT

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

Re: Right join in Splunk

rroberts — Thu, 03 Jan 2013 00:40:08 GMT

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

Re: Right join in Splunk

asarolkar — Thu, 03 Jan 2013 01:03:57 GMT

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 ]

Re: Right join in Splunk

kristian_kolb — Thu, 03 Jan 2013 13:46:39 GMT

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

Re: Right join in Splunk

asarolkar — Thu, 03 Jan 2013 17:44:34 GMT

Very helpful. Thanks !