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

[bollam]

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

[renjith_nair · Accepted solution]

@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    

[bollam]

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

[renjith_nair]

@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

[renjith_nair]

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