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
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
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
Very helpful. Thanks !
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
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 ]