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!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...