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
@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,
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
@renjith.nair Awesome!! It works like a charm.. Thank you!!
Curious to know if it can be attained using join/append command?
@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
@bollam, do you have any further questions on this? If not, please accept as answer to close the thread