topic Re: How to edit my search to fetch and compare the top/first row from two source types? in Splunk Search

How to edit my search to fetch and compare the top/first row from two source types?

email2vamsi — Fri, 02 Dec 2016 11:57:37 GMT

I would like to fetch the latest record (only the first row) from two source types and check if both the fields are zeros or not.
It's a success if both have zeros, else it's a failure if either of them is 1.

The following query is near to the requirement, but the Status_r field never shows up to compare as it has the older time stamp.

index="join_test1" sourcetype="Monitor_join_test" OR sourcetype="Response_join_test"  | eval running_ok = if(Status="0" AND Status_r="0","0","1") |head 1
|eval final = if(running_ok==0,0,1) |table running_ok Status Status_r final

Re: How to edit my search to fetch and compare the top/first row from two source types?

rjthibod — Fri, 02 Dec 2016 12:26:49 GMT

How about this query?

 index="join_test1" (sourcetype="Monitor_join_test" OR sourcetype="Response_join_test") 
 | stats latest(Status*) as Status* by sourcetype
 | fillnull value=0
 | stats max(Status*) as Status*
 | eval running_ok = if(tonumber(Status)=0 AND tonumber(Status_r)=0, 0, 1) 
 | eval final = if(running_ok=0, 0, 1) 
 | table running_ok Status Status_r final

Re: How to edit my search to fetch and compare the top/first row from two source types?

email2vamsi — Mon, 05 Dec 2016 10:12:49 GMT

Thank you. It works for numbers.
I have changed if(tonumber(Status)=0 AND tonumber(Status_r)=0, 0, 1) to the following
| eval running_ok = if(Status="Running" AND Status_r="Running", 0, 1). Here i am check for String,
Then its not giving the expected results.

  index="join_test1" (sourcetype="Monitor_join_test" OR sourcetype="Response_join_test") 
  | stats latest(Status*) as Status* by sourcetype
  | fillnull value=0
  | stats max(Status*) as Status*
  | eval running_ok = if(Status="Running" AND Status_r="Running", 0, 1) 
  | eval final = if(running_ok=0, 0, 1) 
  | table running_ok Status Status_r final

Re: How to edit my search to fetch and compare the top/first row from two source types?

rjthibod — Mon, 05 Dec 2016 10:42:59 GMT

You would to change the first few parts as well where you are combining the fields into a single table result.

To be clear, are you expecting strings in the real data or is there a mixture of strings and numbers?

Re: How to edit my search to fetch and compare the top/first row from two source types?

email2vamsi — Mon, 05 Dec 2016 10:54:50 GMT

I am doing field extraction and assigning the Running to Status="Running".
So the the real events contain "Running". I am not extracting any numbers from events.

I want to compare this Status and Status_r fields from two source types and assign 0 or 1 to the "Final" variable.

...| table final | outputlookup ouput.csv

Re: How to edit my search to fetch and compare the top/first row from two source types?

rjthibod — Mon, 05 Dec 2016 11:00:38 GMT

Then try this.

index="join_test1" (sourcetype="Monitor_join_test" OR sourcetype="Response_join_test") 
   | stats latest(Status*) as Status* by sourcetype
   | stats values(Status*) as Status*
   | eval running_ok = if(Status="Running" AND Status_r="Running", 0, 1) 
   | eval final = if(running_ok=0, 0, 1) 
   | table running_ok Status Status_r final

Re: How to edit my search to fetch and compare the top/first row from two source types?

rjthibod — Tue, 06 Dec 2016 12:30:12 GMT

Did this recommendation work?

Re: How to edit my search to fetch and compare the top/first row from two source types?

email2vamsi — Tue, 06 Dec 2016 12:48:46 GMT

Yes. Its giving the expected results.
I have additionally added | fillnull value="Not running" to the below query as Status and Status_r fields have blanks values when Running is not found in FX.
But this made the query slow. Is there any way to do fillnull during FX or during indexing?

 index="join_test1" (sourcetype="Monitor_join_test" OR sourcetype="Response_join_test") 
    | fillnull value="Not running"
    | stats latest(Status*) as Status* by sourcetype
    | stats values(Status*) as Status*
    | eval running_ok = if(Status="Running" AND Status_r="Running", 0, 1) 
    | eval final = if(running_ok=0, 0, 1) 
    | table final|outputlookup output.csv