Splunk Search
Highlighted

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

Explorer

first query output :

CommonField , FirstQueryValue1 , FirstQueryValue2
1 fv1 fv21
2 fv1 fv2
2
3 fv1 fv2_3

second query output :

CommonField , SecondQueryValue1 , SecondQueryValue2
3 sv2 sv21
4 sv2 sv2
2
5 sv2 sv2_3

I want the ouptut in the below format

CommonField, FirstQueryValue1, FirstQueryValue2, SecondQueryValue1, SecondQueryValue2
1 fv1 fv21 0 0
2 fv1 fv2
2 0 0
3 fv1 fv23 sv2 sv21
4 0 0 sv2 sv22
5 0 0 sv2 sv2
3

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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?

0 Karma
Highlighted

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

Explorer

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.

0 Karma
Highlighted

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

Legend

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

0 Karma
Highlighted

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

Explorer
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 trainname runtimetrain1mins train2name runtimetrain2mins
1 fv11 fv21

2 fv12 fv22
3 fv13 fv23 sv11 sv21
4 sv22 sv22
5 sv23 sv23

0 Karma
Highlighted

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

New Member

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

0 Karma