Splunk Search

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

email2vamsi
Explorer

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
0 Karma

rjthibod
Champion

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
0 Karma

email2vamsi
Explorer

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
0 Karma

rjthibod
Champion

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?

0 Karma

email2vamsi
Explorer

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

0 Karma

rjthibod
Champion

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
0 Karma

rjthibod
Champion

Did this recommendation work?

0 Karma

email2vamsi
Explorer

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

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...