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
Legend

@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    
---
What goes around comes around. If it helps, hit it with Karma 🙂

View solution in original post

renjith_nair
Legend

@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    
---
What goes around comes around. If it helps, hit it with Karma 🙂

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
Legend

@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

---
What goes around comes around. If it helps, hit it with Karma 🙂

renjith_nair
Legend

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

---
What goes around comes around. If it helps, hit it with Karma 🙂
0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...