How to query events where the latest event field is only a specific value?

[Glasses]

Hi

I have a request to find all users that have outcome=fail as the latest event. The outcome can be fail or success

for example >>> index=foo |stats latest(outcome) by user _time

Joe fail 2021-04-23 19:57:25.998
Joe success 2021-04-23 19:40:05.888

Trying to write a query where the result is only users with a fail as last outcome, not success as last outcome.
Any advice appreciated, TY!!

[acharlieh · Accepted solution]

So, you're looking for outcome events by user, so your by clause of your stats should reflect that... (no need for by _time as well)... Then you can use a where or search command to limit the output of your stats command, like so:

index=foo outcome=* user=*
| stats latest(_time) latest(outcome) by user
| rename latest(*) -> *
| where outcome="fail"

[Glasses]

Thank you, that works...

I want to look historically over 24 hours or 7 days,  so I tried to bin _time span=1h  and it didn't work...

Any ideas how to apply historic searches to that... 

for example running the following over 24hours...  I know its faulty depending on when the time break is but that is good enough...

index=foo outcome=* user=*
| bin _time span=5m   
| stats latest(_time) latest(outcome) by user
| rename latest(*)= *
| where outcome="fail"

In other words, I want to check 5 minute intervals for the lastest outcome as a fail.