Splunk Search

How can I append/join the values of sub-search with the main search with the common field in the corresponding row?

bollam
Path Finder

For an instance, I want to calculate the runtime of each stage of two trains and but there are stages which one of the trains do not have.
In such case it should be left empty.

Query which is written:

index=main
| search train_name="train_1" 
| stats earliest(start) as start_time latest(finish) as finish_time by stage, train_1
| eval difference = (finish_time - start_time)
| eval final_time = tostring(difference, "duration") 
| join stage type=inner 
    [ search index=main 
    | search train_name="train_2" 
    | stats earliest(start) as start_time latest(finish) as finish_time by stage, train_2 
    | eval difference = (finish_time - start_time)
    | eval final_time_train2 = tostring(difference, "duration") 
    | table stage train_2 final_time_train2 ] 
 | table stage train_1 final_time train_2 final_time_train2

this is skipping the values which are having common, I want all of them to be listed out.

The output should look like below.

Stage       train_name      runtime_train1_mins      train2_name     runtime_train2_mins
abc        train_1              20                train_2               40                                    
def           train_1              30                 train_2
123           train_1                                 train_2               50
456           train_1              40                 train_2               30
xyz           train_1                                 train_2               25
gee           train_1              55                 train_2
Tags (4)
0 Karma
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

@bollam,

Give this a try without a join

index=main (train_name="train_1" OR train_name="train_2")
| stats earliest(start) as start_time latest(finish) as finish_time by stage, train_name
| eval difference = (finish_time - start_time)
| eval final_time = tostring(difference, "duration") 
| stats values(eval(if(train_name="train_1","train_1",null()))) as  train_name,
            values(eval(if(train_name="train_2","train_2",null()))) as  train2_name,
            values(eval(if(train_name="train_1",final_time,null()))) as  runtime_train1_mins,
            values(eval(if(train_name="train_2",final_time,null()))) as  runtime_train2_mins
        by Stage
| fillnull value="train_1" train_name|fillnull value="train_2" train2_name
| table Stage,train_name,runtime_train1_mins,train2_name,runtime_train2_mins    
Happy Splunking!

View solution in original post

renjith_nair
SplunkTrust
SplunkTrust

@bollam,

Give this a try without a join

index=main (train_name="train_1" OR train_name="train_2")
| stats earliest(start) as start_time latest(finish) as finish_time by stage, train_name
| eval difference = (finish_time - start_time)
| eval final_time = tostring(difference, "duration") 
| stats values(eval(if(train_name="train_1","train_1",null()))) as  train_name,
            values(eval(if(train_name="train_2","train_2",null()))) as  train2_name,
            values(eval(if(train_name="train_1",final_time,null()))) as  runtime_train1_mins,
            values(eval(if(train_name="train_2",final_time,null()))) as  runtime_train2_mins
        by Stage
| fillnull value="train_1" train_name|fillnull value="train_2" train2_name
| table Stage,train_name,runtime_train1_mins,train2_name,runtime_train2_mins    
Happy Splunking!

bollam
Path Finder

@renjith.nair Awesome!! It works like a charm.. Thank you!!
Curious to know if it can be attained using join/append command?

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@bollam, should be possible to do with join as well

index=main "first set of data"| join Stage type=outer [search index=main "second set of data" ]

However, join is bit more expensive in terms of resource utilization. So as a thumb rule, we should avoid join wherever possible 🙂

Please accept the above answer if it works for you

Happy Splunking!

renjith_nair
SplunkTrust
SplunkTrust

@bollam, do you have any further questions on this? If not, please accept as answer to close the thread

Happy Splunking!
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...