Splunk Search

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

nomadichunters
Explorer

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?

0 Karma

ncmouli
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

gcusello
SplunkTrust
SplunkTrust

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

nomadichunters
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 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

0 Karma

Richfez
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

nomadichunters
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
Get Updates on the Splunk Community!

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...