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
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
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
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?
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
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
Did this recommendation work?
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