topic Re: How to query events where the latest event field is only a specific value? in Splunk Search

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

Glasses — Fri, 23 Apr 2021 21:05:59 GMT

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!!

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

acharlieh — Fri, 23 Apr 2021 21:51:37 GMT

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"

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

Glasses — Fri, 23 Apr 2021 22:36:09 GMT

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.