How to select latest events based on some condition?

[dineshCool]

Hi Guys,

Please help with this below scenario.

I have events like this

Now I want to take the events has a "success" status. Here I need to display the results in the same format which mentioned in the snapshot.

I tried with below query but it's not giving me the correct data

 |<base_query> 
    |stats earliest(status) AS earliest_state,latest(_time) latest(status) AS latest_state BY Name_Of_the_Job |eval job_status=if(earliest_state="SUCCESS","SUCCESS","FAIL") |table host,   Application_name,   Job_name,   job_duration,   _time,  status

Thanks in advance

[FrankVl]

You could try something like this:

|<base_query> 
|stats max(status) AS state BY host, Application_name,  Job_name

Since Success > Fail (lexicographically), if there is a Success, max(status) will return that, if there are only Fail values, it will return Fail.

[dmarling]

Your table after the stats line is containing fields that are not being passed through by the stats. Stats is a transforming command, not a streaming command. You will have to pass through the application_name and job_duration for it to work. You'll need to decide which duration you want for this. Do you want the earliest duration, the latest duration, a sum of durations, or some other statistical command on the duration? Your title is saying you want the latest events based on a condition, but your eval |eval job_status=if(earliest_state="SUCCESS","SUCCESS","FAIL") is stating if the earliest status that returns is successful, I am classifying "job_status" as "SUCCESS". You aren't using the "latest_state" field.

We need to understand your goal a bit better. What is the expected out come you are looking for on your output based on your sample data you provided?

[dineshCool]

Thanks for the response. My goal is to achieve to fetch the events status has "success" in any of the previous or latest run. but at the same time it has to consider the events has fail also. if the job ran multiple times and the status of the job remains "Fail", then the fail status should appear in the table.

[dmarling]

Is there a window on this analysis? If it ran 5 times do you only care about the results of the last three? Is failure defined as every run ever failing or only the last x number of runs that you define as your window failing in a row?

[dineshCool]

I have plenty of the events like this and i am not using any window for analysis.
The jobs may run multiple times but it has to consider the "status". if any of the previous run in "success" then it has to take the event has success. But if there is any failure in the result set , then the result should be in "FAIL".