Getting Data In

Right join in Splunk

asarolkar
Builder

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

0 Karma
1 Solution

kristian_kolb
Ultra Champion

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

View solution in original post

kristian_kolb
Ultra Champion

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
Builder

Very helpful. Thanks !

0 Karma

rroberts
Splunk Employee
Splunk Employee

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

0 Karma

asarolkar
Builder

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 ]

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 ...