How to get the colums results attached of first search and second search based on a common fieldvalue?

[nomadichunters]

first query output :

CommonField , FirstQueryValue1 , FirstQueryValue2
1 fv1 fv2_1
2 fv1 fv2_2
3 fv1 fv2_3

second query output :

CommonField , SecondQueryValue1 , SecondQueryValue2
3 sv2 sv2_1
4 sv2 sv2_2
5 sv2 sv2_3

I want the ouptut in the below format

CommonField, FirstQueryValue1, FirstQueryValue2, SecondQueryValue1, SecondQueryValue2
1 fv1 fv2_1 0 0
2 fv1 fv2_2 0 0
3 fv1 fv2_3 sv2 sv2_1
4 0 0 sv2 sv2_2
5 0 0 sv2 sv2_3

How to get the colums attached of first search and second results based on a common fieldvalue?

[ncmouli]

Hey nomadichunters try as below:

index=main (sourcetype=firstquery OR sourcetype=secondquery) | stats values(FirstQueryValue1) as FirstQueryValue1, values(FirstQueryValue2) as FirstQueryValue2, values(SecondQueryValue1) as SecondQueryValue1, values(SecondQueryValue2) as SecondQueryValue2 by CommonField | fillnull

[gcusello]

Hi nomadichunters,
if it's acceptable for you to identify common values from query1 and query2 putting "1_" or "2_" (or something similar), you could run something like this:

index=index1 OR index=index2
| eval  QueryValuesField=if(index=index1,"1_"+QueryValuesField ,"2_"+QueryValuesField)
| chart count over QueryValuesField BY CommonField

Bye.
Giuseppe

[nomadichunters]

1    index=main
2    | search train_name="train_1" 
3    | stats earliest(start) as start_time latest(finish) as finish_time by stage, train_1
4    | eval difference = (finish_time - start_time)
5    | eval final_time = tostring(difference, "duration") 
6    | join stage type=inner 
7        [ search index=main 
8        | search train_name="train_2" 
9        | stats earliest(start) as start_time latest(finish) as finish_time by stage, train_2 
10       | eval difference = (finish_time - start_time)
11       | eval final_time_train2 = tostring(difference, "duration") 
12       | table stage train_2 final_time_train2 ] 
13    | table stage train_1 final_time train_2 final_time_train2

Above is the sample , I am getting train1 details from primarysearch , and train2 details from secondary search , and common field is stage.

Required output :
Stage train_name runtime_train1_mins train2_name runtime_train2_mins
1 fv1_1 fv2_1

2 fv1_2 fv2_2
3 fv1_3 fv2_3 sv1_1 sv2_1
4 sv2_2 sv2_2
5 sv2_3 sv2_3

[Richfez]

You have mentioned CommonField but you have no fields in common. Do you mean that sv2_x matches up with fv2_x, and if so does it match on v2_x or only on 2_x? Or is there a column missing?

[nomadichunters]

No. CommonField is the name of the field which is common for both FirstQuery and Second query and 3 is the commonField value is which is common value for the commonField.